VLOOKUP & Dynamic Ranges

[tryingexcel]

I am trying to create a dynamic range that
will change each time my project list changes.

I have seen some code that allowed me to capture the range as the following:
=OFFSET($A$1,0,0,COUNTA($A:$A),4)

(In the Define Name box I called it myRange)

What I need to do is: have another workbook
(when it is called from the first workbook) use the VLOOKUP table to be able to use myRange to find the value I want. I keep
getting errors. Any suggestions, I am kind of new at this. Thanks

Below is a sample of the first Workbook

FY 2001 MY Project List
PROJ NBR PROJ NAME BR NBR BR CHIEF
0176 Demographic LAN Support 18 Lau
0902 Division InterProject 02 Kreilick
0906 CPS 44 Tucker
0937 LEHD 04 Prevost


[ This Message was edited by: tryingexcel on 2002-04-03 08:00 ]

[ This Message was edited by: tryingexcel on 2002-04-03 08:20 ]

[Aladin Akyurek]

On 2002-04-03 07:59, tryingexcel wrote:
I am trying to create a dynamic range that
will change each time my project list changes.

I have seen some code that allowed me to capture the range as the following:
=OFFSET($A$1,0,0,COUNTA($A:$A),1)

(except I changed the column from 1 to 4)
In the Define Name box I called it myRange

What I need to do is: have another workbook
(when it is called from the first workbook)use the VLOOKUP table to be able to use myRange to find the value I want. I keep
getting errors. Any suggestions, I am kind of new at this. Thanks



[ This Message was edited by: tryingexcel on 2002-04-03 08:00 ]

The formula you posted does not refer to a sheet in which your 4-column table should be. Be aware of the fact if column A has any blanks interspersed with data, you'll end up with "myRange" which is not computed correctly.

You could post 5 rows (including the column headings/labels) of the range to which "myRange" refers for further help.

[Dave Patton]

Mr. tryingExcel

try using Insert name define

type in a name say rLookup

=OFFSET('8'!$A$1,0,0,COUNTA('8'!$A:$A),4)

This Lookup table is on a sheet named - 8
The Table is 4 columns wide

With the value to be looked up in B6

=VLOOKUP(B6,rLookup,2,0) gives info in Column 2 of Lookup Table


=VLOOKUP(B6,rLookup,4,0) gives info in Column 4 of Lookup Table


Notes.
- do not have any blanks in your Lookup table
- the 4th parameter 0 means you want an exact match
- Do not put any information below Column A of the lookup Table

Revise Sheet name and references as necessary



[ This Message was edited by: Dave Patton on 2002-04-03 08:22 ]

[tryingexcel]

The second workbook that is called in not
in the same directory as the first workbook,
so my VLOOKUP is like this:

=VLOOKUP($C$3,'[Projects.xls]My Project List'!myRange,4,FALSE)

[Aladin Akyurek]

Below is a sample of the first Workbook

FY 2001 MY Project List
PROJ NBR PROJ NAME BR NBR BR CHIEF
0176 Demographic LAN Support 18 Lau
0902 Division InterProject 02 Kreilick
0906 CPS 44 Tucker
0937 LEHD 04 Prevost

The above picked up from your edited post shows that you have text fields in your 8 columns of data.

I'll assume that the sheet that houses your data is called Data, otherwise adjust to suit, and the data start at row 2.

Activate the option Insert|Name|Define.
Enter [/b]EndRow[/b] as name in the Names in Workbook box.
Enter as formula in the Refers to box:

=MATCH(REPT("z",20),Data!$A:$A) [ this replaces the dangerous COUNTA ]

Activate Add.
Enter [/b]LTABLE[/b] as name in the Names in Workbook box.
Enter as formula in the Refers to box:

=OFFSET(Data!$A$2,0,0,EndRow-1,8)

[ which defines a lookup table of 8 columns width ]

Activate OK.

While the WB/file is open, you can use VLOOKUP in another WB. Supposing that the first one is named/saved as MyFile.xls,

=VLOOKUP(E1,MyFile.xls!LTABLE,2,0)

should work.

Aladin