Dymanic chart pulling from another sheet selectable by dropdown field

Hello,

Can anyone sort this sheet for me so that the chart populates from the other sheet (Raw_data) using column A (Campaign ID) to bring all the chart elements together. So think of this as 3 seperate campaigns with a dymanic number of links per that need to formulate the chart.

Just a simple drop down on the dashboard to select the data. Also the formatting of that chart needs to remain the same. I've used an example on the dashboard of how I'd like it to look. Ultimately Id want the same data pulling off the (Raw_Data) sheet.

Thanks,

I've set this up for you in the attached file.

There is a data validation drop-down in cell B1 on the Dashboard sheet which allows you to choose the campaign (these are listed in A3:A5).

I put this formula in H2 of the Raw_data sheet:

=IF(A2=PCT_Dashboard!$B$1,MAX(H$1:H1)+1,"-")

and copied down to the bottom of your data. This responds to the choice made in the drop-down, and allocates a unique sequential number to matching records.

I put this formula in cell C2 of the Dashboard sheet:

=IFERROR(INDEX(Raw_data!B:B,MATCH(ROWS($1:1),Raw_data!$H:$H,0)),"")

which is then copied across into D2:F2, and down to row 27, to bring the appropriate data across.

Change the campaign in B1, and the data changes automatically, as does the chart.

Hope this helps.

Pete

Hi Pete,

Thanks so much for this. Are you able to make it so the chart size can change dependant on how many pieces of data it's pulling. The 3 examples I've put in will be added to with no set in stone amount of chart lines per campaign.

Thanks,

Originally Posted by Johnnyenv

... Are you able to make it so the chart size can change dependant on how many pieces of data it's pulling...

I tried to adjust the SERIES formula to make it dynamic based on the amount of non-blank data in column C, but Excel did not seem to like that approach.

Another way would be to set up Autofilter on column C, and then each time you change the campaign in B1 you can use the filter drop-down to unselect Blanks (at the bottom of the list). The physical size of the graph will remain the same, but with fewer elements to display this means that the thickness of each bar will be increased.

Hope this helps.

Pete

Why not use pivotable ?