December 6, 2018
Hi, I’m trying to set up a dynamic reference for a table with data re male and female. My goal is to build a scatter chart with 1 serie with data re male and the other re female. Currently, each time I refresh the data, I sort the table by male/female then edit manually the graph series. Is there a way I could avoid that manual sort and let excel plot the “male” and female” series ?
Mynda suggested using a pivot, but while I read the tip on how make a scatter chart from a Pivot, when I select the data using the columns form the pivot, these are not dynamic, so when I filter in my slicer I lose the second serie because Excel has not readusted the area for each serie according to the filter.
I have attached a sample, so you can better understand my issue : filter on level 8, and although there are male and female on that level, all data show as "female".
Thanks so much for taking a look at this, it's driving me nuts, there must be a way !
July 16, 2010
Thanks for sharing your example file. It makes it much quicker and easier to provide you with a clear answer.
You also need to separate the Male and Female data into separate PivotTables so that the dynamic named ranges know where the data for each gender ends. See example file attached.