VLOOKUP in pivot table calculated field

lironprofit

New Member
Joined
Jul 28, 2021
Messages
2
Office Version
  1. 2010
Platform
  1. Windows
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. :(
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

jorismoerings

Well-known Member
Joined
Jul 4, 2014
Messages
1,481
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
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

New Member
Joined
Jul 28, 2021
Messages
2
Office Version
  1. 2010
Platform
  1. Windows
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! :)
 

Forum statistics

Threads
1,143,907
Messages
5,721,443
Members
422,362
Latest member
elliotpat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top