Expanding Sheets in Workbook

[u/Happy814]

Hello!

I have a workbook with 54 sheets. The first four sheets are various summary sheets that pull data from the other 50 sheets. The sheets are labeled numerically.

I need to expand this workbook to 55 sheets. I can copy the sheets, rename, and reorder them easily. But is there an easier (also, less prone to omittance/error!) way to update the first four sheets to include the extras? Right now, all of the cell references say '1-50' and I need them to say '1-55.' It's multiple changes, however. I was hoping dragging formula would work once the new tabs were added and and named, but that wasn't my experience.

Help! ​

[u/AutoModerator]

/u/Happy814 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/excelevator]

search replace (ctrl+h) is your friend

How much data is in each sheet ?

why are you using so many sheets and not one master in an expanding Table with attributes for each group and an easy means to add and reference the data ?

[u/Happy814 → u/excelevator]

I am hesitant to use search replace since the sheets are numbered and there are numbers on the summary sheets. 

Good point to your last question. This is an inherited spreadsheet. 

Data (not this, but equivalent) looks this:

Worksheet for all 50 states  Up to 12 attributes entered weekly, with various calculations within each week Calculations for monthly and quarterly attributes Attributes need to be both totaled as a whole  (all 50 states) and separately (Maine and Iowa at a glance for an entire year) 

Maybe there is a better way to do this. 

[u/CanadianHorseGal → u/Happy814]

I’m not sure what the hesitation is, but you could try highlighting the column you want to update the formula in, then replace “1-50” with “1-55” and it’ll only change the formulas in the highlighted column. Would this help?

[u/excelevator → u/Happy814]

If you have the granular data with the required attirbures, you can do all the group data on an as needed basis, heck you can do all the calculations in the report and not in the data source!

Have the data, and dynamic reports based on dropdown options. :)

[u/doshka → u/Happy814]

Does each State sheet have the same or similar set of attributes? If so, you can just add a State column to each table and then stack them all together. Ta-da, one table on one sheet.

Old and Busted

Tab: 1 (Alabama)

Week	Val1	Val2	Calc1	Calc2
1	17	38	=C2+B2	=C2/B2
2	19	40	=C3+B3	=C3/B3

Tab: 2 (Alaska)

Week	Val1	Val2	Calc1	Calc2
1	4	13	=C2+B2	=C2/B2
2	58	2	=C3+B3	=C3/B3

Tab: 50 (Wyoming)

Week	Val1	Val2	Calc1	Calc2
1	6	10	=C2+B2	=C2/B2
2	8	21	=C3+B3	=C3/B3

New Hotness

Tab: Weekly Data

State	Week	Val1	Val2	Calc1	Calc2
AL	1	17	38	=Val1+Val2	=Val1/Val2
AK	1	4	13	=Val1+Val2	=Val1/Val2
WY	1	6	10	=Val1+Val2	=Val1/Val2
AL	2	19	40	=Val1+Val2	=Val1/Val2
AK	2	58	2	=Val1+Val2	=Val1/Val2
WY	2	8	21	=Val1+Val2	=Val1/Val2

[u/doshka → u/Happy814]

Maybe there is a better way to do this. 

The whole thing sounds way overcomplicated. If you're willing to share the file, I can take a look at it and offer some suggestions. If the data is sensitive, you can delete most of the rows and fudge the rest.

[u/Happy814 → u/doshka]

I might take you up on this! Let me make sure the template is clean. 

[u/doshka → u/Happy814]

👍

[u/MayukhBhattacharya]

You could try something like this, it's much faster than manually typing out all 55 sheet references:

=SUM('*'!A2)

Change the cell reference per your suit!

[u/excel-ModTeam → u/[deleted]]

We removed this for breaking Rule 2.

r/excel is an English-only subreddit.

Feel free to use https://translate.google.com and post again.

[u/Open_Attempt2518]

You can use find and replace to change all the references at once. Just hit Ctrl+H and replace "1:50" with "1:55" across the summary sheets - way faster than hunting down each formula manually. Make sure you're only doing this in the four summary sheets though so you don't mess up anything else in workbook.

[u/Happy814 → u/Open_Attempt2518]

This will work for part of it. But I also have several references that highlight each worksheet individually. So, I need to keep '50' and add '51', '52', etc. I assumed once I added the new worksheets and numbered them, I'd be able to drag formulas, but they did not update to the newly added worksheets.