Forum Discussion

Mia12's avatar
Mia12
Copper Contributor
Feb 19, 2025

Which formula to use to get data from 11 sheets to trial balance

Hi, My question is how to effectively get the numbers from planning sheets (there is 11 of them, each one represents one colleague to plan) into trial balance? From trial balance to P&L I already ha...
excel
Formulas and Functions
Harun24HR's avatar
Harun24HR
Bronze Contributor
Feb 19, 2025

VSTACK() can stack data from 2D range from multiple sheets. VSTACK(Sheet1:Sheet11!A2:E5000) will stack data from sheet1 to Sheet11 range A2 to E5000. The you can apply other functions to perform operations like VLOOKUP() or SUM(). Here is an example to sum all values from these 11 sheet of ID situated in A3 cell.

 

=LET(x,VSTACK(Sheet1:Sheet11!A2:E5000),SUM(VLOOKUP(A3,x,5,0)))

See the attached file for details.

Mia12's avatar
Mia12
Copper Contributor
Feb 20, 2025

Harun24HRthank you for exel, I checked the formula but I don't know how to correct it. I can see it takes all eleven sheets into consideration, but the value it gave is 50 - insted of : 550, how to get that?

In each sheet someone planned 50 in certain month, on certain account, I need the sum of all 11 sheets, on this account in this month. I hope you understand what I mean. I can see it is written SUM (VLOOKUP(A3;x;5;0) but it gave only 50 insted of 550. 

  • Harun24HR's avatar
    Harun24HR
    Bronze Contributor
    Feb 20, 2025

    Can you please share a sample file showing your inputs and desired output? 

Resources