Link to home
Start Free TrialLog in
Avatar of jwhst10

asked on

Dynamic Named Range for Unorganized List in Excel

I have a spreadsheet where I have a list of individuals on 1 tab [Tab 1] based on information from a different tab [Tab 2]. On a third tab [Tab 3] I have a report generator based on the summarized information on [Tab 1]. The drop-down lists to generate the report on [Tab 3] are dynamic as each filter lists a different set of data from [Tab 1]. I set up the dynamicly named ranges via a macro using the =OFFSET(Tab1!$D$1042,0,0,Tab1!$D$1040,1) where it can grow based on the count in the cell with the count.

My problem is, is the count is a CountA and counts anything that is not blank so say it states 3 as not being blank and those cells are in D1043, D1044 and D1050. The list will show 3 in the drop down however it will only show the values from D1043, D1044 and D1045 rather than displaying correctly only the cells with values.

Do I need to organize the list before I set up the dynamic range or is there a way to only select cells with values?
Avatar of NBVC
Flag of Canada image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial