Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I make a range "dynamic"?
Hello all,
I have a range of cells that I need to reference. This range always starts at B23 and goes to B??? (the last row will be different each time we open the file and load data). What I want to be able to do is enter the number of the last row of this range in B1 and then reference from that cell. Let's say this time I'm using the file, the last row will be 987, so my range I need to reference will need to be B23:B987. I want to enter 987 in B1 and then something like: =SUM(B23:B(& the value in B1)) Any help will be greatly appreciated, Conan Kelly |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I make a range "dynamic"?
Anything wrong with using =SUM(B:B)
Failing that, do you have any gaps at all in the data from B23 to B?? and is there any data in column B below B?? -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------*------------------------------*---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------*------------------------------*---------------- "Conan Kelly" <CTBarbarin at msn dot com wrote in message ... Hello all, I have a range of cells that I need to reference. This range always starts at B23 and goes to B??? (the last row will be different each time we open the file and load data). What I want to be able to do is enter the number of the last row of this range in B1 and then reference from that cell. Let's say this time I'm using the file, the last row will be 987, so my range I need to reference will need to be B23:B987. I want to enter 987 in B1 and then something like: =SUM(B23:B(& the value in B1)) Any help will be greatly appreciated, Conan Kelly |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I make a range "dynamic"?
B1:
=MATCH(9.99999999999999E+307,B23:B65536) =SUM(B23:OFFSET(B23,B1-1,0)) Conan Kelly wrote: Hello all, I have a range of cells that I need to reference. This range always starts at B23 and goes to B??? (the last row will be different each time we open the file and load data). What I want to be able to do is enter the number of the last row of this range in B1 and then reference from that cell. Let's say this time I'm using the file, the last row will be 987, so my range I need to reference will need to be B23:B987. I want to enter 987 in B1 and then something like: =SUM(B23:B(& the value in B1)) Any help will be greatly appreciated, Conan Kelly |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I make a range "dynamic"?
Another option:
=SUM(B23:INDEX(B23:B65536,B1)) Or, eliminate the use of the formula in B1: =SUM(B23:INDEX(B23:B65536,MATCH(9.9999999999999E+3 07,B23:B65536))) Biff "Aladin Akyurek" wrote in message ... B1: =MATCH(9.99999999999999E+307,B23:B65536) =SUM(B23:OFFSET(B23,B1-1,0)) Conan Kelly wrote: Hello all, I have a range of cells that I need to reference. This range always starts at B23 and goes to B??? (the last row will be different each time we open the file and load data). What I want to be able to do is enter the number of the last row of this range in B1 and then reference from that cell. Let's say this time I'm using the file, the last row will be 987, so my range I need to reference will need to be B23:B987. I want to enter 987 in B1 and then something like: =SUM(B23:B(& the value in B1)) Any help will be greatly appreciated, Conan Kelly |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I make a range "dynamic"?
Another option
Don't worry about using B1 and =Sum(B:B)-Sum(B1:B22) It elimnates the '9.9999999999999E+307' that I know Biff likes so much <bg Cheers RES |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I make a range "dynamic"?
It elimnates the '9.9999999999999E+307' that I know Biff likes so much
<bg argh! <vbg Biff wrote in message ... Another option Don't worry about using B1 and =Sum(B:B)-Sum(B1:B22) It elimnates the '9.9999999999999E+307' that I know Biff likes so much <bg Cheers RES |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I make a range "dynamic"?
"Biff" wrote: It elimnates the '9.9999999999999E+307' that I know Biff likes so much <bg argh! <vbg Biff wrote in message ... Another option Don't worry about using B1 and =Sum(B:B)-Sum(B1:B22) It elimnates the '9.9999999999999E+307' that I know Biff likes so much <bg Cheers RES |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I create a schedule from a list of dates ? | Charts and Charting in Excel | |||
How to make excel not round real numbers when making a histogram? | Charts and Charting in Excel | |||
How to Select a relative range with Using "Find" and Offset() | Excel Discussion (Misc queries) | |||
Cannot Expand Named Range - when size of the Range exceeds | Excel Discussion (Misc queries) | |||
How to dynamically reference a dynamic named range | Excel Worksheet Functions |