Minor Workbooks Updating The Main Workbook

[danjojackson]

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

 

[Misca]

Welcome to the board!

Sounds like a perfect job for the Power Query (or Get & Transform, as they also call it). I hope there's a some kind of a time stamp column in your data to sort the transactions correctly. If the row order is the only way to sort your data you might want to create a custom function that adds the index column to your data for sorting.

 

[danjojackson]

Thanks Misca! I'll be adding a date column but need to research an auto input method which excel recognises (The =NOW formula is only recognised as text).

I'll give this a try sometime and report back cheers.