| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic Range, Data Validation and Address, Match and Offset Funct
Hello all,
Perhaps someone will be kind enough to help me. I am creating a project management spreadsheet. What I have done is: 1) On Worksheet "Project list", among others I have column headings of: Row/Col A B M 6 START 7 Project# Project Name Dependent on? 8 001 Get T1 Line Null 9 002 New Logo design Null 18 011 Order Letterhead 002 35 028 Set up Computers 001 41 Asterisk Next new record 42 ---Blank Row--- 43 END 2) I have created a "List" from this table so that each time I add a project name, a new formatted line is inserted at the end of the table and it is automatically numbered in sequence.(001, 002, .....095 etc) using =TEXT(ROW($A1),"000") 3) Above the list, in the first 5 rows of the spreadsheet, I have put general information like the date and the company name. 4) In cell A6, I have typed the word "START" 5) Outside of the list, currently in cell A43, I have typed the word "END". As new projects are added, however, "END" will move down the spreadsheet. Also, I would like to make a template out of this spreadsheet and the number of rows before I get to the "List" might fluctuate. 6) What I am trying to do is incorporate a data validation on col M that references the project #'s in Col A. Each time I add a new record, it's project # should be available to the data validation in Col M. 7) I have named a range as "DynamicRange" and set the Data Validation Source for Col M to "= DynamicRange" Now the puzzle: OFFSET(reference,rows,cols,height,width) =OFFSET('Project List'!$A$8,0,0,33,1) - this works but would need constant updating =OFFSET('Project List'!$A$8,0,0,COUNTA('Project List'!$A$8:$A$65536),1) - this works but I can not add rows above the list or add anything in Col A below the list =OFFSET('Project List'!$A$8,0,0,((MATCH("END",'Project List'!$A:$A,0))-(MATCH("START",'Project List'!$A:$A,0))-4),1) - this also works, but still I can not insert or delete rows above the list without updating the formula I have tried: ADDRESS((MATCH("START",'Project List'!$A:$A,0)+2),1,1,1,"Project List") the result of this formula looks to be exactly what I want: it returns 'Project List'!$A$8 I have plugged this formula in to the OFFSET formula as the reference, =OFFSET((ADDRESS((MATCH("START",'Project List'!$A:$A,0)+2),1,1,1,"Project List")),0,0,((MATCH("END",'Project List'!$A:$A,0))-(MATCH("START",'Project List'!$A:$A,0))-4),1) but all I get is an error. Sorry for the length.......anyone have any ideas. Is there a better way??? Thanks R Excel 2003 on Windows XP |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Dynamic Range, Data Validation and Address, Match and OffsetFunct
Name the cell that contains the heading Project# as StartCell
Name the cell that contains the word End as EndCell For the data validation cells, use the formula: =OFFSET(StartCell,1,0,ROW(EndCell)-ROW(StartCell)-3,1) rudawg wrote: Hello all, Perhaps someone will be kind enough to help me. I am creating a project management spreadsheet. What I have done is: 1) On Worksheet "Project list", among others I have column headings of: Row/Col A B M 6 START 7 Project# Project Name Dependent on? 8 001 Get T1 Line Null 9 002 New Logo design Null 18 011 Order Letterhead 002 35 028 Set up Computers 001 41 Asterisk Next new record 42 ---Blank Row--- 43 END 2) I have created a "List" from this table so that each time I add a project name, a new formatted line is inserted at the end of the table and it is automatically numbered in sequence.(001, 002, .....095 etc) using =TEXT(ROW($A1),"000") 3) Above the list, in the first 5 rows of the spreadsheet, I have put general information like the date and the company name. 4) In cell A6, I have typed the word "START" 5) Outside of the list, currently in cell A43, I have typed the word "END". As new projects are added, however, "END" will move down the spreadsheet. Also, I would like to make a template out of this spreadsheet and the number of rows before I get to the "List" might fluctuate. 6) What I am trying to do is incorporate a data validation on col M that references the project #'s in Col A. Each time I add a new record, it's project # should be available to the data validation in Col M. 7) I have named a range as "DynamicRange" and set the Data Validation Source for Col M to "= DynamicRange" Now the puzzle: OFFSET(reference,rows,cols,height,width) =OFFSET('Project List'!$A$8,0,0,33,1) - this works but would need constant updating =OFFSET('Project List'!$A$8,0,0,COUNTA('Project List'!$A$8:$A$65536),1) - this works but I can not add rows above the list or add anything in Col A below the list =OFFSET('Project List'!$A$8,0,0,((MATCH("END",'Project List'!$A:$A,0))-(MATCH("START",'Project List'!$A:$A,0))-4),1) - this also works, but still I can not insert or delete rows above the list without updating the formula I have tried: ADDRESS((MATCH("START",'Project List'!$A:$A,0)+2),1,1,1,"Project List") the result of this formula looks to be exactly what I want: it returns 'Project List'!$A$8 I have plugged this formula in to the OFFSET formula as the reference, =OFFSET((ADDRESS((MATCH("START",'Project List'!$A:$A,0)+2),1,1,1,"Project List")),0,0,((MATCH("END",'Project List'!$A:$A,0))-(MATCH("START",'Project List'!$A:$A,0))-4),1) but all I get is an error. Sorry for the length.......anyone have any ideas. Is there a better way??? Thanks R Excel 2003 on Windows XP -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Dynamic Range, Data Validation and Address, Match and Offset F
Thank you........works like a charm..........I almost wish it weren't so
simple. Could I have gotten there going the way I was going? Can you use the ADDRESS function as the reference in the OFFSET function? Thanks again "Debra Dalgleish" wrote: Name the cell that contains the heading Project# as StartCell Name the cell that contains the word End as EndCell For the data validation cells, use the formula: =OFFSET(StartCell,1,0,ROW(EndCell)-ROW(StartCell)-3,1) rudawg wrote: Hello all, Perhaps someone will be kind enough to help me. I am creating a project management spreadsheet. What I have done is: 1) On Worksheet "Project list", among others I have column headings of: Row/Col A B M 6 START 7 Project# Project Name Dependent on? 8 001 Get T1 Line Null 9 002 New Logo design Null 18 011 Order Letterhead 002 35 028 Set up Computers 001 41 Asterisk Next new record 42 ---Blank Row--- 43 END 2) I have created a "List" from this table so that each time I add a project name, a new formatted line is inserted at the end of the table and it is automatically numbered in sequence.(001, 002, .....095 etc) using =TEXT(ROW($A1),"000") 3) Above the list, in the first 5 rows of the spreadsheet, I have put general information like the date and the company name. 4) In cell A6, I have typed the word "START" 5) Outside of the list, currently in cell A43, I have typed the word "END". As new projects are added, however, "END" will move down the spreadsheet. Also, I would like to make a template out of this spreadsheet and the number of rows before I get to the "List" might fluctuate. 6) What I am trying to do is incorporate a data validation on col M that references the project #'s in Col A. Each time I add a new record, it's project # should be available to the data validation in Col M. 7) I have named a range as "DynamicRange" and set the Data Validation Source for Col M to "= DynamicRange" Now the puzzle: OFFSET(reference,rows,cols,height,width) =OFFSET('Project List'!$A$8,0,0,33,1) - this works but would need constant updating =OFFSET('Project List'!$A$8,0,0,COUNTA('Project List'!$A$8:$A$65536),1) - this works but I can not add rows above the list or add anything in Col A below the list =OFFSET('Project List'!$A$8,0,0,((MATCH("END",'Project List'!$A:$A,0))-(MATCH("START",'Project List'!$A:$A,0))-4),1) - this also works, but still I can not insert or delete rows above the list without updating the formula I have tried: ADDRESS((MATCH("START",'Project List'!$A:$A,0)+2),1,1,1,"Project List") the result of this formula looks to be exactly what I want: it returns 'Project List'!$A$8 I have plugged this formula in to the OFFSET formula as the reference, =OFFSET((ADDRESS((MATCH("START",'Project List'!$A:$A,0)+2),1,1,1,"Project List")),0,0,((MATCH("END",'Project List'!$A:$A,0))-(MATCH("START",'Project List'!$A:$A,0))-4),1) but all I get is an error. Sorry for the length.......anyone have any ideas. Is there a better way??? Thanks R Excel 2003 on Windows XP -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Dynamic Range, Data Validation and Address, Match and OffsetF
ADDRESS returns a string, not a range reference. Instead, you could use
the INDIRECT function, e.g. INDIRECT("R"&MATCH("START",A:A,0)&"C1",FALSE) rudawg wrote: Thank you........works like a charm..........I almost wish it weren't so simple. Could I have gotten there going the way I was going? Can you use the ADDRESS function as the reference in the OFFSET function? Thanks again "Debra Dalgleish" wrote: Name the cell that contains the heading Project# as StartCell Name the cell that contains the word End as EndCell For the data validation cells, use the formula: =OFFSET(StartCell,1,0,ROW(EndCell)-ROW(StartCell)-3,1) rudawg wrote: Hello all, Perhaps someone will be kind enough to help me. I am creating a project management spreadsheet. What I have done is: 1) On Worksheet "Project list", among others I have column headings of: Row/Col A B M 6 START 7 Project# Project Name Dependent on? 8 001 Get T1 Line Null 9 002 New Logo design Null 18 011 Order Letterhead 002 35 028 Set up Computers 001 41 Asterisk Next new record 42 ---Blank Row--- 43 END 2) I have created a "List" from this table so that each time I add a project name, a new formatted line is inserted at the end of the table and it is automatically numbered in sequence.(001, 002, .....095 etc) using =TEXT(ROW($A1),"000") 3) Above the list, in the first 5 rows of the spreadsheet, I have put general information like the date and the company name. 4) In cell A6, I have typed the word "START" 5) Outside of the list, currently in cell A43, I have typed the word "END". As new projects are added, however, "END" will move down the spreadsheet. Also, I would like to make a template out of this spreadsheet and the number of rows before I get to the "List" might fluctuate. 6) What I am trying to do is incorporate a data validation on col M that references the project #'s in Col A. Each time I add a new record, it's project # should be available to the data validation in Col M. 7) I have named a range as "DynamicRange" and set the Data Validation Source for Col M to "= DynamicRange" Now the puzzle: OFFSET(reference,rows,cols,height,width) =OFFSET('Project List'!$A$8,0,0,33,1) - this works but would need constant updating =OFFSET('Project List'!$A$8,0,0,COUNTA('Project List'!$A$8:$A$65536),1) - this works but I can not add rows above the list or add anything in Col A below the list =OFFSET('Project List'!$A$8,0,0,((MATCH("END",'Project List'!$A:$A,0))-(MATCH("START",'Project List'!$A:$A,0))-4),1) - this also works, but still I can not insert or delete rows above the list without updating the formula I have tried: ADDRESS((MATCH("START",'Project List'!$A:$A,0)+2),1,1,1,"Project List") the result of this formula looks to be exactly what I want: it returns 'Project List'!$A$8 I have plugged this formula in to the OFFSET formula as the reference, =OFFSET((ADDRESS((MATCH("START",'Project List'!$A:$A,0)+2),1,1,1,"Project List")),0,0,((MATCH("END",'Project List'!$A:$A,0))-(MATCH("START",'Project List'!$A:$A,0))-4),1) but all I get is an error. Sorry for the length.......anyone have any ideas. Is there a better way??? Thanks R Excel 2003 on Windows XP -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Printing data validation scenarios | Excel Worksheet Functions | |||
Hybrid Mode
