Dynamic Scatter chart based on criteria

Danalope23

New Member
Joined
Oct 2, 2020
Messages
1
Office Version
  1. 365
  2. 2010
Hello,

I want to create a X/Y scatter chart to plot projects Final Amounts Invested amounts & % and use slicers to select one or more than one location.
I have numerous locations and each location has a different number of projects to be included.
This number of projects changes each month
Data source is a pivot table
I want each location to be a series with a different color in the scatter chart. I want the color to stay with the series when slicers are used to select a particular location.
I attempted to create a dynamic range based on a Location criteria and then use this range for each location in my scatter chart but I cannot figure out the offset & index formula
See the image. Imagining that the table is a Pivot, what is the formula to use in Series X values & series Y values so that if there are more or less data for each location, the range is dynamic?
I do not want to manually adjust my ranges for each location every month.

Do I need to create Names range with offset based on a criteria? I cannot figure out how to do so and then use this range in the scatter graph.

Thank you for your help.
Annie
 

Attachments

  • Dynamic range.PNG
    Dynamic range.PNG
    34 KB · Views: 58

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Forum statistics

Threads
1,176,168
Messages
5,901,732
Members
434,914
Latest member
mabek

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top