Drop down menus that hide or reveal cells

**markugo** · 04-26-2013, 02:43 PM

Hello, everyone

I am trying to make a checklist that updates itself depending on drop down selections. For example, let's say that I have a list of 100 household chores that need to be done at different frequencies on different days and require different amounts of time.

So I build some drop downs for 'day of the week' and 'time available to do chores.' Let's say I have two hours and it's Monday. I want the list to return only those chores that need to be done on Monday and can be done in two hours.

I know that I need dependent drop downs, but now I need to somehow tie the selections to a list of chores that hides/reveals chores based on drop down selection.

Please excuse the dumb example, but this is the kind of functionality I need.

Thanks!

Mark

**Harribone** · 04-26-2013, 03:12 PM

Attached is an exmpla of how to do this. You need to create a table which populates with the choices that will be available for your drop down list.
Then use data validation to create the list but use the OFFSET formula so the list will only show the choices availble without blank entries.

**markugo** · 04-26-2013, 03:43 PM

Thank you so much! This is the functionality I need! Now I will try to replicate.

**markugo** · 04-26-2013, 03:45 PM

What exactly does the OFFSET formula do anyway?

**markugo** · 04-26-2013, 03:52 PM

Oh nevermind, you explained it in your initial response.

Now, what if I want a series of drop downs to result in a specific series of questions to be answered. Like an audit. If you select x options, from x drop downs, y series of questions will appear for you to address.

**Harribone** · 04-26-2013, 03:57 PM

Never explained this formula before but here goes...

Firstly to help explain I will refer to the file I attached. The range V5:V11 is what you want to display as a drop down. If you simply select this range in the data validation you will get the choices A B and E plus 4 blank options. The offset will only show the values A B and E. If you change the day to Tue the only chore in my example is C. the drop down will now show this again without blanks to keep things tidy.

=OFFSET(V5,0,0,MAX(W:W),1)

V5 - this is the strating point for your list.

1st 0 - How many rows from your starting point you want to move
2nd 0 - How many columns from stating point to move to

(note you wnat to start at V5 for your list so these are set to 0. if you changed both to 1 for example it would tell excel to start at W6 instead.

MAX(W:W) is the highest value for COl W i.e the number of available Chores currently. This is insetred into the OFFSET formula to say how many rows down to go to for the range (so it now knows the range is V5:V7 in my example file).

The 1 in the OFFSET is the number of columns in the range. If this was 2 the range would change to V5:W7. Setting to one tells excel basically its just one column.

Change the day and hours and re read the example again and see if you can understand what the offset formula does for that situation. If you manage to follow my explination then hopefully you wil undertsand how this changes each time to show a list without blanks.