Can cell reference (array) in VLOOKUP be pulled from another source than actual array?

Question

Hi all,

I'm working with VLOOKUPS in a sales spreadsheet that pull data from other worksheets in the workbook.

The challenge is that the source data is an exported from a 3rd party platform, and so the relative location of the array is different week over week (the columns)

There are a slew of metrics that each week displays, mostly simple arithmatic of the data on the source worksheet, or from the "dashboard" sheet itself that I'm talking about.

The challenge is that when I cut and paste a new column for a new week, the easiest way I've found to update the cell references is to FIND/REPLACE the previous week's array with the current week's array. It's always a little tedious, and often a little worrisome if it's catching all the references and not accidentally changing unintended references.

THE QUESTION:

Is there a way to have the array, as referenced in the VLOOKUP formula, refer not to the array itself (=VLOOKUP("Pizza",'11.01.15 Sales'!A123:J153,6,FALSE)

But rather to the contents of ANOTHER CELL, where I can simply put the relavent array, so ALL formulas in that week know to look to that cell for the location of the array in question:

(=VLOOKUP("Pizza",Z99,6,FALSE) 

where Z99 is '11.01.15 Sales'!A123:J153

This way I really can just copy and paste each week, and as long as the correct array reference is in the column's row (AB99, AC99, AD99...), all relevant formulas in that column will refer to the same array.

Thanks all!

Answer 1

I'm not sure I understand. If the cell contains the fully qualified sheet name and the array reference as text it oughtbtovwork.  Perhaps a better method would be to use a name to identify the array.

Answer 2

Thanks Bob,

Certainly a point in the right direction.

I'm still either struggling with syntax accuracy, or that my contents of "z99" also contain the array, not just the tab reference. Heres the actual formulas as I'm TRYING to articulate them:

In a cell I'm trying to get the "Food Total" from "11.01.15 Sales" tab

In my 11.01.15 column, there are a lot of references to the same array for different metrics, so I want to put the tab AND array in one cell, say C39, and have all relevant formulas refer to that data set, which is:

11.01.15 Sales'!A123:J153

How can I get the INDIRECT function to refer to a tab and a specific array?

I'm done some digging and either get caught up in shell upon shell of quote marks, or I got a "volatile" result on one try (I'm running Mac Excel 2008)

Thanks!

Answer 3

Yes, try =vlookup("pizza",indirect(z99),6,false)