danjojackson
New Member
- Joined
- Sep 30, 2020
- Messages
- 2
- Office Version
- 365
- 2016
- Platform
- Windows
Hi All,
I'm starting a project to re-do an age old excel system. I have a good understanding of what I want to do but there's one small detail I can't work out.
===EXISTING SYSTEM===
We have a master spreadsheet which contains records of all business conducted
We also have 8 advisors who have their own minor spreadsheet.
When a transaction is done, the advisors update both their minor one and the master. Ultimately, this has left the minor ones all being different and the main one an absolute mess.
===NEW SYSTEM PLAN===
My plan is to have a basic minor spreadsheet which requires minimal input on the advisor part (Because they're idiots) and have the master one update automatically so they don't have access to it.
They're all going to be in the same folder so linking the spreadsheets wont be a problem. The MAIN workbook will have tabs as follows.
MAIN
ADVISOR 1
ADVISOR 2
ADVISOR 3
etc etc
The ADVISOR tabs will be a direct mirror of the individual minor workbooks. The minor workbooks will be just 1 tab
===THE PROBLEM===
Main needs to list all the transactions in the order they are entered. For example, Advisor A completes a transaction, then ADV B, then ADV A, then ADV C etc etc...
I only know how to link cells so a block would have to be advisor A and a block Advisor b and so on. I need it to be clever and list in order of data entered. Please see image below
The Minor Workbooks are listing in chronological order for each advisor whereas the MAIN workbook needs to list in chronological order for all advisors.
Once again I really appreciate any help received. Theres probably multiple approaches and i'd be interested in researching them for future projects so any links for further reading would be gratefully received.
Wishing you all the best
Danjo
I'm starting a project to re-do an age old excel system. I have a good understanding of what I want to do but there's one small detail I can't work out.
===EXISTING SYSTEM===
We have a master spreadsheet which contains records of all business conducted
We also have 8 advisors who have their own minor spreadsheet.
When a transaction is done, the advisors update both their minor one and the master. Ultimately, this has left the minor ones all being different and the main one an absolute mess.
===NEW SYSTEM PLAN===
My plan is to have a basic minor spreadsheet which requires minimal input on the advisor part (Because they're idiots) and have the master one update automatically so they don't have access to it.
They're all going to be in the same folder so linking the spreadsheets wont be a problem. The MAIN workbook will have tabs as follows.
MAIN
ADVISOR 1
ADVISOR 2
ADVISOR 3
etc etc
The ADVISOR tabs will be a direct mirror of the individual minor workbooks. The minor workbooks will be just 1 tab
===THE PROBLEM===
Main needs to list all the transactions in the order they are entered. For example, Advisor A completes a transaction, then ADV B, then ADV A, then ADV C etc etc...
I only know how to link cells so a block would have to be advisor A and a block Advisor b and so on. I need it to be clever and list in order of data entered. Please see image below
The Minor Workbooks are listing in chronological order for each advisor whereas the MAIN workbook needs to list in chronological order for all advisors.
Once again I really appreciate any help received. Theres probably multiple approaches and i'd be interested in researching them for future projects so any links for further reading would be gratefully received.
Wishing you all the best
Danjo