Excel Magic Trick 400: Show List of Values In Cells After Using Data Validation Drop-Down

[kevster95]

Hi ExcellsFun,

I have tried this on my computer and it seems to be picking up the correct data but it is displaying it as a 0 rather than the data in the cell. Is there a quick fix or a reason for this?

Thanks, Kevin

[ExcelIsFun]

@kevster95 , I do not know what is causing the problem.

[Kevster2011]

@ExcelIsFun , I have worked it out I was trying to extract the data from another sheet.

Is this trick possible or is there another formula that will allow this. It seems to register what I am trying to do displaying a 0 instead of the actual cell data. Any help would be great.

Thanks,

[ExcelIsFun]

@Kevster2011, I do not know. Post Q to:

mrexcel [dot] com/forum

Be clear when you post to this site, if you state a clear question, you will get a response quickly.

[lcteddy]

Thanks! I was Trying to find exactly this for a diagram at work. I just hope I can do this in time and work out how to use this for multiple lines via the drop down list

[passcmfas]

Hi Mike, I actually wanted to create a drop-down button, say at cell B1, and B1 I would format it to be a drop-down which contains these two values - "Red" and "Yellow". this drop-down can allow user dynamically display the rows that contain the specific word Say If I select Red, only the following would appear on my table and leaving the rows containing "Yellow" hidden. A1 Red A2 Red A5 Red Data set: A1 Red A2 Red A3 Yellow A4 Yellow A5 Red A6 Yellow Appreciate your help.

[ExcelIsFun]

I am not sure how to do this. The best place to ask Excel questions is:

mrexcel[dot]com/forum

If you ask a clear question, you should get an answer.

If you post to this amazing site, send me the link so I can follow alomg and learn also.

[passcmfas]

Hi excelisfun- thanks for the video, I have a question here - in a column i have the following data:

A1 Red

A2 Red

A3 Yellow

A4 Yellow

A5 Red

A6 Yellow

How do i create a drop down button that display the rows that contains the word "Yellow" only?

Thank you again for your great videos!

[ExcelIsFun]

I do not understand. Are you saying extract the "yellow" words then use those in a data validation drop-down?

[Mynotoar]

Pressing the Ctrl key is very useful, I've known how to do that for a while and it helps in nearly everything. The INDIRECT function was very interesting, though.

[ExcelIsFun]

Yes, you can go see the Playlist that I have just on INDIRECT function tricks!

[planiolro]

Awesome! Interesting how the name feature is working, I did not know about this limitation ( has to be on the same row..........). What if we do not have the named ranges on the same row, or we have it on different sheets? What can be done in that situation?

[ExcelIsFun]

I showed an example in the video of what happens if it is not in the same row: you get a #VALUE! error.

But check this out:

If you put the name in the same row on a different sheet, it will work.

So being in the same row is the key!