Dynamic chart range based on 2 values

Lilium

New Member
Joined
Jun 6, 2023
Messages
9
Office Version
  1. 2019
Platform
  1. Windows
Hi,

I have a table in a "Data" sheet with temperature readings from 4 locations (Columns B to E for locations T1, T2, T3 and T4) and different temperature readings (B2:E24) in those locations on different timeframes (A2:A24).
Now I have a sheet "Graphs" with a line chart of the temperature readings on those 4 locations, but I would like the graph to show only the readings based on the timeframe selected on sheet "Analysis" from cell B2 (start time) and cell D2 (End time).

How do I do this? I tried making a name for the temperature range and adding this formula for the name: INDEX(Data!A$2:E$24;MATCH(Analysis!$B$2;Data!$A$2:$A$24;1)):INDEX(Data!A$2:E$24;MATCH(Analysis!$D$2;Data!$A$2:$A$24;1))
and another name for the timeframe and adding this formula:
INDEX(Data!A$2:A$24;MATCH(Analysis!$B$2;Data!$A$2:$A$24;1)):INDEX(Data!A$2:A$24;MATCH(Analysis!$D$2;Data!$A$2:$A$24;1))
and then putting this in the data range of the graph but I don't get it to work.

What am I doing wrong?

Thanks
 

Attachments

  • Data.png
    Data.png
    31.6 KB · Views: 19
  • analysis.png
    analysis.png
    2.9 KB · Views: 14
  • graphs.png
    graphs.png
    29.4 KB · Views: 16

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
It would be helpful to know how it doesn't work but at first glance, your temperature formula appears to have a typo. You have
Excel Formula:
INDEX(Data![B]A$2[/B]:E$24;MATCH(Analysis!$B$2;Data!$A$2:$A$24;1)):INDEX(Data!A$2:E$24;MATCH(Analysis!$D$2;Data!$A$2:$A$24;1))

Seems like it should be
Excel Formula:
INDEX(Data![B]E$2[/B]:E$24;MATCH(Analysis!$B$2;Data!$A$2:$A$24;1)):INDEX(Data!A$2:E$24;MATCH(Analysis!$D$2;Data!$A$2:$A$24;1))

I suggest putting the formula into a cell (select several rows) and confirm as an array formula with Ctrl+Shift+Enter and it'll show you the output.
 
Upvote 0
@levipe if you are going to format something inside tags, you need to use the RICH tags.
 
Upvote 0
Thank you levipe. I've pasted the formula in the cels and i get output now: see attached screenshot.
But then how do I incorporate this in my data range selection of my graph? I just can't figure it out. (see screenshot)
 

Attachments

  • Graph Data Range.png
    Graph Data Range.png
    17.3 KB · Views: 15
  • INDEXformula-matrix.png
    INDEXformula-matrix.png
    4 KB · Views: 21
  • INDEXoutput.png
    INDEXoutput.png
    6.9 KB · Views: 16
Upvote 0
If the output you're getting from the formula in the worksheet is what you want, then go back to your original plan and put that formula into a name. The idea of putting the formula into a cell is just so we could see what the output was and why it wasn't working.
 
Upvote 0

Forum statistics

Threads
1,223,966
Messages
6,175,661
Members
452,666
Latest member
AllexDee

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
Back
Top