Forum Discussion
Dynamic scatter chart based on a reference to an address in a cell
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!
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.
HansVogelaar 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?
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?
