Need help copying custom spreadsheet formats to 2014

[jbroulette]

Let me start by stating that I am at best an Excel Rookie and that I wish I had 10% of the knowledge that most of you here have.

Last year I built a custom spreadsheet for my little trucking business that is just perfect for me. I am trying to copy the entire setup for this year and am having problems.

It has three pages, a 2013 Main page and two accompanying pages that have data on each trucks earnings and expenditures. The data on the main page tabulates data from all three, along with general expenditures. I tried to just copy each page, clear the contents and then go in and change the formulas that reference the 2013 pages to now use data from the 2014 pages. That didn't work. The columns did not zero out after I cleared the contents of the pages and, when I entered some trial figures into the spreadsheets, it was ignored buy the formulas.

Any help figuring this out will be greatly appreciated.

[NickyC]

Hi

did you copy the sheets into a new workbook? If so, the formulas may have retained their links to the old one.

Or, maybe the easiest way to do this is to save your old file with a new name and then change the names of the sheets (tabs) from eg 2013 to 2014. All the internal fomulas should then update automatically.

[jbroulette]

I just added sheets(tabs), and copied the 2013 sheets to the new tabs. The formulas didn't update. Maybe I need to rename the tabs AFTER copying the data over?

[NickyC]

yes, renaming after you have copied the sheets should result in your formulas updating automtically to the new names. Give that a try.

[jbroulette]

Nope, I had to go in and change the formulas such as = SUM(D5:D999)+('2013 Ken'!D2+'2013 Pete'!D2) to = SUM(D5:D999)+('2014 Ken'!D2+'2014 Pete'!D2). Then I just noticed that the "copy" isn't a true copy as certain characteristics didn't reproduce, such as frozen columns and rows.

[NickyC]

did you try just saving your old file with a new name and then changing the sheet names? That should give you an identical file.

other suggestions:

- you coud do a search and replace on each sheet to change all instances of 2013 in formulas to 2014, without needing to change each formula by hand

- when copying, rather than creating a new sheet and copying the content of an old sheet onto it, you can right click the sheet's tab and copy the whole thing. That should give you an identical sheet

[jbroulette]

well, I got it with your help but it was in a roundabout method. I copied the entire file and renamed the sheets, cleared the data and verified they were working properly. Then I just sent them back over to the original workbook. There should be an easier way but this one worked. Thank you for the help. Sorry for being so Excel illiterate.

[NickyC]

glad you got there
if you don't have any more queries, could you mark the thread as "solved" so other people know you don't need more help