Dynamic scatter chart based on a reference to an address in a cell

[Maar7]

Hi all, 

I managed to get a cell which displays a dynamic address based on the data and various built-in checks of the data. Now I would like to plot this address in a scatter plot, but I have a lot of trouble of finding the right way. The cell D2 on the tab Data Water contains the following input: 

D2: $D$21:$D$31

D3: $E$21:$E$31

I want to plot this, and I tried the formula name manager function: 

   name = WaterTime

   refers to =INDIRECT('Data Water'!&$D$2) 

and

   name = WaterFlow

   refers to =INDIRECT('Data Water'!&$D$3) 

if I click on the names, I see the correct range highlighted, so I figured this would work. However, when I plot it according to this: 

series x values = 'Data Water'!WaterTime 

series x values = 'Data Water'!WaterFlow

I get an error that the reference is incorrect, even though I was people using this method in other fora and youtube. How do I get Excell to read 'Data Water'!WaterTime as 'Data Water'!$D$21:$D$31 in the chart domain? Or is there another way to get the address easily into the chart? I do not prefer the Series function, as it the data is scattered all over the worksheet and I didnt get it to work properly 

I hope to hear from you! 

[#2]

@Maar7 

Shouldn't it be =INDIRECT('Data Water'!$D$2) 

But I don't understand: 'Data Water'!D2 contains the text $D$2:$D$31. The range 'Data Water'!$D$2:$D$31 contains D2 itself, so it does not seem to be suitable as x-value. Same for D3.

[#3]

@Hans Vogelaar Sorry, indeed it is =INDIRECT('Data Water'!$D$2), I had that in my formulas but copied it wrong here. D2 is not the same as the range, the range is for this data set from 21 to 31. The range however changes often, depending on the data set, so that is why I would like it to make it dynamic. The cell D2 has the formula: 

   =ADRES(LINKS(D$1, VIND.ALLES(",",D$1)-1),@KOLOM(INDIRECT($B2)))&":"&ADRES(LINKS(E$1, VIND.ALLES(",",E$1)-1),@KOLOM(INDIRECT($B2))+KOLOMMEN(INDIRECT($B2))-1)

in which the B2 contains the name of the column of the table, and D1 and E1 contain the rows that indicate the correct range (begin and end, based on an IF function) 

I already saw that in the name manager, 'Data Water!' gets added before $D$21:$D$31 with this indirect function, and  'Data Water!' gets also added in the chart function, so it is there twice, which is most likely the cause of the problem. However, is there a method to get $D$21:$D$31 given in cell D2 automatically in the chart editor? 

[#4]

@Maar7 

I have a hard time visualizing all this. Could you attach a sample workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Thanks in advance.

[#5]

@Maar7 

I'm puzzled to why you are using INDIRECT for this. I think it could be easy if you go with the capability of spill ranges of dynamic arrays.

Could you provide a sample workbook to investigate this further?

[#6]

@RecalcOrDie @Hans Vogelaar Thank you both so much for helping! My main goal is to automatically find the correct temperature range for any data set I have (and I think I managed to that), and to plot that ranges accordingly. I often forget to update the ranges in the graph, so therefore I would love to have that automated. I thought this problem would be easy, but I could not find suitable information on dynamic plotting that worked for me. If you have suggestion on complete other methods to it I'm glad to hear them as well. Here is the link: 

https://docs.google.com/spreadsheets/d/1KJko2Vw5Jm1-jtrNQxd1cuTBidpQ1fcv/edit?usp=sharing&ouid=10696... 

Thank you again! 

[#7]

@Maar7 

You'll have to share the workbook. I now get "Access Denied".

[#8]

Sorry, does this one work?
https://docs.google.com/spreadsheets/d/1KJko2Vw5Jm1-jtrNQxd1cuTBidpQ1fcv/edit?usp=sharing&ouid=10696...

[#9]

@Maar7 

Yes, it does. I will take a look.

[#10]

@Maar7 

See the attached version. I created extra named ranges.

[#11]

This was exactly what I was looking for, thank you so much!