Updating legends dynamically

Hello guys I am struck in a problem.
I am using pivot charts and table for my data representation. Data source for the pivot chart and pivot table is a hidden sheet. I have defined a range manually in that hidden sheet, this hidden data sheet contains column names only. When data comes in it, my pivot chart gets refreshed and the data starts reflecting in the chart. My problem is the legends in the chart are showing on the basis of column names but i want these legends to be dynamic.
Can anyone help me in doing this, i have tried everything i know before writing this thread.
Any help is appreciated.

Originally Posted by protocol

Hello guys I am struck in a problem.
I am using pivot charts and table for my data representation. Data source for the pivot chart and pivot table is a hidden sheet. I have defined a range manually in that hidden sheet, this hidden data sheet contains column names only. When data comes in it, my pivot chart gets refreshed and the data starts reflecting in the chart. My problem is the legends in the chart are showing on the basis of column names but i want these legends to be dynamic.
Can anyone help me in doing this, i have tried everything i know before writing this thread.
Any help is appreciated.

Is there no one who have any experience with column updating in pivot tables.
Is there no way to update column names in pivot table via vba ?