Drop down menu - change data (Excel 2016)

[Timewalker]

Ok, what I want to do is complicated (in my mind), so I don't know if Excel can do this, or if I can explain it. I'm currently making a list of when local restaurants open and close for my hotel. Currently, I have the days as columns, merged in two cells, with O (open) and C (closed) being filtered so I can select a day and filter so I can see what is open. I have a better idea to change this, but I don't know if I can. I'd like to have a dropdown to select the day, and have the information in the column change for whatever day I have selected. I think it'd look nicer, as it would just be one column instead of 7, and it'd teach me something new. Is this possible?

[AliGW]

There are instructions at the top of the page explaining how to attach your sample workbook (NOT screenshot).

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

Administrative Note:

Members will tailor the solutions they offer to the version (NOT release number) of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.

Administrative Note:

Although we value your privacy as much you do, it could be important that members have a rough idea of your location as the solutions they offer may be affected by your locale. For instance, you might in the future post questions which are related to your regional settings.

With this in mind, please update your profile to something more precise (something such as UK, Europe, USA, UAE, etc. will suffice).

Thank you for helping us to help you.

[JohnTopley]

Please see yellow banner at top of page on how to attach a sample workbook.

And update your profile with Location and Excel version as responses do depend on knowing this information.

[Timewalker]

Ok, I changed it from a picture to a sample, and updated my profile.

[AliGW]

Where have you manually mocked up what you want? All I see are the daily columns you say you want to be rid of. I can't work out what is meant to be in these drop-down menus, where the data is that is meant to feed them or anything, really. You know what you want, obviously, but we don't.

[Timewalker]

I don't know how to mock it up, as I don't know how to do drop downs. I want to get rid of the full week columns, and put one drop down that has all seven days in it. By clicking any of the days of the week in the dropdown menu, the open and close hours of the restaurant will be filled in to the one column I'd have for the hours.

[JohnTopley]

Re Ali's reply: If O/C is simply Open/ Closed to you may want tp consider adding the actual Open hours e.g 0900:22:00.

[AliGW]

You will still need to have a full list of opening hours somewhere. Please mock this up for the first five restaurants in the list.

[AliGW]

OK - I've mocked something up for you.

There is a matrix lookup table on another sheet (you will need to fill it in). C2 is the drop-down list set up using data validation on the Data ribbon. Then I used this to grab the data:

=INDEX(Sheet2!$D$2:$R$15,MATCH(1,(Sheet2!$B$2:$B$15=$C$2)*(Sheet2!$C$2:$C$15=C$3),0),MATCH($B4,Sheet2!$D$1:$R$1,0))

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	A	B	C	D
2	Type of Restaurant	Name of Restaurant	Sun
3			O	C
4	Bakery / Cafes	In a Pinch	11:00	15:00

Sheet: Sheet1

[Timewalker]

Ok, I changed it to include some sample hours.

[AliGW]

Too late - see post #9.

[Timewalker]

Yes! That's pretty much exactly what I'm looking for. Thank you.

[AliGW]

Glad to have helped.

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

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.