Forum Discussion
MLipke
May 31, 2023Copper Contributor
Copying formulas to multiple sheets in a workbook
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 she...
- Jun 01, 2023
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.
MLipke
Jun 01, 2023Copper Contributor
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=112657124400319678851&rtpof=true&sd=true
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=112657124400319678851&rtpof=true&sd=true
mathetes
Jun 01, 2023Silver Contributor
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.
- mathetesJun 01, 2023Silver Contributor
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.
- MLipkeJun 01, 2023Copper ContributorI 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.