Copy items from dropdown list to new sheets

Remove the ' before MON in your formula and see if that works.

See the attached:

I have put formulae in "MON - VOLUNTEER" for BOTTON, MIDDLE AND TOP

For TOP .....

in U6

=INDEX(INDIRECT("'" & $S$3 &"'!C4:C24"),MATCH($S6,INDIRECT("'" &$S$3 & "'!$A$2:$A$24"),0))

in V6

=INDEX(INDIRECT("'" & $S$3 & "'!$F$4:$O$24"),MATCH($S6,INDIRECT("'" & $S$3 &"'!$A$4:$A$24"),0),MATCH($S$1,INDIRECT("'" & $S$3 &"'!$F$2:$O$2"),0)+1)

in X6

=INDEX(INDIRECT("'" & $S$3 & "'!$F$4:$O$24"),MATCH($S6,INDIRECT("'" & $S$3 &"'!$A$4:$A$24"),0),MATCH($S$1,INDIRECT("'" & $S$3 &"'!$F$2:$O$2"),0))

Nothing in column Y as it would more complex to "sort" by Main Meal

in X5

=COUNTIF(X6:X25,$F$4)

in Y5

=COUNTIF(X6:X25,$G$4)

For BOTTOM & MIDDLE change $S$3 to $A$3 and $J$3 and $S$1 etc to $A$1


I also "de-merged" cells: avoid merging cells - in particular if they are to be used in formulae.

P.S Add ERROR handling to avoid N/A Errors

=IFERROR(INDEX(INDIRECT("'" & $A$3 & "'!$F$4:$O$24"),MATCH($A6,INDIRECT("'" & $A$3 &"'!$A$4:$A$24"),0),MATCH($J$1,INDIRECT("'" & $A$3 &"'!$F$2:$O$2"),0)+1),"")

I have looked at this again:

First , for Clients use your original formula

=TOP!A6 and copy down

For Juice, Dessert, Main Meals (in TOP) for Monday

=VLOOKUP($A6,TOP!$A$4:$O$29,3,0)

=VLOOKUP($A6,TOP!$A$4:$O$29,7,0)

=VLOOKUP($A6,TOP!$A$4:$O$29,6,0)

You will need to change the 7 and 6 to 9 and 8 for Tuesday, 11 and 10 for Wednesday etc

A bit simpler


I'll try and send you a complete set later.

Hi John

sorry have had one of those days and head not working properly.

First , for Clients use your original formula

=TOP!A6 and copy down
If my clients start at =TOP!A4 will I use that instead?

For Juice, Dessert, Main Meals (in TOP) for Monday

=VLOOKUP($A6,TOP!$A$4:$O$29,3,0)

=VLOOKUP($A6,TOP!$A$4:$O$29,7,0)

=VLOOKUP($A6,TOP!$A$4:$O$29,6,0) I have placed this formula in cell for TOP Tab in worksheet. How do I get the 2 choices of meals for Monday Roast Beef and Chicken Pie to show up in the correct column in the 'MON VOLUNTEER'S' tab. When I select beef in drop down list from TOP tab under Monday, I need it to show up in the beef column in the 'Mon Volunteers' tab. When I select the Chicken Pie from same drop down list I need it to show up in the chicken pie column in 'Mon Volunteers' tab. I then need the total of beef and chicken meals for clients to total at top of each column for each meal choice.

sorry just want to make sure I put it in the correct cell and dont make a mess of it all

Try

in TOP

In X6 : the clients in "MON - VOLUNTEER" start in A6 but the Clients in TOP start in A$4 so we LOOKUP A6 in the range A4:O29 in TOP

.... rather confusing I know .... but formulas do not need changing

=IF(VLOOKUP($A6,TOP!$A$4:$O$29,MATCH($S$1,TOP!$A$2:$O$2,0),0)<>F$4,"",VLOOKUP($A6,TOP!$A$4:$O$29,MATCH($S$1,TOP!$A$2:$O$2,0),0))

in Y6

=IF(VLOOKUP($A6,TOP!$A$4:$O$29,MATCH($S$1,TOP!$A$2:$O$2,0),0)<>G$4,"",VLOOKUP($A6,TOP!$A$4:$O$29,MATCH($S$1,TOP!$A$2:$O$2,0),0))

The F & G can remain constant as they apply to all of BOTTOM, MIDDLE and TOP


Previously I put all main meals in one column as I (and admittedly I don't know your business) could see little merit in having two columns, The Counts in X4 and Y4 gave the correct counts of meal. Nor did I understand why Desert/Main Meals were reversed!

in X4

=COUNTIF($X$6:$X$25,$F$4)

In Y4

=COUNTIF($Y$6:$Y$25,$G$4)

Updated file with completed "MON - VOLUNTEER" sheet. See my PM.


I have doing some further "exploration" of the workbook:

There are (daily) menus in "TOP" in columns U to AA in which there can be more than 2 choices.

This means the current selection with 2 headings will "fail" if a 3rd (or more) choice is made e.g. "FROZEN" or "STEAM FISH".

How do want to handle this? Do we have more columns for the selections in the "MON -VOL" sheet(s)?

There is also a table for "Diabetic Client" so again how should this be handled? Perhaps a column indicating whether a Client is a Diabetic?



I would suggest you place these tables in a separate worksheet and use Named Ranges e.g. "Monday" for Monday's menu.