Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Worksheet function that duplicates End(xlUp)?
Hello, Excelsperts!
I have a SUMPRODUCT function applied to a huge range, some 14000 rows big. This makes calculations a tedious and timely chore. But really, I only need that kind of size for one of my worksheets. The other sheets are all much smaller. Is there some way to say SUMPRODUCT($B$4 to Last row with data in B) rather than $B$4:$B$14200? In a worksheet function, not a macro? Thank you for your time and your brainpower. Arlen |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Worksheet function that duplicates End(xlUp)?
One way you could represent the range of $b$2:$b$14000 with just the
'occupied' cells would be: OFFSET($B$2,0,0,COUNTA($B$2:$B$14000),1) This would mean that there is nothing in the blank cells, including not having formulas. If you have formulas all the way down, you could consider the following: OFFSET($B$2,0,0,13999-COUNTBLANK($B$2:$B$14000),1) -- John C "Arlen" wrote: Hello, Excelsperts! I have a SUMPRODUCT function applied to a huge range, some 14000 rows big. This makes calculations a tedious and timely chore. But really, I only need that kind of size for one of my worksheets. The other sheets are all much smaller. Is there some way to say SUMPRODUCT($B$4 to Last row with data in B) rather than $B$4:$B$14200? In a worksheet function, not a macro? Thank you for your time and your brainpower. Arlen |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Worksheet function that duplicates End(xlUp)?
OFFSET($B$2,0,0,COUNTA($B$2:$B$14000),1)
Using a volatile function will just make it worse. This does the same thing and is not volatile: B2:INDEX(B2:B14000,COUNTA(B2:B14000)) -- Biff Microsoft Excel MVP "John C" <johnc@stateofdenial wrote in message ... One way you could represent the range of $b$2:$b$14000 with just the 'occupied' cells would be: OFFSET($B$2,0,0,COUNTA($B$2:$B$14000),1) This would mean that there is nothing in the blank cells, including not having formulas. If you have formulas all the way down, you could consider the following: OFFSET($B$2,0,0,13999-COUNTBLANK($B$2:$B$14000),1) -- John C "Arlen" wrote: Hello, Excelsperts! I have a SUMPRODUCT function applied to a huge range, some 14000 rows big. This makes calculations a tedious and timely chore. But really, I only need that kind of size for one of my worksheets. The other sheets are all much smaller. Is there some way to say SUMPRODUCT($B$4 to Last row with data in B) rather than $B$4:$B$14200? In a worksheet function, not a macro? Thank you for your time and your brainpower. Arlen |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Understanding .End(xlUp) (1,1) | Excel Discussion (Misc queries) | |||
Find Duplicates and Move to New Worksheet | Excel Worksheet Functions | |||
problem with .end(xlUp).row | Excel Discussion (Misc queries) | |||
When I open a an existing excel worksheet, it duplicates itself a. | Excel Discussion (Misc queries) | |||
ADD Macro - xlup plus xlto right | Excel Worksheet Functions |