UI Analog of EntireRow

Suppose you have two dynamic ranges, myHdrRow that resolves to D1:H1, and myHdrCol that resolves to A5:A10.

In VBA, you can easily get the body area of the table defined by the extents of the header row and column:

Please Login or Register  to view this content.

How would you define a dynamic range in the same fashion via the UI?

The point of a dynamic range is to let the presence of data define the extents. The header row and column are easy to define that way; I want use their definitions alone to find the range of the body of the table, as in the VBA example.

Know what I mean?

Hmm,

is the start of the dynamic row range and the start of the dynamic column range fixed, or also dynamic? If it is fixed, the intersect table will start in D5, right? Then this

=$D$5:INDEX($1:$1048576,MATCH(99^99,$A:$A,1),MATCH(99^99,$1:$1,1))
or
=$D$5:INDEX($1:$1048576,MATCH("zzzz",$A:$A,1),MATCH("zzzz",$1:$1,1))

defines the extent of the table.

But it's probably not that simple, is it?

OK, with the beginnings of the two ranges also dynamic.

Not pretty.

For numeric column labels

Please Login or Register  to view this content.

for text labels use the "zzzz" instead of BigNum, but who am I telling this.

This works fine for me as a dynamic range name that can be referenced in a formula. In a cell, this monster has to be array entered.

See attached.

cheers

Originally Posted by shg

The header row and column are easy to define that way; I want use their definitions alone to find the range of the body of the table, as in the VBA example.

Without knowing the specifics of how the existing Names are defined perhaps:

Please Login or Register  to view this content.

edit:

To clarify...

Te above of course assumes that myHdrRow and myHdrCol are contiguous (vectors) and I am thus assuming:

a) myHdrRow returns a horizontal vector (row 1) of "dynamic" width (determined by some other factor - eg start/end)
b) myHdrCol returns a vertical vector (col A) of "dynamic" height (determined by some other factor - eg start/end)
c) myTbl is to be the intersect of those ranges (when extended to entire column / row per the VBA ex.)

I knew you would come up with something shorter, DO

The only thing I can say in favour of my suggestion is that it is independent of the workings of the row and column range definitions, assuming that the row and column ranges span from the first populated cell to the last populated cell in the first row (or first column respectively).

T,

=INDEX(areaAZ, MATCH(INDEX(colA, MATCH(TRUE, LEN(colA)<>0, 0)), colA, 0), MATCH(INDEX(row1, 1, MATCH(TRUE, LEN(row1)<>0, 0)), row1, 0)):INDEX(areaAZ, MATCH(big, colA, 1), MATCH(big, row1, 1))


Where's myHdrRow and myHdrCol in that? I want the definition based on the row and column header so there no duplication in the definitions.

DO,

=OFFSET($A$1, ROW(myHdrCol)-1, COLUMN(myHdrRow)-1, ROWS(myHdrCol), COLUMNS(myHdrRow))

Compact, simple, intutive, and ... volatile (should have mentioned that, sorry!). But inspired by that, I'm using

=INDEX(Sheet1!$1:$65536, ROW(myHdrCol), COLUMN(myHdrRow))
:INDEX(Sheet1!$1:$65536, ROW(myHdrCol) + ROWS(myHdrCol) - 1, COLUMN(myHdrRow) + COLUMNS(myHdrRow) - 1)

Ugly and long, but not complicated.

Suggestions for improvement invited and welcome.

Thank you both for responding.

I think if non-volatile is they Key then INDEX:INDEX as you have is about as good as you can get - can't think of a way to shorten
(unless you store the row / column values in additional Names which would seem of little value in this instance)

Originally Posted by MP

So a formula in the Totals column could say = Quantity * Price and it worked.

You can do that today in Excel -- it's an implied intersection:

Please Login or Register  to view this content.

My goal was to have a dynamic range defined on the worksheet by its row and column header, so that it was accessible both via the UI and VBA. Accessible from the UI means you can use formulas (sort of) like this on the worksheet:

=sum(index(tbl, 0, column())

... and accessible from VBA, which would be filling out the body of the table.

Originally Posted by MP

Are you looking to self define Named ranges and use them more efficently?

Efficiently, yes, but more specifically, avoiding redundancy.

Thank you all, again.

(off topic):

teylyn - did you get shaken?

Thanks for asking. No worries. Reportedly, the shake could be felt in my town, but I didn't. I had better things to do at 4.35am, when the quake hit, local time. My heart goes out to all those poor sods who have no power, no water, no walls and/or no roof. Fortunately (so far), there were no fatalities. NZ is good at bouncing back from incidents like these. She'll be right.

Thanks for the links, Marvin. I am passingly familiar with structured references, limited by the fact that I use Excel 2007+ as infrequently as possible. My question has been answered.

Don't know if it's better but a little shorter.........

=INDEX(Sheet1!$1:$65536, ROW(myHdrCol), COLUMN(myHdrRow)):INDEX(Sheet1!$1:$65536, MAX(ROW(myHdrCol)),MAX(COLUMN(myHdrRow)))

=INDEX(Sheet1!$1:$65536, ROW(myHdrCol), COLUMN(myHdrRow))
:INDEX(Sheet1!$1:$65536, ROW(myHdrCol) + ROWS(myHdrCol) - 1, COLUMN(myHdrRow) + COLUMNS(myHdrRow) - 1)


=INDEX(Sheet1!$1:$65536, ROW(myHdrCol), COLUMN(myHdrRow))
:INDEX(Sheet1!$1:$65536, MAX(ROW(myHdrCol)),MAX(COLUMN(myHdrRow)))

Significantly shorter, thank you.