VLOOKUP in pivot table calculated field

lironprofit

New Member
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

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

jorismoerings

Well-known Member
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
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
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

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

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

Replies
0
Views
448
Replies
2
Views
376
Replies
0
Views
267
Replies
4
Views
535
Replies
8
Views
859

1,148,424
Messages
5,746,597
Members
424,032
Latest member
pochie2741

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.

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

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