CODEWITHSUNDEEP
excel-formulanamed-ranges
H.P.
H.P.

Reputation: 13

Dynamic named range including rows and columns

Little help needed here with excel dynamic named ranges.

I know how to make a dynamic named range that adjusts row height depending on the size of the range (no. rows with data in):

=OFFSET(Helper!$DJ$3,0,0,SUM(--(Helper!$DJ$3:$DJ$30<>"")))

However I find myself working with a large(ish) range of data across multiple columns - about 100 - and I REALLY want to avoid making 100 named ranges.

It must be possible to create a single named range that references the whole data set (DJ3:HE30) and the formula dynamically chooses which column to look at and (as before) chooses how many rows to return in that specific column.

The closest I've got is this:

=OFFSET(Helper!$DJ$3,0,0,SUM(--(Helper!$DJ$3:$DJ$30<>"")),SUM(--(Helper!$DJ$3:$HE$3<>"")))

But this just goes to the last column with data in (which makes sense really as it's doing the same thing with columns as it is with rows).

I'm assuming I need to tweak the starting point of the offset ref and somehow pass that column to the rest of the formula.

These named ranges are going to be used for drop-downs so the first drop-down will be the criteria that needs to be passed on to the second drop-down (that will utilize this new funky named range).

For example - if my first drop-down uses criteria 1,2,3 etc - my second drop-down would need to display results like this:

So the lists containing the letters in this example are all in individual columns, so the dynamic range needs to identify which column to use to pull the right list.

Also, with the number of columns I think I need to make this an INDEX rather than an OFFSET.

Can anyone point me in the right direction?

Thanks in advance, much appreciated!

Upvotes: 0

Views: 912

Answers (1)

H.P.
H.P.

Reputation: 13

It looks like I have managed to solve this one myself.

What I have done is create 2 named ranges as helper cells - Range_Start and Range_End. These ranges are single cells that contain an ADDRESS ref using MATCH to pick out the start and end of the column I need.

I then pass these named ranges to the formula I showed in my question but using INDIRECT to convert the cells to addresses:

=INDIRECT(Range_Start):INDEX(INDIRECT(Range_Start):INDIRECT(Range_End),COUNTA(INDIRECT(Range_Start):INDIRECT(Range_End)))

Works like a dream :-)

Upvotes: 0

Related Questions