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