Help! Is this possible??

Copper Contributor

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! 

11 Replies

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

I would agree but it's a timesheet so I need everyone separate to keep timesheets confidential. @Ingeborg Hawighorst 

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.

@kileecovert 

There is no confidentiality when different people operate in the same workbook.

 

@kileecovert  They can't see each others' sheets? That must be one heck of a workbook.

 

You can let everybody use their own workbook for their time sheet entry. Let the files be stored in one folder. Then you can use Power Query to collect the data from all the files in that folder and do your report and aggregations.

@bramsey , IMHO, Forms is less suitable tool for the timesheets

@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

@Sergei BaklanIt 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.

@bramsey 

With both if all data collected in one file you have no possibility to see/correct what was already entered previous time. If data collected in separate files better to work directly with them.

@Sergei Baklancorrect on using Forms.  Not correct on the PowerApps solution.  You could easily only present the users data to them and allow them to edit unposted time entries.