Using Vlookup in PivotTable Calculated field

M

mines

New Member
Joined
Oct 29, 2008
Messages
5
Hi there,

How can i use Vlookup in the Calculated field in Pivot table.

i am trying to get an information from a different sheet to do some calculation , but i get an error saying "references,Names,Arrays are not supported in pivottable formulas"


the formula i am using is =VLOOKUP(CONCATENATE( DocYear , DocMonth ),[DailySalesReport.xlsx]Cost!$A:$L,3,0) .

is there a work around for this.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
Hi. If I understand correctly, the solution is to join the tables in SQL. For example, if you name the source data tables (headers & data rows) with normal defined names and from a new workbook start the pivot table wizard taking the external data option at the first page. Follow the wizard to the end & take the option to edit in MS Query. Hit the 'SQL' button and edit the SQL, OK to enter it and then hit the 'open door' button and then complete the pivot table. The SQL must be specific to your field names, which I don't know, but maybe below gives you the idea? When finished the resultant worksheet containing the pivot table can be moved into the source data workbook if you like. HTH, Fazza

Code: 
SELECT A.*, B.Sales
FROM main_table A, lookup_table B
WHERE A.DocYear & A.DocMonth = B.DateField
 
Upvote 0
Thanks fazza,

the issue is , i am not dealing with tables/sql.

i have two sheets , the first one have sales figure, the second one is the operating cost, depending on the month should be picked. i can do a workaround by applying the vlookup in the sheet one, and doing arithmetic operations there. but my problem is ,
Is there anyway i can use a vlookup or a likewise function in the pivot table calculation formula to pick a value based on some condition?

Thanks
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Solved
PivotTable Fields and VBA
Replies
3
Views
184
Domenic
D
B
  • Question Question
Sum child cells and allow reference to higher cell within Table
Replies
7
Views
105
babyjwhale
B
W
  • Question Question
Calculated Field
Replies
4
Views
755
Alex Blakenburg
A
J
  • Solved
VBA/Macro: Adding Pivot table with connection string
Replies
1
Views
178
Jette3173
J
D
  • Solved
Matching TODAY() using VLOOKUP
Replies
7
Views
164
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,920
Messages
6,175,378
Members
452,638
Latest member
Oluwabukunmi

Share this page

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
Back
Top