Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
help with setting up dynamic name ranges
Hi all,
Please help me with defining a name range using offset. I need to set up a name per column using dynamic range. Each Column starts at row 7. Data are appended to row (and it will grow and I don't know how many rows before hand, and the column does not contain empty cell. Thanks, |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
help with setting up dynamic name ranges
Jeff
See Debra Dalgleish's site for instructions. http://www.contextures.on.ca/xlNames01.html#Dynamic Gord Dibben MS Excel MVP On Thu, 21 Sep 2006 16:36:02 -0700, Jeff wrote: Hi all, Please help me with defining a name range using offset. I need to set up a name per column using dynamic range. Each Column starts at row 7. Data are appended to row (and it will grow and I don't know how many rows before hand, and the column does not contain empty cell. Thanks, |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
help with setting up dynamic name ranges
Gord,
Thanks for answering, but that didn't exactly solve my problem. The example =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1) works only when range starts from column 1. However, I'll need the formula which would starts from row 7 (row 1-6 maybe blank or with data). There isn't a way for my to set the height. Just wondering if there is a generic formula for that. Say, counting for row7 til the end of the row (of the column). Thanks, "Gord Dibben" wrote: Jeff See Debra Dalgleish's site for instructions. http://www.contextures.on.ca/xlNames01.html#Dynamic Gord Dibben MS Excel MVP On Thu, 21 Sep 2006 16:36:02 -0700, Jeff wrote: Hi all, Please help me with defining a name range using offset. I need to set up a name per column using dynamic range. Each Column starts at row 7. Data are appended to row (and it will grow and I don't know how many rows before hand, and the column does not contain empty cell. Thanks, |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
help with setting up dynamic name ranges
The formula is an example that you can adapt to your workbook. For
example, to create a dynamic range in column B, change the references, and subtract the count of items in the header rows: =OFFSET(Sheet1!$B$7,0,0,COUNTA(Sheet1!$B:$B) -COUNTA(Sheet1!$B$1:$B$6),1) Jeff wrote: Gord, Thanks for answering, but that didn't exactly solve my problem. The example =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1) works only when range starts from column 1. However, I'll need the formula which would starts from row 7 (row 1-6 maybe blank or with data). There isn't a way for my to set the height. Just wondering if there is a generic formula for that. Say, counting for row7 til the end of the row (of the column). Thanks, "Gord Dibben" wrote: Jeff See Debra Dalgleish's site for instructions. http://www.contextures.on.ca/xlNames01.html#Dynamic Gord Dibben MS Excel MVP On Thu, 21 Sep 2006 16:36:02 -0700, Jeff wrote: Hi all, Please help me with defining a name range using offset. I need to set up a name per column using dynamic range. Each Column starts at row 7. Data are appended to row (and it will grow and I don't know how many rows before hand, and the column does not contain empty cell. Thanks, -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
help with setting up dynamic name ranges
Debra,
The problem with subtracting the header rows is... not all header rows contain data. e.g. b2 to b4 could be empty... Thanks, "Debra Dalgleish" wrote: The formula is an example that you can adapt to your workbook. For example, to create a dynamic range in column B, change the references, and subtract the count of items in the header rows: =OFFSET(Sheet1!$B$7,0,0,COUNTA(Sheet1!$B:$B) -COUNTA(Sheet1!$B$1:$B$6),1) Jeff wrote: Gord, Thanks for answering, but that didn't exactly solve my problem. The example =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1) works only when range starts from column 1. However, I'll need the formula which would starts from row 7 (row 1-6 maybe blank or with data). There isn't a way for my to set the height. Just wondering if there is a generic formula for that. Say, counting for row7 til the end of the row (of the column). Thanks, "Gord Dibben" wrote: Jeff See Debra Dalgleish's site for instructions. http://www.contextures.on.ca/xlNames01.html#Dynamic Gord Dibben MS Excel MVP On Thu, 21 Sep 2006 16:36:02 -0700, Jeff wrote: Hi all, Please help me with defining a name range using offset. I need to set up a name per column using dynamic range. Each Column starts at row 7. Data are appended to row (and it will grow and I don't know how many rows before hand, and the column does not contain empty cell. Thanks, -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
help with setting up dynamic name ranges
The formula doesn't subtract the header rows. It subtracts the count of
items in the header rows. Did you try the formula? Jeff wrote: Debra, The problem with subtracting the header rows is... not all header rows contain data. e.g. b2 to b4 could be empty... Thanks, "Debra Dalgleish" wrote: The formula is an example that you can adapt to your workbook. For example, to create a dynamic range in column B, change the references, and subtract the count of items in the header rows: =OFFSET(Sheet1!$B$7,0,0,COUNTA(Sheet1!$B:$B) -COUNTA(Sheet1!$B$1:$B$6),1) Jeff wrote: Gord, Thanks for answering, but that didn't exactly solve my problem. The example =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A), 1) works only when range starts from column 1. However, I'll need the formula which would starts from row 7 (row 1-6 maybe blank or with data). There isn't a way for my to set the height. Just wondering if there is a generic formula for that. Say, counting for row7 til the end of the row (of the column). Thanks, "Gord Dibben" wrote: Jeff See Debra Dalgleish's site for instructions. http://www.contextures.on.ca/xlNames01.html#Dynamic Gord Dibben MS Excel MVP On Thu, 21 Sep 2006 16:36:02 -0700, Jeff wrote: Hi all, Please help me with defining a name range using offset. I need to set up a name per column using dynamic range. Each Column starts at row 7. Data are appended to row (and it will grow and I don't know how many rows before hand, and the column does not contain empty cell. Thanks, -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
Dynamic Ranges: Speed Issue | Excel Worksheet Functions | |||
Solver and dynamic ranges | Excel Worksheet Functions | |||
Indirect and dynamic ranges | Excel Worksheet Functions | |||
Named dynamic ranges, copied worksheets and graph source data | Charts and Charting in Excel |