Display the date in a cell which foods are available based on the selections list

[thup_98]

Hello,
I have a Foods Menu Table and would like the date is displayed based on the foods are selected from a list.
Thank you for your help
Thup

[Kaper]

I'd use helper row (say row 10) and in A10 array formula:

Please Login or Register  to view this content.

copy right, and then in I2 standard formula:

Please Login or Register  to view this content.

note that after removing one of items on Sun Thu will appear and only after removing one of your foods from Thu you will get N/A info.

[Kaper]

PS. If you want to list all days in I2 array formula:

Please Login or Register  to view this content.

and copy down

Just in case (I should wrote this in post #2): Array formula is the one confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[newdoverman]

Kaper and I thought pretty much alike but differently. I used the same array formula {=SUM(COUNTIF(A2:A9,IF($B13:$B20="X",$A13:$A20)))} which counts the matches for the X values in B13:B20 for each of the columns for each day of the week.

To get all days that match in a horizontal arrangement the following will do that:

Please Login or Register  to view this content.

Enter with Ctrl + Shift + Enter

A vertical display of days matching the criteria can be obtained with this array formula

Please Login or Register  to view this content.

Enter with Ctrl + Shift + Enter

[thup_98]

Thank you Kaper and Newdoverman both ways are working fine. Is there a way to make I2 blank when no food is selected?
Thank you so much for your help
Thup

[newdoverman]

This will return a blank if no cells are selected in B13:B20 Enter in I2 and fill down

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[thup_98]

Thank you Newdoverman it works perfectly.
Happy Holiday Season and a prosperous New Year!
Thup

[thup_98]

Hi Newdoverman,
Is there a way to populate the cells below the date with the selected items on another sheet?
Thup

[newdoverman]

This can be done. I moved all the data in the range A12:G20 to Sheet2. As you can see, the formulae still works on Sheet1.

With the data on Sheet2 the formulae for column I is as follows. Array enter.

Please Login or Register  to view this content.

The formula on Sheet2 that makes the count (formerly in row 10 on sheet1)

Please Login or Register  to view this content.

[thup_98]

Hi Ron,
Sorry I may not make it clear in my question so you may misunderstood me. Is there a way to populate the cells below the date with the selected items on another sheet?
Please see sheet 3.
In your formula I did not mark shellfish but it still shows Thu in sheet 1or I may do something wrong. Please help to explain and correct me.
Thank you,
Thup

[newdoverman]

I don't know where this is going but this should help you with your last request. If this is a work in progress, I suggest that you post the whole problem as solving one thing at a time may not be the best way to achieve your goals. A new thread with that in mind might be in order.

I created a Data Validation drop down list with the days of the week and also a copy of the listing on Sheet1 onto Sheet2 where I inserted helper columns to count the items that were chosen from the range with the Xs. The helper columns have this formula which is copied into the helper columns for each day of the week (helper columns are to the left of the items for the day)

Please Login or Register  to view this content.

In the area on Sheet3 where you indicated that you want the results, the data validation drop down list is in Sheet3!A2 where you can choose the day of the week that you are interested in. The formula in A3 is as follows and is filled down the column.

Please Login or Register  to view this content.

I copied the range from Sheet3 onto Sheet1 in case you wanted a copy there. You can delete the copy that you don't want. Don't delete the range with the calculations on Sheet2 or nothing will work.

[thup_98]

Hi Ron,
You are brilliant! I understand now. It works. Problem solved.
Thank you so much for your help.
Wishing you and yours a very Merry Christmas and a Happy New Year.
Thup

[newdoverman]

Thank you for the feedback.
Merry Christmas and Happy New Year to you and yours too.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.