Forum Discussion
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 have solutions with sumif.
I know that I could get via vlookup function, but if I use this: there will be more than 11.000 vlookup functions in formulas. My cells are for 95 P&L accounts, each from january to december (12), from 11 sheets (95*12*11=12.540). Is there any easier way? Another thing is, if I do that, the excel will get very slow I suppose.
Even if there is no another more useful formula, is there any other system to do that? How do you usually get values for plan (in case you don't use software for it).
This is just example of vlookups I need to use to get values from two sheets.
Thank you, I tried to do it via Power Query, from eleven different excel files, to get one table. Generally it work fine. Mine issue was, when I changed one number in one of excel files, I refreshed power query and it was updated. But than out of this query table, I made pivot table myself. When I refreshed pivot table, it didn't update. Is it normal or I have to do it another way? In file you can see in green tab E2 it updated to 10.000, in sheet 2 where should be the pivot table, it did not update.
If it works after you Refresh All, wait a bit, Refresh All again and PivotTable shows correct data - disable "Enable background refresh" for Power Query connection. More details is here Enable Background Refresh in Power Query - Solve and Excel Consulting
It works with these selections in the Pivot Table. I've applied a filter for only a few accounts (KONTO in german Excel) in column A. However this a very unusual way of using Pivot Table. In my opinion the data from Power Query must be unpivoted.
I think you have to unpivot the data in Power Query in order to return a result like in my sample file. In the screenshot below is the result from Power Query in the green table. This result is ideal for a Pivot Table.
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.
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.
Can you please share a sample file showing your inputs and desired output?
I checked the file, it could only bring the data from sheet 1 to the trial balance sheet. The formula couldn't add data from other sheet. You may want to check the formula to see if there is anything missing
An alternative could be Power Query and a Pivot Table. In the attached file you can add data to the blue dynamic tables in the first 7 sheets. In sheet 8 is a green result table that combines all the data from the first 7 sheets. You can right-click in any cell of the green table to update the data collection from the first 7 sheets. Then you can click in any cell of the Pivot Table in sheet 8 and right-click to update the results of the Pivot Table.
