Dynamic Range, Data Validation and Address, Match and Offset Funct

[rudawg]

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]

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]

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]

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)