Forum Discussion

MLipke's avatar
MLipke
Copper Contributor
May 31, 2023

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 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?

  • 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.

  • mathetes's avatar
    mathetes
    Silver Contributor

    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.

    • MLipke's avatar
      MLipke
      Copper Contributor

      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".

       

       

      • mathetes's avatar
        mathetes
        Silver Contributor

        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.

Resources