Forum Discussion
HELP WITH VBA OR FORMULA
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.
msm66 Yeah it still sounds like you want that column E in my example even if it is just 1 cell the point is that you want it to be unique from the cell in column C. The problem is that when you copy that formula you have a new RAND() function that is completely independent from the previous and both RAND() could give the same or different answers so sometimes you can get the same output and sometimes you don't. I tried to a bunch of tricks to include the previous RAND selection in the unique formula but they didn't work. But those were 'general' solutions that would be 'flexible'. let me ask if it is correct to make the following 'assumptions' about your data:
1) the original set of data doesn't have duplicates (i.e. no need to filter it)
2) you only need a few discreet selections
Here is an example of the formula I came up with:
=INDEX($A$1:$A$11,INDEX(SORT(ROW($A$1:$A$11)*($A$1:$A$11<>$C$1)*($A$1:$A$11<>$D$1),,-1),RAND()*SUM(--($A$1:$A$11<>$C$1)*($A$1:$A$11<>$D$1))))where original data is $A$1:$A$11 then the 1st random selection was in C1 and the 2nd was in D1 and this is the 3rd selection
See attached
I might have more questions in the future that will bother you soon again. lol
Thanks again.