Pivot talbes issue and vlookup

[james rockford]

Hi to all,

I have set up a pivot table... the data will change daily and as we know we can just refresh the data ...but i am pulling data from the pivot table to a new work book and i am looking for a way that when the table changes the info on the vlookup changes alone with the data...I.E If the pivot table changes from three rows to four then i will need the vlookup to change to return the info on the four row as well if the table get large or smaller the vlookup auto correct ....hope this is clear and thanks for the help

 

[Fazza]

Hi, Jim

Instead of a pivot table, what about a query table? Its fixed set up of columns overcomes the flexibility of the pivot table upsetting your lookup type formulas. If given a defined name, it will adjust automatically on refreshing so 'downstream' formulas will be good. Create via menu path ALT-D-D-N: is similar to pivot table in that needs refreshing (which can be automated, btw, via the query tables' properties).

HTH

 

[Norie]

Instead of VLOOKUP you could try GETPIVOTDATA, assuming that's what you are trying to do - get pivot data.

 

[james rockford]

Hi Norie...That work get thanks for the help
Fazza ....I will work on your info and see what workes the best again thanks for all the help

James

 

[Fazza]

Another thought, James - might be better than a query table. Have a second pivot table set up exactly as required to suit the way you pull the data from it. The worksheet containing this second pivot table can be hidden so that users don't change the set up.

 

[james rockford]

Hi fazza....that will work as well will set this up...thanks