Search code examples
excelexcel-formula

Consolidating all sheets data into another work book

I have a Master file which contains 5 sheets names like SC1,SC2,SC3,SC4,SC5. I have to collate all these sheets data into my working file book based on the week commencing. I am using the below formula by taking helper columns which gives my result. Is there any other approach?

=VSTACK(
FILTER(INDIRECT($D$3&D5),INDIRECT($D$3&E5)=$F$4),
FILTER(INDIRECT($D$3&D6),INDIRECT($D$3&E6)=$F$4),
FILTER(INDIRECT($D$3&D7),INDIRECT($D$3&E7)=$F$4),
FILTER(INDIRECT($D$3&D8),INDIRECT($D$3&E8)=$F$4),
FILTER(INDIRECT($D$3&D9),INDIRECT($D$3&E9)=$F$4))

[![Master file][1]][1]

https://i.sstatic.net/65aPGY6B.png

[![Working File][2]][2]

https://i.sstatic.net/trHpLJGy.png

Solution

  • You can use 3D reference to stack data from SC1 to SC5:

    =VSTACK('SC1:SC3'!$A$2:$C$100)

    enter image description here

    To filter by week commencing date, you can use FILTER and LET:

    =LET(stacked,VSTACK('SC1:SC3'!$A$2:$C$12),dates,TAKE(stacked,,1),FILTER(stacked,dates="wc 16th Jun 2024",""))

    enter image description here