Link to home
Start Free TrialLog in
Avatar of jwhst10
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?
ASKER CERTIFIED SOLUTION
Avatar of NBVC
NBVC
Flag of Canada image

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