Formula or macro?

[gd6noob]

Hello,

I have a workbook with 5 sheets.
Sheet 1 is the main page where all the data will be compiled and should not be touched.

Sheet 2-5 will be worked on by 4 different people.
Whats the best way to have all the data being showed on the main page without having to copy/paste every time all the information or from accidentally being deleted?

 

[gd6noob]

Hi Perpa,

Thank you for the quick reply.
So on sheet 1, having it hidden I think would be difficult for my manager, but being able to copy data/values from sheet one but not being able to make any changes. Is there any way to prevent this?


For the Button, can we have only 1 and place it on Sheet 1?

 

[Perpa]

gd6noob,
It is possible to put a button on sheet1 that will bring whatever is current on sheets 2-5 to sheet1. If Sheet 1 is not hidden, then others besides the manager can make changes. The sheet could be protected, but then the manager would have to enter a password to unprotect the sheet to make any changes. I don't see that as any advantage compared to hiding the sheet because the cells cannot be copied without unprotecting the sheet to select the cells.

Then, if I understand correctly, once that data is on Sheet1, you want your manager to be able to make changes that can be reflected back to whichever sheet is affected, 2 to 5.
What I am proposing will clear all the data from each sheet 2 to 5, and replace it with what is on sheet1 for that sheet. It is not just replacing the cells that changed, it would be replacing ALL the cells with data.

How we accomplish that depends on how we display the info from each sheet 2 to 5, on sheet1... And will probably require a second button (all sheets copied back to their respective sheets at once), OR a button for each sheet 2 to 5, to send the revisions back to the respective sheet.

We could use just columns A to I as you have shown, but we would need the 'Employee ID' or the 'Sheet Number' to be placed in cell A2 of sheets 2 to 5 to indicate the top LH corner of each sheet when it is transferred to sheet1. No other information can be in column A for that scheme to work.

Another scheme...Instead of showing sheets 2 to 5 vertically in columns A to I on sheet1, we could show the sheets horizontally, ie. sheet2 would be shown in columns A to I, Sheet3 in columns K to S, Sheet4 in columns U to AC, and sheet5 in columns AE to AM. This would allow us to locate the last row with data for each sheet to be copied back to the respective sheets.

Let me know if you understand what I have described, and which, if any, of the proposed methods you want to pursue.
Perpa

 

[gd6noob]

gd6noob,
It is possible to put a button on sheet1 that will bring whatever is current on sheets 2-5 to sheet1. If Sheet 1 is not hidden, then others besides the manager can make changes. The sheet could be protected, but then the manager would have to enter a password to unprotect the sheet to make any changes. I don't see that as any advantage compared to hiding the sheet because the cells cannot be copied without unprotecting the sheet to select the cells.

Then, if I understand correctly, once that data is on Sheet1, you want your manager to be able to make changes that can be reflected back to whichever sheet is affected, 2 to 5.
What I am proposing will clear all the data from each sheet 2 to 5, and replace it with what is on sheet1 for that sheet. It is not just replacing the cells that changed, it would be replacing ALL the cells with data.
For Sheet 1, there shouldn't be any editing done, just copying and using the filters.

How we accomplish that depends on how we display the info from each sheet 2 to 5, on sheet1... And will probably require a second button (all sheets copied back to their respective sheets at once), OR a button for each sheet 2 to 5, to send the revisions back to the respective sheet.
For this, from what Im understanding, button on Sheets 2-5 that will send the data to Sheet 1. Will this overwrite any previous data? If the name on Sheet 2 is not longer present, will it remove it on Sheet 1?

We could use just columns A to I as you have shown, but we would need the 'Employee ID' or the 'Sheet Number' to be placed in cell A2 of sheets 2 to 5 to indicate the top LH corner of each sheet when it is transferred to sheet1. No other information can be in column A for that scheme to work.
This part, Im not sure I understand

Another scheme...Instead of showing sheets 2 to 5 vertically in columns A to I on sheet1, we could show the sheets horizontally, ie. sheet2 would be shown in columns A to I, Sheet3 in columns K to S, Sheet4 in columns U to AC, and sheet5 in columns AE to AM. This would allow us to locate the last row with data for each sheet to be copied back to the respective sheets.
Horizontal for be a bit confusing to work on?

Let me know if you understand what I have described, and which, if any, of the proposed methods you want to pursue.
Perpa

Please see my questions in the quote

 

[Perpa]

gd6noob,
I guess I misunderstood earlier...let me try again.
So Sheet1 will NOT be edited, per your last post: 'For Sheet 1, there shouldn't be any editing done, just copying and using the filters.'

Copying shouldn't be any problem. But blank rows could be problematic for filtering. I'm no expert on that and will defer that to someone else.

Earlier you wanted a button on sheet1 to bring the current info from sheets 2 to 5 to sheet1. Do you also want a button on sheets 2 to 5 to update sheet1?

If editing is done on one or more of sheets 2 to 5, then I recommend that ALL 4 sheets (2 to 5) be re-copied to sheet1. This will be much less confusing, especially if rows are added or deleted.

If you must keep all 4 sheets in columns A to I on sheet1, how do you propose to know where one sheet ends and another begins?
I had thought that placing the 'Employee ID' or 'Sheet #' at the top row Left corner (column A) of each sheets' data would clarify that. What do you have in mind?

If we don't password protect sheet1, we cannot prevent anyone from changing the data thereon. You don't want to hide sheet1, and protecting sheet1 will prevent copying unless it is unprotected, the range copied, and then the sheet is protected again. I'm not sure how to proceed?

Let's see how you respond to these questions.
Perpa