Forum Discussion

kileecovert's avatar
kileecovert
Copper Contributor
Jul 16, 2019

Help! Is this possible??

Hello- I would like to set up multiple sheets within an excel workbook where people can go to their tab and input data and then have that data transfer to a "totals" sheet also within the workbook. I know how to do that but what I was wondering is if there was a shortcut to copying the information from one sheet to the totals sheet? The information I'm gathering is in the exact same spot on all of the sheets within the workbook (besides the "totals" sheet) but I would like to avoid clicking back and forth for each of my 18 sheets to do formulas on the totals sheet for each one. 

 

Also- is there a way to make a copy of a master's sheet that is connected to the totals sheet that automatically adds a new connected line in the totals sheet from the newly created sheet without me having to go in a manually set up another line with formulas?

Thanks! 

  • kileecovert 

     

    Hello,

     

    the data architecture you describe is not desirable. It would be better to have just one sheet for data entry. If you need to show data for a certain person, you could build a report. One report can be set up so that different people can be selected. That would dramatically reduce the number of sheets in your workbook and would simplify things a lot.

  • bramsey's avatar
    bramsey
    Copper Contributor

    If you are in the Office 365 environment you could always use Forms.  It saves the data in Excel and then a Flow could move any new entries to your master list.  You could also write a PowerApp to write to your master list.

      • bramsey's avatar
        bramsey
        Copper Contributor

        SergeiBaklanIt is a quick and easy way to preserve privacy of other entries and it easily stores its data in Excel.  If you had a lot of corrections yes not ideal.  I wouldn't really consider Excel as a long term solution anyway which is why I closed my post with the idea of writing a PowerApp for it.  I usually find it helpful to have a quick short term solution to a problem that will at least carry you through to the longer term solution.

  • kileecovert 

    Hi,

    There are different ways to do that, whether by nested Functions, Macros & VBA or Power Query.

    Just to give you an example of the concept (although it is a little different than your exact request) In the attached File, I have 87 Sheets (Each for a different day). I would like to consolidate the records from all the sheets in One single Summary report and display a message box showing the total number of records. There is a code created in VBA which will do all the magic when triggered with the Shortcut CTRL + SHIFT +P
    In your case, minor modification to the code (like you do not want to delete the original sheet after copying it's data) can be done.

    Again:
    If your question is : Is it feasible

    The answer is : YES

     

    Thanks

    Nabil Mourad

  • kileecovert 

    Hi,

    There are different ways to do that, whether by nested Functions, Macros & VBA or Power Query.

    Just to give you an example of the concept (although it is a little different than your exact request) In the attached File, I have 87 Sheets (Each for a different day). I would like to consolidate the records from all the sheets in One single Summary report and display a message box showing the total number of records. There is a code created in VBA which will do all the magic when triggered with the Shortcut CTRL + SHIFT +P
    In your case, minor modification to the code (like you do not want to delete the original sheet after copying it's data) can be done.

    Again:
    If your question is : Is it feasible

    The answer is : YES

     

    Thanks

    Nabil Mourad

Resources