UI Analog of EntireRow

[shg]

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?

[MarvinP]

shg,

Wouldn't the user simply select the range of cells to define their region?

[shg]

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?

[MarvinP]

In 1985 I tested pfs:Plan (a spreadsheet that didn't make it). It had "natural language" formulas where you could use the column heads in formulas. So a formula in the Totals column could say = Quantity * Price and it worked. Named Ranges come close to what pfs:Plan did.

Are you looking to self define Named ranges and use them more efficently? Is that where you're going?

[teylyn]

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?

[teylyn]

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

[DonkeyOte]

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.)

[teylyn]

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).

[shg]

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.

[DonkeyOte]

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)

[shg]

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.

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

Efficiently, yes, but more specifically, avoiding redundancy.

Thank you all, again.

[MarvinP]

I read a webpage about Named Ranges today and kept thinking about his thread.
The page is http://www.xldynamic.com/source/xld.Names.html

We knew most of the information but I wondered how extensive the formulas in ranges could be. Can you use NamedRanges in Formulas for Named Ranges? I find you can. Also these constant(?) formulas don't show as ranges in the dropdown to the left of function.

What constant names would you want and then what to do with them?

I also find that constant range names can be used in building other constant range formulas. Is this the key?

Does this help or am I way, way behind you guys?

(teylyn - did you get shaken? Lose power? Net connection?)

[teylyn]

(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.

[MarvinP]

Hi shg -
I think you will find this very interesting concerning your question about AI and formulas.
This is exactly what we had in pfs:Plan. Search on "Structured referencing"

Look at: http://blogs.msdn.com/b/excel/archiv...28/486604.aspx
Or http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

Is this where you were going with your question?

[shg]

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.

[daddylonglegs]

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)))

[shg]

=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.