I'm busting my brains here ... all I want to do is graph a line representing the values of a single cell across multiple worksheets. To that effect, the chart data range should look something like

='Sheet1:Sheet10'!$D$5
​

But Excel keeps on rejecting it, no matter how many different ways I've tried to tweak it to make it work. What gives?

@MrSoftware, you might be able to offer some insight into this conundrum of mine ... if you have the time

 

I'm busting my brains here ... all I want to do is graph a line representing the values of a single cell across multiple worksheets. To that effect, the chart data range should look something like

='Sheet1:Sheet10'!$D$5
​

But Excel keeps on rejecting it, no matter how many different ways I've tried to tweak it to make it work. What gives?

@MrSoftware, you might be able to offer some insight into this conundrum of mine ... if you have the time

I'm not aware of any way to do that directly. However you could do it by accumulating the data from the various sheets into a single sheet and then plotting the cells from there. I did that a lot, so I know it works.

 

Ditto to wayneh's comment. If there's too much data to do it manually or if you want it to update automatically as the data on the source sheets changes, then you can write a cell formula that constructs the sheet name based on the cell's address in your accumulator sheet. Do do that most efficiently, you would need to have your source sheets named systematically, though another way would be to use a lookup table. You could also write some VBA code to automate that process.

 

I think this is what you want, or hopefully is close enough. As mentioned above, it's not graphing the data directly, instead you first accumulate the data on a new sheet and then graph it from there.

https://www.atlaspm.com/toms-tutori...alue-of-the-same-cell-on-multiple-worksheets/

Basically, if I have Sheet1, Sheet2, Sheet3 and I want the value in cell A1 on each sheet, then on Sheet4 I do this in any arbitrary cell:

=INDIRECT("'Sheet"&ROW()-1&"'!"&$E$1)

Now in cell E1 on this sheet I put the cell number that I want to read on each sheet, in this case A1. Then drag the formula cell down for each sheet, then graph the results. For examle, Sheet1!A1 has "1", Sheet2!A1 has "2" and Sheet3!A1 has "4". On Sheet4 I do this:

 

I think this is what you want, or hopefully is close enough. As mentioned above, it's not graphing the data directly, instead you first accumulate the data on a new sheet and then graph it from there.

https://www.atlaspm.com/toms-tutori...alue-of-the-same-cell-on-multiple-worksheets/

Basically, if I have Sheet1, Sheet2, Sheet3 and I want the value in cell A1 on each sheet, then on Sheet4 I do this in any arbitrary cell:

=INDIRECT("'Sheet"&ROW()-1&"'!"&$E$1)

Now in cell E1 on this sheet I put the cell number that I want to read on each sheet, in this case A1. Then drag the formula cell down for each sheet, then graph the results. For examle, Sheet1!A1 has "1", Sheet2!A1 has "2" and Sheet3!A1 has "4". On Sheet4 I do this:

View attachment 178367

View attachment 178369

Excellent! ... Worked like a charm, thank you very much. Of course I had to tweak it a little to make it work exactly how I wanted it to, but I've learned an important thing about Excel in the process. Also, thanks wayneh & Bahn for your suggestions.

 

I think this is what you want, or hopefully is close enough. As mentioned above, it's not graphing the data directly, instead you first accumulate the data on a new sheet and then graph it from there.

Basically, if I have Sheet1, Sheet2, Sheet3 and I want the value in cell A1 on each sheet, then on Sheet4 I do this in any arbitrary cell:

=INDIRECT("'Sheet"&ROW()-1&"'!"&$E$1)

Now in cell E1 on this sheet I put the cell number that I want to read on each sheet, in this case A1. Then drag the formula cell down for each sheet, then graph the results. For examle, Sheet1!A1 has "1", Sheet2!A1 has "2" and Sheet3!A1 has "4". On Sheet4 I do this:

Yep. That's exactly what I was referring to. I use this level of spreadsheet programming infrequently enough that I always have to spend ten minutes or so relearning it and playing with it to get up to speed again. Possibly because some of the functions involved seem (to me) to be nonintuitive and needlessly complicated, so I can never internalize them.

It's amazing the power that Excel has just in the basic functions it supports -- and I know that I've only scratched the surface of what's available even amongst them.

 

Just a related trick that may save you some headaches when doing this:

• Named ranges in the source sheets can simplify trying to keep track of the exact position of the source cell(s). Once the name is applied, Excel keeps track of its location and properties. Here's an example where I'm extracting the last data point in a column containing flow rate data.

=INDEX(INDIRECT($DD62&"!Flow"),$AA62,1)
DD62 contains the name of the sheet, the named range is "Flow", AA62 contains the number of rows in the range​