Forum Discussion

HunterSS's avatar
HunterSS
Copper Contributor
Dec 11, 2020

Consolidating data from multiple worksheets automatically

Hi, In 1 workbook i presently have 4 worksheets: Sheet 1 - Overall Register Sheet 2 - Document Register Sheet 3 - Form Register Sheet 4 - Presentation Register   Each sheet has the same 10 col...
excel
mathetes's avatar
mathetes
Silver Contributor
Dec 11, 2020

HunterSS 

 

Sounds like a classic case of we humans getting in the way of letting Excel do what it does very well, which is to take a single database and produce extracts, summary analyses, etc. We like to create separate lists and then ask Excel to put them together, which is possible, but (as you're experiencing) less than easy.

 

Why not use your overall register as the main data base, the Input side of things, and then let Excel do it's thing of extracting only the Documents or Forms or Presentations, on demand (the Output end of things). Excel actually excels at that quite easily. Especially now, with newly available Dynamic Array functions like FILTER and SORT.

 

Here's a YouTube video that was used to introduce these Dynamic Array functions. It might be just what you're seeking. https://www.youtube.com/watch?v=9I9DtFOVPIg

 

 

 

Resources