How do I make a named cell look to an address from a formula?

Hi.

Firstly, I trust that the data in that column contains null strings ("") as a result of formulas in those cells? Otherwise there are far more efficient set-ups for determining the last non-blank row.

If you define e.g. LRow in Name Manager as:

=INDEX($C:$C,AGGREGATE(14,6,ROW($C$3:$C2000)/($C$3:$C2000<>""),1))

then:

=SUM(C1:LRow)

will be a perfectly valid syntax.

However, I have taken the liberty to make absolute the reference to column C in this definition. I'm not sure where you intend to use any formulas which reference LRow, though of course I'm sure you're aware that formulas in Defined Names behave in precisely the same way as those within the worksheet, i.e. they take into account relative/absolute referencing.

Regards

Originally Posted by XOR LX

Hi.
...

Whats the catch here?
If used in a cell the following returns the same thing as your index variant:

=INDIRECT(ADDRESS(AGGREGATE(14, 6, ROW('QV3'!C$3:C2000)/('QV3'!C$3:C2000<>""), 1),COLUMN(C1)))

Why does the index work but not this?

As for the last cell solution, when i was searching for it, some of them broke down on empty cells this is the first one i got that performed.
What do you suggest if i got blank cells in the range and NOT formulas ending resulting in ""?

And I take it index is the only way to convert an address for the name so it would use it?

Originally Posted by Polymorpher

Whats the catch here?
If used in a cell the following returns the same thing as your index variant:

=INDIRECT(ADDRESS(AGGREGATE(14, 6, ROW('QV3'!C$3:C2000)/('QV3'!C$3:C2000<>""), 1),COLUMN(C1)))

Why does the index work but not this?

ADDRESS returns a text string which merely looks like a range reference. Using INDIRECT won't help since that then returns the actual cell contents. INDEX, however, is capable of returning an actual range reference (as well as a cell content, depending on how the function is employed).

Originally Posted by Polymorpher

As for the last cell solution, when i was searching for it, some of them broke down on empty cells this is the first one i got that performed.
What do you suggest if i got blank cells in the range and NOT formulas ending resulting in ""?

That would be dependent upon whether the entries in that range were purely text, purely numeric or a mixture of both datatypes.

Originally Posted by Polymorpher

And I take it index is the only way to convert an address for the name so it would use it?

See above. In any case, INDEX set-ups are far better in general than ADDRESS/INDIRECT ones, not least since they lack the volatility of the latter.

Regards

Apologies. I stand corrected.

Still prefer INDEX for volatility reasons, however.

Regards

My bad for misleading you folks its not working, i apparently messed up something when setting it up.

The data mix keeps changing in type and composition. I've used match index before for that but i guess its habitual. Its easier to wrap/unwrap the address in INDIRECT( enter (autocorrect, done), than wrap index match in a cell("address",


Its also a flexible beast. Its a fast tweak to find matches 2 positions before the last etc. It always works regardless of data and spaces, i guess thats why i stuck to it as a powerhouse

Originally Posted by Polymorpher

whats the benefit

Efficiency.

Indirect is a volatile function, meaning it recalculates every single time Anything in the book changes, regardless if that change is relevant to that function or not.

Plus, the binary search used by the MATCH function is leaps and bounds faster than AGGREGATE(14, 6, ROW('QV3'!C$3:C2000)/('QV3'!C$3:C2000<>""), 1)
And I mean really fast, like comparing a Turtle to a Cheetah faster.