Make a chart from a cell on multiple sheets

English
#1

I have a spreadsheet document with multiple sheets. Every business day I duplicate the previous day’s sheet and change values in some of the cells based on external data, stock prices to be exact. Every month I start over by making a new document for that month, then a new sheet for each day, so each document I make will eventually have about 20 sheets in it, one for each business day of the month. Each sheet performs certain operations on some of the values and calculates a number that appears in a specific cell, H4. So, every day, the new sheet has a new value in H4.

Is there a way to make one chart on one page of each document that will chart the values in H4 from each sheet in the document?

Thanks.

#2

Something like this? You can either concatenate the string or use Address function depending on preference.
SummaryPageWithGraph.ods (19.2 KB)

You might need a list of sheet names rather than my simple one, see Is there a way for me to list down all the sheet names of the document in a separate column or sheet? - #3 by LeroyG
or by macro
Get sheet names as a list - #2 by Lupp

1 Like
#6

Thanks much for the suggestions! As they are a bit above my level of sophistication and will require some study in order for me to understand hem, for now I think I may be able to make due with WhiteKnight’s suggestion,

#3

Have you considered writing a reference to each sheet/cell in a row of cells on your ultimate sheet? E.g. =sheet1; H4 then the adjacent cell is =sheet2;H4 then =Sheet3;H4 ad inf then simply charting that row of cells. Once the chart is functioning satisfactorily just copy and paste it to each sheet and it will replicate a single source chart on each page. If you test for content and “null” the chart range cell you can Set the chart line to ignore spaces if you don’t want them to drop to the base of the chart. I always reverse the flow so I see the latest data closest at hand.

#4

I realise it’s similar to EarnestAl’s solution but replaces all the INDIRECT() with a direct “DrillDown” targeting of the appropriate sheet and allows you to individually vary the location of some of the source data as the reference will follow the re-location.

#5

That’s copy and past the chart object only. It will automatically carry its source references to the new location.

#7

Your suggestion seems easy enough. I tried it out. I’ll see what it is like to work with going forward.

Thanks!

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.