change pivot data source automatic

**koi** · 09-19-2012, 08:45 AM

hi,

need to ask if we can make like macro to change the pivot data source as our input.

let say in A1 we type :"data source" and in B1 is blank cell..

whatever sheet name we type in B1 then press a Button..then pivot table will refer to that B1 name (we could use white color in that area to make it invisible)

thanks

**dilipandey** · 09-19-2012, 09:15 AM

Hi Koi,

See the attached workbook... where I have given defined names to two data tables. These names are available for you to select in cell B1 as in data validation drop down. Now I have used Indirect function to catch this name as a source to pivot table. Now.. when you change the name in cell B1, just refresh the pivot table.. and data source will be changed

pivot data source referenced through a cell.xlsx

Hope this helps you to start on.. thanks.

regards,
DILIPandey

<click on below 'star' if this helps>

**koi** · 09-19-2012, 09:44 AM

hi dilipandey,

thanks for the help..and the example is good enough since my pivot table will get same column and everything only the location is different.

if we put aa in sheet2, bb in sheet3, cc in sheet 4 and so on..can it still be done?

can you explain the logic here? i seen in name manager there are 3 formula there, 2 for identifying the location of source.. and 1 is using as indirect in B1, is that all the 3 formula needed?

i just need to create pivot table from aa or bb..then later when i change in B1, then press refresh, the pivot table is refresing?

**dilipandey** · 09-19-2012, 09:57 AM

Hi Koi,

Defined names can work here as they can be scoped to entire workbook... In name manager, yes, there are 3 formulas.. and you are correct in your understanding as on that stage.. but since you are going to use the aa,bb,cc in different sheets, you may need to enhance the Indirect formula to accommodate these changes. I would suggest you to learn about Indirect function and I 'm sure you can do it.

You need to create a pivot table with a source name which you would define using indirect function (which would have already referring to the aa,bb,cc as they were there in Cell B1 in our earlier example). Now when a master name (using indirect function) is defined and ready, you just need to change the aa,bb,cc etc and then just refresh pivot. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>