VLOOKUP in pivot table calculated field

[lironprofit]

Hi all! Sorry in advance if some of my English isn't perfect as I'm not a native speaker.
I have a pivot table with the sum of sales for each customer ID by month, and I have a different pivot table in the same sheet with the customer ID and current balance (his AR).
What I'm trying to achieve is a calculated field in the first pivot table that for each customer ID shows also the balance. The problem is, the formula for the cal. field I'm trying to use is:
= IFERROR(VLOOKUP('CustomerID',OFFSET(,0,0,COUNTA($EH:$EH),2),8,0),0)
Whereas the OFFSET retrieves the second pivot table, and the VLOOKUP is supposed to search for the CustomerID in that table and bring it. Problem is pivot table doesn't support this.
Also, the data in the pivot table is changing regularly as it is referencing a table in a different sheet that is retrieved from BW Reporting.
What I tried to do is just create a column that has the VLOOKUP function adjacent to the first pivot table and I dragged it all the way down, but if the data is largening daily then I have to drag the column again and again. What I ended up doing is drag it all the way down in advance to 200,000 cells, but then the excel file is taking 5 minutes for each new change (like even putting borders around a cell). Is there a way to just make a cell drag itself down N amount of times (N will be calculated using COUNTA($EH:$EH)? Is there a way to bypass that pivot restriction to referencing entire tables?
Thanks, Liron.

P.S. Couldn't upload an SS because it is company's privacy and then I'd have to blur almost everything so it's a moo point.
P.S. P.S. I've sat on this problem for 5 hours.

 

[jorismoerings]

Hi,

The chosen solution involves a lot of repetitive array checking functions which do not improve the calculation speed of excel. With the information given, it probably would be a better solution to combine sum of sales and the AR position already in the datasource (eg use power pivot to create a combined datasources from the BW reporting data feeds) before creating the pivot table.

Hope this helps

 

[lironprofit]

Hi,

The chosen solution involves a lot of repetitive array checking functions which do not improve the calculation speed of excel. With the information given, it probably would be a better solution to combine sum of sales and the AR position already in the datasource (eg use power pivot to create a combined datasources from the BW reporting data feeds) before creating the pivot table.

Hope this helps

Hey! Thanks for the reply.
Unfortunately our company doesn't use Excel 2016+ (where Power Pivot is available), therefore I found the following solution for those who are interested:
I used a Macro (to which I assigned a "Refersh Calculations" button):
Dim numberOfRows As Integer
numberOfRows = Range("BN7").Value || BN7 counts the number of rows currently in the table with COUNTA on an entire column

Range("D7:E7").Select
Selection.AutoFill Destination:=Range("D7:E" & numberOfRows) || drags calculations

Range("AD7:BD7").Select
Selection.AutoFill Destination:=Range("AD7:BD" & numberOfRows) || drags calculations

This solved my problem. Thank you for your reply tho, much appreciated!