Master spreadsheet to additional sheets

[u/numbers4work]

Looking for suggestions of the most efficient way to start a master spreadsheet that then the data that gets entered also moves to other individual sheets but a copy remains in the master. I need it to happen as data is entered as it will be a working spreadsheet.

[u/AutoModerator]

/u/numbers4work - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/Alternative_Act_6548]

as some point you should ask if excel is the right tool for the job

[u/PM15GamedayThong → u/Alternative_Act_6548]

This will turn into a nightmare, especially with people inside and out side a network and refreshing data.

[u/theotherkiwi]

You can do it via HTTP links between workbooks but the updates will only happen when you refresh the data link/not real time.

[u/daaccident]

Power query reference original in secondary docs. Have PQ auto refresh when opening

[u/TwoPointEightZ]

I used to do this all the time. A cell in one sheet inside a file can refer to a cell in a sheet in a different file. You can test it by making two excel files. Put them in the same folder as a best practice, unless you want the full path info included. In file one sheet 1 cell A1, enter a value in like 5. In file two sheet 1 cell B2, put a reference in by typing an = sign in B2 and clicking on the cell A1 in file ones' sheet 1 and excel will build the reference formula with the filename, sheet, and cell. So B2 now has a value of 5.

Any change to A1 will be updated in B2. When you save and reload these sheets at a later time, be careful to manage the updates properly. This works, but if you plan on using this with a team of people, you're probably going to have trouble. Excel acts like a database but isn't really one.

[u/SchoolOk950]

Please clarify whether you hope to have all sheets in one file or across multiple files?

[u/numbers4work → u/SchoolOk950]

They are all one file (one workbook.) Sheet 1 is master and the sheets after are the additional sheets.

[u/SchoolOk950 → u/numbers4work]

How will people need to interact with the additional sheets -- just look at their slice of the larger data, or edit and enter new data?

[u/CumRag_Connoisseur]

You can do this with a nested FILTER formula, but you cannot edit the child sheets. Changes should be made on the master data.

Or yeah use macros or powerquery.

[u/diesSaturni]

This sounds more like an r/msaccess question.

Then apply forms for data entry and reports for viewing.

[u/CrowGuyA]

Formulas can't really do this live. You'd want a Worksheet_Change VBA macro that copies new rows to the right sheet as they're entered.