Forum Discussion
Copying formulas to multiple sheets in a workbook
- 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.
https://docs.google.com/spreadsheets/d/1Xtvw3edh00R0QynhJ2eI44b9CpbAIB8V/edit?usp=share_link&ouid=112657124400319678851&rtpof=true&sd=true
- MLipkeJun 01, 2023Copper ContributorI 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- mathetesJun 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.
- 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.
- MLipkeJun 01, 2023Copper ContributorSent you a request.