Update reference in multiple formulas

L

Luke

Hi,
I’ve set up a few tables to with formulas to extract data from a large
spread sheet which is produced every month. I’m using Vlookup but every
month the “table_array†part of the formula will need to be changed. Is
there a way to update the “table_array†part for all the formulas all at once?
Many thanks
Luke
 
W

Wigi

Hi

You could use a named range to stand for the table_array. It could as well
be dynamic if you want.
 
S

Sebation.G

try with Indirect formula & column formula
e.q. in your destinational sheet u can use the formula :
=INDIRECT("[book.name]"&COLUMN()&"!A1")
then drag it toward the right

HTH
 
N

Niek Otten

Hi Luke,

Define a name for the range (Insert>Name>Define) and use that in your formulas. You'll only have to update the definition.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi,
| I've set up a few tables to with formulas to extract data from a large
| spread sheet which is produced every month. I'm using Vlookup but every
| month the "table_array" part of the formula will need to be changed. Is
| there a way to update the "table_array" part for all the formulas all at once?
| Many thanks
| Luke
|
 
Top