Cell number in formula changes based on data array?

[uberathlete]

Hi guys, I have a question. Say in Sheet 1 I have a formula like this:

=VLOOKUP(F2,$A$2:$D$7,2,FALSE)

which depends on the data array $A$2:$D$7.

I will use the same formula in other sheets but the data array will change. Like for example, in Sheet 2, the data array is $A$2:$D$15, so the formula I need would be =VLOOKUP(F2,$A$2:$D$15,2,FALSE).

And so on and so forth for other sheets that have a different data array in terms of vertical size. Since there are so many sheets, I don't really want to manually change the array every time. So is there an easy way to change it automatically? Basically I just want the array to change based on the vertical size of the array regardless of the formula.

Any suggestions would be greatly appreciated. Thanks!

 

[dave3009]

Hi There

Try naming your Range then referncing it in your formula using

INDIRECT(CellContainingDataArrayName) instead of the array itself.

HTH

Dave

 

[uberathlete]

Hi There

Try naming your Range then referncing it in your formula using

INDIRECT(CellContainingDataArrayName) instead of the array itself.

HTH

Dave

Hi Dave. Sorry, but could you give me an example?

 

[dave3009]

Sure give me 5 minutes to build you a HTML

 

[uberathlete]

Sure give me 5 minutes to build you a HTML

Thanks!

 

[Jonmo1]

Why not just oversize the range to a number larger than any of the sheets? Blank rows do not effect Vlookup Formulas.

=VLOOKUP(F2,$A$2:$D$1000,2,FALSE).

That way, it will work on any sheet that has ranges 1000 rows or smaller.


for that matter, why bother specifying rows at all??

=VLOOKUP(F2,$A:$D,2,FALSE).

 

[dave3009]

Here I have two Arrays separated by the VLOOKUP functions

The top array is called Arrays1, the bottom one Arrays2, you will note the INDIRECT function referencing the cell with the named range, this means you can VLOOKUP any value in any Named range you have

Book1
	A	B	C	D	E
1
2		Bob	1	5
3		John	2	6
4		Dave	3	7
5		Mark	4	8
6
7
8	Arrays1	Bob	1	5
9	Arrays2	John	12	12
10
11
12		Bob	11	15
13		John	12	16
14		Dave	13	17
15		Mark	14	18
16
Sheet1

Hope it helps


Dave

 

[Jonmo1]

dave - somethings wrong with your HTML post, can't see the formulas...

By the way, there's no need to use indirect, you can put the named range directly in the formula..

=VLOOKUP(A1,Arrays1,2,FALSE)

Just need to make sure tools - options - calculation - "Accept Lables In Formulas" is checked.

 

[dave3009]

Sorry about the error, I had a problem with the HTML Maker had to restart Excel. I have resubmitted. There is a column reference error in the bottom right, I would change it but it purely for demonstration purposes.