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
Bookmarks