Data Validation – COUNTA for a list

[johncheng435]

Hi everyone, I have a table of values and I'm trying to figure out how to use data validation to create a dynamic drop-down list based on the number of cells with contents within the table.

For cell H2, users can select which category they want (Revenue, Sales, EBITDA, Net Income). However, for cell H3, I want to create a drop-down list where users can only select only the number of cells that have contents. For example, if they choose Revenue, they would be able to select between 1-4, but if they choose EBITDA, they would be able to select between 1-7. The maximum number would be 17 because there are 17 possible selections.

I tried using a nested-if function such as =IF(H2="Revenue",COUNTA(REVENUE),IF(H2="Sales",COUNTA(SALES), etc. but it does not appear to work for a drop-down list.

[johncheng435]

Please see below

[johncheng435]

see attached

[menem]

you may use OFFSET together with COUNTA to create drop down list.

Regards.

Note: all data must not have blank row between them.
for more information please attach your sample file.

or you may review this post...
https://www.excelforum.com/excel-for...ml#post5657376

[johncheng435]

Thank you! I managed to figure it out