Forum Discussion
HELP WITH VBA OR FORMULA
Hi ,
let's say I give you a list of fruits (something like the attachment) and ask you choose the one you like from each list. let's say there are "N" items in the list and you only like 10 of them the most.
now you start selecting or marking or checking the one you like. every time you select your choice will enter or type in another cell. or even you can select all in one time maybe through VBA you create a "run" button. by clicking the "run" all your choices will be enter to a new cell one on one.
so at the end i would have a column selection of your 10 choices of 50. hope the problem is clear.
thanks a lot
msm66 First off, please don't create new duplicate threads.
As for VBA, of course VBA can do anything you can do in the sheet and more, but unless the VBA code is really small or trivial, the us volunteers here probably won't create it for you. But in VBA you can create custom forms for data entry (I believe a PC version it is still required to create or edit the form but that they will work on a Mac, but they don't work if using Excel online). You can also create active modules that will monitor sheet edits and make updates accordingly.
That said, I often try to achieve the functionality in the sheet to avoid problems with others being required to approve the macro functions before the sheet will work (and in some cases they can't).
My prior answer using drop down menus could be modified so each successive drop down list would exclude items already selected.
Another alternative is to add another column next to the list and just have the user type an "x" in that column instead of 'checkboxes' or maybe rank their favorite foods in that column (then you could even sort by that rank).
If you want checkboxes, they point to a cell so if you create that column next to the list and add checkboxes over each cell you can have each checkbox 'point' to that corresponding cell.
So if you take the suggestion of adding a column that they rank their favorites then you could use:
=IFERROR(INDEX(A:A,MATCH(ROW()-ROW($C$1),B:B,0)),"")assuming the food is in col A, the ranking is in col B, and this list is created in col C with C1 being a header line.
Various tweaks could be made to account for non-sequential and duplicate ranking and such if needed.
28 Replies
msm66 First off, please don't create new duplicate threads.
As for VBA, of course VBA can do anything you can do in the sheet and more, but unless the VBA code is really small or trivial, the us volunteers here probably won't create it for you. But in VBA you can create custom forms for data entry (I believe a PC version it is still required to create or edit the form but that they will work on a Mac, but they don't work if using Excel online). You can also create active modules that will monitor sheet edits and make updates accordingly.
That said, I often try to achieve the functionality in the sheet to avoid problems with others being required to approve the macro functions before the sheet will work (and in some cases they can't).
My prior answer using drop down menus could be modified so each successive drop down list would exclude items already selected.
Another alternative is to add another column next to the list and just have the user type an "x" in that column instead of 'checkboxes' or maybe rank their favorite foods in that column (then you could even sort by that rank).
If you want checkboxes, they point to a cell so if you create that column next to the list and add checkboxes over each cell you can have each checkbox 'point' to that corresponding cell.
So if you take the suggestion of adding a column that they rank their favorites then you could use:
=IFERROR(INDEX(A:A,MATCH(ROW()-ROW($C$1),B:B,0)),"")assuming the food is in col A, the ranking is in col B, and this list is created in col C with C1 being a header line.
Various tweaks could be made to account for non-sequential and duplicate ranking and such if needed.
- Thank you Very much and a lot appreciate your effort to give me all the different possible options. Also thank you for informing I should not create duplicate threads. I thought the second problem description was more clear and understandable. So I might get better responses. I do prefer a solution with checkbox because make it much user friendly. Can you please guide me through that option?
I am working on a project and it looks like you are an expert. Not sure how can be in touch. but let me know if you are available.msm66 I am not an expert, when we talk about checkboxes. I've used them, but not too fond of them for various reasons, some of which may just be me not knowing/learning advanced use options for them. One of the main issues I have with them is the manual overhead to create and configure each of them. So I have attached a sample document that I think is what you are looking for and as you take it and expand it you can see/decide for yourself (unless someone else here might know and chime in with some tricks to make it easier).
In the attached I copied the lists you showed in your attached image (spelling errors corrected) into columns B, D, F and columns A, C, E are used to give spacing between the lists so the checkboxes can sit to left of each corresponding item. The checkboxes are then configured to control the corresponding cell it is sitting over top. The 3 "Selection" lists are then in columns H, J, and K also skipping a column between each. This space is nice in appearance but more importantly makes copying the formula easier. So try it out and see and if you like you can follow the following instructions to expand:
1. fill in the rest of you lists
2. "copy" or "add" additional checkboxes (tip: if you shift - right click multiple checkboxes you can then ctrl-drag them to copy many at a time)
3. configure each new checkbox to "control" the cell it is sitting on top of. Ctrl-Right click box and select "Format Control" then on the "Control" tab type the cell reference into the Cell Link field. (note you can click on the cell but I find that harder since the checkbox is blocking it and I'm faster with my keyboard)
4. The formulas under the "Selected" list are copied from row 2 to 6 (i.e. max of 5 items). If more are needed you can copy the formula down to additional rows. Bonus: I added conditional formatting to highlight a "Selected" column if it appears more items are are selected than can be shown in the "Selected" list. You can test this by selecting all 6 items in the first list.
