Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to change sheet names via formula | Excel Discussion (Misc queries) | |||
Sumif referring to range names formulas not updating | Excel Worksheet Functions | |||
Updating Range Names | Excel Discussion (Misc queries) | |||
Problems with updating category names in pivot tables | Excel Discussion (Misc queries) | |||
Updating Names | Excel Discussion (Misc queries) |