Updating formula as tab names change
 

hi

I have created a top level summary spreadsheet for accounts analysis that
adds all the values from the various tabs in the workbook (mainly using
vlookup). This works fine, but when next year comes, I would want to change
all the 08 to 09s on the tabs, but that would result in the vlookups not
working. I was thinking the best way for this was to reference the tab names
on 'data' workbook, and use this value in the vlookup formulae, but not sure
how that would work.
e.g. tabs currently called 'May 08 - Jul 08'
formulae in 'total' worksheet is
VLOOKUP($A5,'May 08 - Jul 09'!$A$4:$Q$130,16,FALSE)
and this formula has been used many times within this workbook.
So for nexts years spreadsheet, i would only want to change the tab names
(or a reference to it), and have all the formulae not affected.
At the moment I can change all the vlookup formulae as it will be a one
off, but dont want to have to do it every year - as i probably will not be
the one looking after it.
thanx

Updating formula as tab names change
 

If you change the tab name, the formula will change automatically.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"SU123" wrote in message ...
| hi
|
| I have created a top level summary spreadsheet for accounts analysis that
| adds all the values from the various tabs in the workbook (mainly using
| vlookup). This works fine, but when next year comes, I would want to change
| all the 08 to 09s on the tabs, but that would result in the vlookups not
| working. I was thinking the best way for this was to reference the tab names
| on 'data' workbook, and use this value in the vlookup formulae, but not sure
| how that would work.
| e.g. tabs currently called 'May 08 - Jul 08'
| formulae in 'total' worksheet is
| VLOOKUP($A5,'May 08 - Jul 09'!$A$4:$Q$130,16,FALSE)
| and this formula has been used many times within this workbook.
| So for nexts years spreadsheet, i would only want to change the tab names
| (or a reference to it), and have all the formulae not affected.
| At the moment I can change all the vlookup formulae as it will be a one
| off, but dont want to have to do it every year - as i probably will not be
| the one looking after it.
| thanx

Updating formula as tab names change
 

Thanx, that is brilliant, and has saved me lots of time!

much appreciated!


"Niek Otten" wrote:

If you change the tab name, the formula will change automatically.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"SU123" wrote in message ...
| hi
|
| I have created a top level summary spreadsheet for accounts analysis that
| adds all the values from the various tabs in the workbook (mainly using
| vlookup). This works fine, but when next year comes, I would want to change
| all the 08 to 09s on the tabs, but that would result in the vlookups not
| working. I was thinking the best way for this was to reference the tab names
| on 'data' workbook, and use this value in the vlookup formulae, but not sure
| how that would work.
| e.g. tabs currently called 'May 08 - Jul 08'
| formulae in 'total' worksheet is
| VLOOKUP($A5,'May 08 - Jul 09'!$A$4:$Q$130,16,FALSE)
| and this formula has been used many times within this workbook.
| So for nexts years spreadsheet, i would only want to change the tab names
| (or a reference to it), and have all the formulae not affected.
| At the moment I can change all the vlookup formulae as it will be a one
| off, but dont want to have to do it every year - as i probably will not be
| the one looking after it.
| thanx