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

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

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
>
>

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