Dyn Named Range = Last Used Cell in Col & RC in Formula

[carsto]

Can you use Row & Column numbers in a Formula the way you can in VBA?

I want to do the same as Range(Cells(5,2)) in VBA EXCEPT in a Formula
because I want to use named ranges for the Row & column entries.
(And I don't want to have to run a macro every time a change is made. The spreadsheet is huge enough already. It's slow on my machine & I have the biggets baddest PC in the company!)

Using Formulas only, (not VBA) I would like to create a Dynamic Named Range, LastUsedRow, which is the ROW NUMBER of the Last Used Cell in Column C
(it would = 470)

Also I have an existing Named Range HeaderRowNum (it = 16)

Currently I have a LOT of formulas like:
=SUMPRODUCT(($E17:$E470)*(--(CO17:CO470>0)))

problem is any new data must be added between Rows 17 & 470

So I would like to create dynamic new forumlas to read like:
SUMPRODUCT( (Cells(HeaderRowNum+1,5) : (LastUsed Row,5)) * (--(Cells(HeaderRowNum+1,93) : (LastUsed Row,93))>0) )


I obtained the following from Chip Pearsons site
I can create a Dynamc Range and a LastCell from that Range BUT the Dynamic Range must have an ending point.
DynaRange =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A$2:$A$25),1)
LastCell =OFFSET(Sheet1!$A$2,COUNTA(Sheet1!$A$2:$A$25)-1,0)

I need my Dynamic Range to be limitless (A:A) and unless I'm doing something wrong, the Last Cell doesn't work with a limitless DynaRange

[teylyn]

Hi Carsto,

set up a dynamic range name with this formula:

LastCell =OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A:$A)-1,0)

This'll give you the last cell in column A. With Counta you don't have to specify the end of the counting range, you can have the whole column counted.

If you want to use a range name in your Sumproduct formula, you don't need to specify the start cell and the end cell. Instead you can specify the whole range. The following formula creates a dynamic range for column A, where A1 has the header and below is only data. The range excludes the header:

DynaRangeColA =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,1)

Likewise for Column B:
DynaRangeColB =OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)-1,1)

You can use such dynamic range names in your Sumproduct formula, something like this:

SUMPRODUCT(DynaRangeColA * (DynaRangeColB>0))

cheers

[DonkeyOte]

The only drawbacks of the COUNTA/COUNT methods are if/when you have blanks interspersed in your data... eg

A1: 2
A2: 3
A3: blank
A4: 3

COUNT/COUNTA(A:A) will return 3 rather than 4 given only 3 numbers ... so using this value as a row variable when generate spurious ranges

If this is an issue for you this thread may be of interest: http://www.excelforum.com/excel-gene...ic-ranges.html

[carsto]

Thank you all for your help.

Teylyn, I should have mentioned the blank cells.

After looking up the significance of 9.99999999999999E+307
I based my solution off DonkeyOte’s link

- I do have blanks in all the columns except the Key Column E. (Match stays on Col E)
- Since my Key Column will always be numeric I didn’t have to go to the “_lastno, _lasttxt, _lastlogic” level.
- My header row isn’t Row 1 so I had to work around that. (-16)

BigNum = 9.99999999999999E+307
Qty_List = OFFSET('Sheet1'!$E$17,0,0,MATCH(BigNum,'Sheet1'!$E:$E)-16,1)
Op_List = OFFSET('Sheet1'!$CG$17,0,0,MATCH(BigNum,'Sheet1'!$E:$E)-16,1)

final solution =SUMPRODUCT(Qty_List*(Op_List>0))


I already have 80+ named ranges in this spreadsheet and this solution will add 30+ more.
Initially, I didn’t want to add all those named ranges, I just wanted to refer to the column and first and last rows, but I have gotten over that hang-up!


Re BigNum in MATCH: Aladin Akyurek explained it well here:
http://www.mrexcel.com/forum/showthread.php?t=18954



Just for future reference, is there a function in Excel that allows you to refer to a cell by it’s Row & Col number rather than the address, as you would in VBA Range(Cells(5,2))?

[DonkeyOte]

You could just use an INDEX based approach, eg:

RefersTo:
=OFFSET('Sheet1'!$E$17,0,0,MATCH(BigNum,'Sheet1'!$E:$E)-16,1)

could be

RefersTo:
=Sheet1!$E$17:INDEX(Sheet1!$E:$E,MATCH(BigNum,Sheet1!$E:$E))

Re referring to cells in R1C1 fashion, not directly (unless you opt for R1C1 option) ... you can use INDEX, eg:

=INDEX(1:65536,2,3) --> C2

but that's not ideal... you can also use INDIRECT with R1C1, eg:

=INDIRECT("R2C3",FALSE) --> C2

or with ADDRESS

=INDIRECT(ADDRESS(2,3)) --> C2

but INDIRECT (like OFFSET) is Volatile so is best avoided... INDEX is generally best but it all depends on the context in which you intend to use it.

Re: lots of named ranges etc... if they are of the same dimension you could have one encompassing all columns and use INDEX to select the appropriate column ?
=SUM(INDEX(_namedrange,0,1)) ... would SUM column 1 of your _namedrange

[carsto]

thank you!