Automatically change tab reference

[Guest]

I have 2 Excel files. One named "Actual" and one named "Budget". In each
file are tabs for the months of the year, "Jan" "Feb" "Mar" etc. The Budget
file Jan tab cell A1 is referencing the Actual file Jan tab cell A1. The
other months tabs are added as the year goes on, so in February, the Actual
file Jan tab is copied to create a new Feb tab within the Actual file. The
information in the Actual file Feb tab cell A1 is adjusted.

What I would like to do is then to copy the Budget file Jan tab to create a
Feb tab within the Budget file. I would like the Budget file Feb tab cell A1
to automatically adjust it's reference to now reference the Actual file Feb
tab cell A1 (instead of the original reference of Actual file Jan tab cell
A1).

Lotus 123 has this functionality built into it's software. If you have tabs
named "Jan" "Feb" "Mar", it recognizes the sequence and when a "New Sheet" is
added automatically calls it "Apr" and changes any cells referencing "Jan"
"Feb" or "Mar" to "Apr".

Could someone please help me do this in Excel. Thank you in advance.

 

[Gordon]

I have 2 Excel files. One named "Actual" and one named "Budget". In each
file are tabs for the months of the year, "Jan" "Feb" "Mar" etc. The Budget
file Jan tab cell A1 is referencing the Actual file Jan tab cell A1. The
other months tabs are added as the year goes on, so in February, the Actual
file Jan tab is copied to create a new Feb tab within the Actual file. The
information in the Actual file Feb tab cell A1 is adjusted.

What I would like to do is then to copy the Budget file Jan tab to create a
Feb tab within the Budget file. I would like the Budget file Feb tab cell A1
to automatically adjust it's reference to now reference the Actual file Feb
tab cell A1 (instead of the original reference of Actual file Jan tab cell
A1).

Lotus 123 has this functionality built into it's software. If you have tabs
named "Jan" "Feb" "Mar", it recognizes the sequence and when a "New Sheet" is
added automatically calls it "Apr" and changes any cells referencing "Jan"
"Feb" or "Mar" to "Apr".

Could someone please help me do this in Excel. Thank you in advance.

Why don't you simplify matters, and have one workbook with two
worksheets named "Actual" and "Budget". In the "Budget" sheet, list the
budgets for each month in separate columns. You can then set a formula
for the budget year-to-dare according to the month number.
In the "Actual" sheet you set up a calendarised Actual figures by month
and then set up VLOOKUPs such that when you change the Month number,
you get Month Actual versus Month Budget and Year to Date Actual versus
Year to date budget in either a separate worksheet or on the same one.
Much simpler, doesn't require the copying of sheets and you can make a
copy of the entire workbook for the next year.
If you would like a sample I might have one!

 

[Guest]

Thank you Gordon for the quick response!

Your suggestion is definitely a possibility! I understand what you're
saying and it sounds like after the initial construction, that way would be
fairly easy to maintain.

The description I gave in my original question is from inherating some lotus
files that used the methodology I described. I was hoping to easily convert
these into excel files. Each tab has about 10 columns of information and
formulas, so putting them all into one sheet could become rather wide, but I
like the added efficiency your scenerio descibed.

I wouldn't mind seeing an example if you had one on hand. My email is
(e-mail address removed)

Thanks again.