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: 44

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Forum statistics

Threads
1,148,272
Messages
5,745,790
Members
423,973
Latest member
man_this_is_hard

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