Copying formulas to multiple sheets in a workbook

[MLipke]

I am building a workbook to add the data in one sheet to the data in the next sheet for up to 35 sheets.  I can copy the whole sheet but the formulas to not increment the sheet number to the next sheet.  So if I copy the second sheet to the third sheet the formulas still reference the first sheet.  How do I copy the sheet and increment the sheet number automatically?

[#2]

@MLipke 

You probably need INDIRECT to accomplish this, at least in the situation you describe.

Could you please give a reason for the "up to 35" sheets? What does each represent?

I ask because, especially assuming they are all to be laid out in the same manner, it would be easier to keep them all on one sheet, copying down the same number of rows each time. So, for example, if your first sheet occupies A1:Z26 you could copy that to A31:Z56 and then again to A61:Z86 and so on. Then the references would increment as desired, so long as they're relative and not absolute references.

[#3]

@mathetes I am creating a workbook for a monthly production report.  Each sheet in the workbook represents a production day for the facility.  I am just trying to create the file quickly without having to individually enter the formulas for each "day".

[#4]

@MLipke 

I am creating a workbook for a monthly production report.  Each sheet in the workbook represents a production day for the facility.  I am just trying to create the file quickly without having to individually enter the formulas for each "day".

OK, that's the answer to my question concerning the purpose of your workbook.

You haven't responded to my suggestion, which I'll reiterate in a slightly different form. It's what I'd call a fairly common mistake to think (because it's how we'd do it if we were doing it on paper ledger sheets) that it makes most sense to have a separate tab or sheet for each day of the month. In fact, Excel can work wonders quite easily with a single database of--in your case--production data, including production on various product lines or whatever. Excel would, in fact, produce a monthly  (or month-to-date)(or month by month through the year) report from that single database very easily.  Just add the date to each row of data.

If you're open to re-thinking the basic design, I'd be happy to suggest more how that could work. It would help to see what one day's worth of data might look like, if you can grant access to a copy of your file, placing the file itself (or a mock-up of it) on OneDrive or GoogleDrive with a link here that grants access.

[#5]

Here is a link to the file on Google Drive
https://docs.google.com/spreadsheets/d/1Xtvw3edh00R0QynhJ2eI44b9CpbAIB8V/edit?usp=share_link&ouid=11...

[#6]

I needed to (and have) send you a request for access.

[#7]

I don't know if this is any different.
I probably need an email address. My email is email address removed for privacy reasons.

https://docs.google.com/spreadsheets/d/1Xtvw3edh00R0QynhJ2eI44b9CpbAIB8V/edit?usp=sharing&ouid=11265...

[#8]

Sent you a request.

[#9 · Accepted solution]

@MLipke 

The second link worked.

So I'm attaching the beginning of a workbook that illustrates what can be done with a single database into which you enter each day's production data. I populated it with your data for the first seven days, and chose to do so for the month of May. Feel free to modify the raw data and see how immediately it carries through to the summary page (based on the date range you select)

Note; this is just a proof of concept, it can be greatly refined. I left off the cells reporting Sales data because those seemed somewhat incidental to the main focus, but I am pretty sure you'll see how readily that information could also be incorporated.

My main goal is to show how easily Excel can summarize very selectively daily, weekly, monthly data from a single database.

The main formula is below (and, by the way, it DOES require a relatively new version of Excel to operate; so if you aren't seeing totals in the "Summary" page, that would mean that you need to update)

=SUM(FILTER(INDIRECT("Table1["&A5&"]"),(Table1[Date]>=Start_Date)*((Table1[Date]<=End_Date))))

Let me know if you'd like to have that formula explained. If you enjoy researching formulas or functions you aren't yet familiar with, here are links that explain INDIRECT and FILTER, the two functions that might be new.

[#10]

I like the solution. I am not very proficient with tables and using database functionality in excel. However, I think this can work so I'll see if I can modify it to do what we need.

Thank you for your help. I really appreciate it.

[#11]

@MLipke 

If you have any questions, don't hesitate to come back and ask them. I'm glad to hear, though, that you think this will work. I think once you're comfortable with it, you'll find there's a LOT more power and flexibility in an approach based on a single database table.