Minor Workbooks Updating The Main Workbook

%3CLINGO-SUB%20id%3D%22lingo-sub-1728088%22%20slang%3D%22en-US%22%3EMinor%20Workbooks%20Updating%20The%20Main%20Workbook%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1728088%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EHi%20All%2C%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EI'm%20starting%20a%20project%20to%20re-do%20an%20age%20old%20excel%20system.%20I%20have%20a%20good%20understanding%20of%20what%20I%20want%20to%20do%20but%20there's%20one%20small%20detail%20I%20can't%20work%20out.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3E%3D%3D%3DEXISTING%20SYSTEM%3D%3D%3D%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EWe%20have%20a%20master%20spreadsheet%20which%20contains%20records%20of%20all%20business%20conducted%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EWe%20also%20have%208%20advisors%20who%20have%20their%20own%20minor%20spreadsheet.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EWhen%20a%20transaction%20is%20done%2C%20the%20advisors%20update%20both%20their%20minor%20one%20and%20the%20master.%20Ultimately%2C%20this%20has%20left%20the%20minor%20ones%20all%20being%20different%20and%20the%20main%20one%20an%20absolute%20mess.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3E%3D%3D%3DNEW%20SYSTEM%20PLAN%3D%3D%3D%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EMy%20plan%20is%20to%20have%20a%20basic%20minor%20spreadsheet%20which%20requires%20minimal%20input%20on%20the%20advisor%20part%20and%20have%20the%20master%20one%20update%20automatically%20so%20they%20don't%20have%20access%20to%20it.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EThey're%20all%20going%20to%20be%20in%20the%20same%20folder%20so%20linking%20the%20spreadsheets%20wont%20be%20a%20problem.%20The%20MAIN%20workbook%20will%20have%20tabs%20as%20follows.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EMAIN%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EADVISOR%201%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EADVISOR%202%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EADVISOR%203%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3Eetc%20etc%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EThe%20ADVISOR%20tabs%20will%20be%20a%20direct%20mirror%20of%20the%20individual%20minor%20workbooks.%20The%20minor%20workbooks%20will%20be%20just%201%20tab%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3E%3D%3D%3DTHE%20PROBLEM%3D%3D%3D%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EMain%20needs%20to%20list%20all%20the%20transactions%20in%20the%20order%20they%20are%20entered.%20For%20example%2C%20Advisor%20A%20completes%20a%20transaction%2C%20then%20ADV%20B%2C%20then%20ADV%20A%2C%20then%20ADV%20C%20etc%20etc...%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EI%20only%20know%20how%20to%20link%20cells%20so%20a%20block%20would%20have%20to%20be%20advisor%20A%20and%20a%20block%20Advisor%20b%20and%20so%20on.%20I%20need%20it%20to%20be%20clever%20and%20list%20in%20order%20of%20data%20entered.%20Please%20see%20image%20below%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3E%3CSPAN%3EThe%20Minor%20Workbooks%20are%20listing%20in%20chronological%20order%20for%20each%20advisor%20whereas%20the%20MAIN%20workbook%20needs%20to%20list%20in%20chronological%20order%20for%20all%20advisors.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EOnce%20again%20I%20really%20appreciate%20any%20help%20received.%20Theres%20probably%20multiple%20approaches%20and%20i'd%20be%20interested%20in%20researching%20them%20for%20future%20projects%20so%20any%20links%20for%20further%20reading%20would%20be%20gratefully%20received.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EWishing%20you%20all%20the%20best%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EDanjo%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22NEW%20WIP%20PROJECT.JPG%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F223018i088E03A533245DCE%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22NEW%20WIP%20PROJECT.JPG%22%20alt%3D%22NEW%20WIP%20PROJECT.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1728088%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1732136%22%20slang%3D%22en-US%22%3ERe%3A%20Minor%20Workbooks%20Updating%20The%20Main%20Workbook%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1732136%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F814556%22%20target%3D%22_blank%22%3E%40danjojackson%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAdd%20a%20timestamp%20to%20each%20minor%20workbook%20entry%20and%20use%20the%20timestamp%20to%20order%20merged%20records.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3ENon-VBA%20method%3A%3C%2FSTRONG%3E%20Request%20each%20adviser%20use%20this%20keyboard%20shortcut.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%3CSPAN%3ETo%20insert%20the%20current%20date%20and%20time%2C%20press%20Ctrl%2B%3B%20(semi-colon)%2C%20then%20press%20Space%2C%20and%20then%20press%20Ctrl%2BShift%2B%3B%20(semi-colon).%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CSTRONG%3EVBA%20alternative%3C%2FSTRONG%3E%3A%20Add%20this%20code%20to%20each%20minor%20workbook%20Sheet1.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%3CBR%20%2F%3E%3CBR%20%2F%3EDim%20oLo%20As%20ListObject%3CBR%20%2F%3EDim%20oLR%20As%20ListRow%3CBR%20%2F%3EDim%20lRow%20As%20Long%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20Not%20IsEmpty(Target.Cells(1))%20Then%3CBR%20%2F%3E%20%20%20%20If%20Not%20Target.ListObject%20Is%20Nothing%20Then%3CBR%20%2F%3E%20%20%20%20%20%20%20%20Set%20oLo%20%3D%20Target.ListObject%3CBR%20%2F%3E%20%20%20%20%20%20%20%20lRow%20%3D%20Target.Cells(1).Row%20-%20oLo.HeaderRowRange.Row%3CBR%20%2F%3E%20%20%20%20%20%20%20%20Set%20oLR%20%3D%20oLo.ListRows(lRow)%3CBR%20%2F%3E%20%20%20%20%20%20%20%20If%20IsEmpty(oLR.Range(1))%20Then%20oLR.Range(1)%20%3D%20Now()%3CBR%20%2F%3E%20%20%20%20End%20If%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20Sub%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThe%20VBA%20alternative%20assumes%20each%20Minor%20worksheet%20has%20a%20table%20with%20a%20timestamp%20column%20like%20so%3A%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22table.png%22%20style%3D%22width%3A%20214px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F223145i39EFE301C2046883%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22table.png%22%20alt%3D%22table.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThe%20VBA%20advantage%20is%20the%20timestamp%20is%20entered%20automatically.%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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 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

NEW WIP PROJECT.JPG

2 Replies

@danjojackson ,

 

Add a timestamp to each minor workbook entry and use the timestamp to order merged records.

 

Non-VBA method: Request each adviser use this keyboard shortcut. 

 

To insert the current date and time, press Ctrl+; (semi-colon), then press Space, and then press Ctrl+Shift+; (semi-colon).

 

VBA alternative: Add this code to each minor workbook Sheet1.

 

Private Sub Worksheet_Change(ByVal Target As Range)

Dim oLo As ListObject
Dim oLR As ListRow
Dim lRow As Long

If Not IsEmpty(Target.Cells(1)) Then
If Not Target.ListObject Is Nothing Then
Set oLo = Target.ListObject
lRow = Target.Cells(1).Row - oLo.HeaderRowRange.Row
Set oLR = oLo.ListRows(lRow)
If IsEmpty(oLR.Range(1)) Then oLR.Range(1) = Now()
End If
End If

End Sub

 

The VBA alternative assumes each Minor worksheet has a table with a timestamp column like so:

 

table.png

 

The VBA advantage is the timestamp is entered automatically. 

Really Helpful Craig, Thanks

Was going to research this but you saved me the hassle! Muchos Gracias.

All The Best
Danjo