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?
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?
RecalcOrDie HansVogelaar 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=106966733465153993320&rtpof=true&sd=true
Thank you again!
You'll have to share the workbook. I now get "Access Denied".
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.
