Minor Workbooks Updating The Main Workbook

danjojackson

New Member
Joined
Sep 30, 2020
Messages
2
Office Version
  1. 365
  2. 2016
Platform
  1. 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
 

Attachments

  • NEW WIP PROJECT.JPG
    NEW WIP PROJECT.JPG
    183.2 KB · Views: 2

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Misca

Well-known Member
Joined
Aug 12, 2009
Messages
1,623
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

New Member
Joined
Sep 30, 2020
Messages
2
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,744
Messages
5,543,955
Members
410,586
Latest member
acadavid86
Top