up vote 0 down vote favorite
share [g+] share [fb]

I have about 50 Pivot Tables in Excel 2007 that need a drop down updated on a daily basis several times to reflect multiple pieces of data from the page I have of data. It is very time consuming to update every table over and over to recover these charts. Is there a way to update them in one place, maybe reference a cell instead of updating every drop down?

link|improve this question
feedback

1 Answer

up vote 0 down vote

Unfortunately I don't think excel has a built in way to update all pivot tables.

I would achieve this by recording a macro of updating each pivot table, then linking this macro to a button. if that is unfamiliar territory for you, let me know and I can elaborate further.

link|improve this answer
I am not familiar with this in Pivot Tables, but I understand where you are going with this. Can I actually update drop downs in the macro? What if I want to update the same drop down multiple times to different values? What I mean is, I have about 50 tables that represent data in different ways, I want to select different airlines and represent all that data for each airline. Is that possible? Would I need a different macro for each airline for all 50 tables? Thanks for your help! – Wuggles Oct 14 '11 at 14:18
The path of least resistance here really depends on how many airlines and how much data. recording a macro will record your selections so you would need record a macro for each airline. To have single macro update all tables based on an input selection will require some hand made vba code. Depending on the nature of the data you are collecting it may be easier to hit the data behind the pivot table directly with formulas (like vlookup, sumif and sumproduct) – Pynner Oct 14 '11 at 16:02
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.