Excel Magic Trick #241: Data Validation w Shrinking List

See how to create Data Validation Drop Down Cell List where the List shrinks as you select values. See array formulas, INDEX function formula with criteria, data validation and the OFFSET function used in a named formula that creates a dynamic range of cells.

Category:

Science & Technology

Tags:

• Excel
• Data
• Validation
• Shrinking
• List
• array
• formulas
• INDEX
• function
• Mike
• Gel
• Girvin
• Highline
• Community
• College

[6642633]

This is a real time saver!

just used it in my worksheet and it is working perfectly, real time saver.

Thank

[ExcelIsFun]

You are welcome!

[SanadFeisal]

Thank you sir!

[ExcelIsFun]

You are welcome! Thanks for the 5 star ratings for all the videso that you have been watching!!

[csm634s]

Can you tell me how I would change these formulas if I wanted to do drop down boxes in more than one column? You have drop down boxes for just column E. I need drop down boxes for 10 columns (columns E-N).

[ExcelIsFun]

It would be a lot to do with formulas, your best bet is to do it with VBA code. I am not good with VBA. Post a question to the Mr Excel Message Board and maybe you can get some help there.

[ExcelIsFun]

Dear soulsaver04,

Yes, it takes a while to get the hang of array formulas, but once you do you have a new universe of Excel possibilities!

--excelisfun

[soulsaver04]

oh yah i have to control shift enter

[ExcelIsFun]

Dear cbaskis,

Not that I know. Not with tables set up as databases because the whole column would have formulas.

However, you could combine it with this video:

YTLE#75: DV Drop-Down based on DV Drop-Down

--excelisfun

[cbaskis]

I fat fingered it and figured it out.

Is there anyway to combine this with your video on the dynamic ranges, EMT#259?