Dependent drop down list

Resolved · Urgent Priority · Version 2013

Mon 8 Jan 2018, 10:50 Delegate Ibijoke said...

Ibijoke has attended:
Excel Intermediate course
Excel Advanced course

Dependent drop down list

Hi,

I've been trying to create dependent drop down lists. Some of the drop downs are working while some are not and I'm struggling to figure out where the error is.
The formula I'm using is =INDIRECT(Q2).
I've made sure there are no spaces behind my words. What else do I need to do please?
Thank you.

Mon 8 Jan 2018, 15:08 Trainer Sarah said...

RE: Dependent drop down list

Hi Ibijoke

Thanks for your question.

If some lists are working and others aren't, then I would suggest this could be a problem with your named ranges.
Try pressing Ctrl+F3 to open the Name Manager window and checking if all of the lists have the correct names and references. Even one character difference will stop it from working.

If this doesn't work, please let us know what sort of error you're getting from the lists that aren't working. Is it #REF! or #NAME? or some other problem? This information will help us answer your question better.

Kind Regards,

Sarah
Excel Trainer

Mon 8 Jan 2018, 15:46 Delegate Ibijoke said...

RE: Dependent drop down list

Hi Sarah,

Thanks for your response. I have opened up the named ranges like you suggested and there are no errors. however, I did notice that the drop downs not working are the ones depending on an object with two words.

For example:
Dependent drop down list for "Weather" is working while list for "Human factor" is not working. Could the problem be related to the fact that there are 2 words involved?

I've tried doing it all over again and this is the message it come ups with:
"The Source currently evaluates to an error. Do you want to continue?"

Please note that there are no spaces in my named ranges even if there are 2 words.

Thank you again

Tue 9 Jan 2018, 10:41 Trainer Wendy said...

RE: Dependent drop down list

Hi Ibi

Thank you for using the forum

The problem is indeed the fact that your cell contains two words.

Essentially your cell containing the name and the name range need to be exactly the same

"Human factor" would have to typed as "HumanFactor" or "Human_Factor" or however it is listed inside the Name Manager.

Hope this helps

Kind regards
Wendy

Tue 9 Jan 2018, 13:34 Delegate Ibijoke said...

RE: Dependent drop down list

Thank you Wendy. that is all sorted now!

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Sorting data stored in rows

Primarily Excel is set up to sort data that is stored in columns rather than rows.

It is possible to get Excel to sort data stored in rows however.

Click in the row you want to sort, or select the cells in the rows you wish to sort.

Go to Data - Sort, then go to the Options button in the bottom left corner of the Sort dialogue box.

View all Excel hints and tips