Vlookup and Indirect help!!

[Dave Hoff]

Hi All,

I'm trying to create a spread sheet to track a data set that updates
each week.

I plan on each week going to my data sheet and then inserting rows at
the top of the sheet to enter that weeks data, so the current data is
at the top of the sheet and the previous weeks data is pushed down the
sheet.

On the following sheet I have vlookup functions that I want to use
that will always use that same specific cell location - i.e. I want to
always the first row to reference the latest weeks data, so that would
be array A14 etc.

My problem is that when I insert the new rows in the data sheet, the
vlookup's on the second sheet update to follow the change, i.e. now
instead of that vlookup referencing the current week array of A14,
it's now gone to A59. I tried doing $A$1:$D$9 but no luck, would
update those too.

Is there any way to make the lookup always reference the A14 array
so it doesn't shift after I've inserted the new rows?

I've researched the Indirect function and it's not returning a result
- just an error. This is the function I currently have:

VLOOKUP($A$2,(INDIRECT(DATA!A435)),2)

I have a 4 column array and I simply want it to match up from column 1
and give the result from the associated column 2 in that referenced
array.

Cheers in advance!

 

[GS]

Dave Hoff explained on 1/29/2012 :

Hi All,

I'm trying to create a spread sheet to track a data set that updates
each week.

I plan on each week going to my data sheet and then inserting rows at
the top of the sheet to enter that weeks data, so the current data is
at the top of the sheet and the previous weeks data is pushed down the
sheet.

On the following sheet I have vlookup functions that I want to use
that will always use that same specific cell location - i.e. I want to
always the first row to reference the latest weeks data, so that would
be array A14 etc.

My problem is that when I insert the new rows in the data sheet, the
vlookup's on the second sheet update to follow the change, i.e. now
instead of that vlookup referencing the current week array of A14,
it's now gone to A59. I tried doing $A$1:$D$9 but no luck, would
update those too.

Is there any way to make the lookup always reference the A14 array
so it doesn't shift after I've inserted the new rows?

I've researched the Indirect function and it's not returning a result
- just an error. This is the function I currently have:

VLOOKUP($A$2,(INDIRECT(DATA!A435)),2)

I have a 4 column array and I simply want it to match up from column 1
and give the result from the associated column 2 in that referenced
array.

Cheers in advance!

You could give the lookup range a dynamic defined name so it always
refs the same cells RELATIVE to a fixed (absolute) location. This will
require using the header row as the absolute location and define the
name to always ref the 4 rows below it. Then use this dynamic defined
name as your lookup range. This means the data on your data sheet will
start at row2, and the dynamic range will ref A25 now.

 

[Pete_UK]

You need to put the reference within quotes for the INDIRECT function
to work, like this:

VLOOKUP($A$2,INDIRECT("DATA!A435"),2)

although if you are only getting data from column 2 you only need to
define the table as "DATA!A4:B35"

Hope this helps.

Pete