Forum Discussion
Create new worksheet with one graph using single cell input from multiple worksheets
GIVEN:
- I have an Excel 2003 (don't yell at me!) spreadsheet with multiple worksheets.
- Each worksheet has two columns (text label, numeric value) and a graph showing all the columns' values.
- Each set of two columns ends with a "Total" row containing a single number in a single cell.
- Each "Total" row is in a different row between the worksheets, as there are different inputs for different worksheets (e.g., income from various, multiple, sources), but, the total value is always in the B column (at least, for now!) (e.g., one worksheet may have the total in B7, the next worksheet's total may be in B11, the next in B3, etc).
NEEDED:
I want to take all the worksheets' single cell's total values, and put them into a single worksheet with a single line graph, so that I can easily see how the totals change for all years. I don't think I can do this via Excel 2003, but, I can use Microsoft 365 online (free version) or the third-party app, TrioOffice, if that helps (i.e., I would import my existing spreadsheet to them). I've searched online, but, the methods shown on various third-party websites don't seem to work, because I can't select different worksheets, much less the values in them, after creating the new graph, which they always show appearing on the first worksheet's page, whereas I want the "all totals" (if you will) graph to be in its own worksheet. Ideally, the new graph should show the worksheet name (i.e., its corresponding tab title) on the X axis (i.e., running along the bottom line) and some derivation of the values (e.g., 100, 200, 300, 400, etc) on the Y axis (i.e., running along the left side).
Any help would be appreciated, either by instruction or URL, etc.
FWIW, I'm a computer programmer with over 40 years of experience,
who cannot believe that nobody has needed something like this before me!
That could be done with Excel for web if
- you have texts "Total" or any other one, but the same for all sheets, in Column A against total value in column B
- you add manually sheet names into the master sheet
That could be like
Perhaps the same could be done directly in Excel 2003, but with INDEX/MATCH or VLOOKUP instead of XLOOKUP
The formula in C2 for Excel 2019 and earlier could be
=VLOOKUP("TOTAL", INDIRECT("'"&B2&"'!A:B"), 2, FALSE)
I appreciate the suggestions, but, I found them to be overly complicated. I decided to enter the totals by hand into the new worksheet, and then make the graph from that. It turned out perfectly, though I wish I could change the Y-axis values from 100k to 50k (the "format" lets me change it, but, doesn't do anything with it!). Thanks, anyway.
- I did that. Nothing happened in the result, and, when I clicked the Y-axis, again, it showed the original value, as if I had never changed it! I consider the matter closed; 'tis enough, 'twill serve!
There is a strange dissonance about this discussion. You are an (ex?)-programmer who appears not to want programmatic solutions and is using software that is beyond obsolete!
In the 1970s the way of achieving your goal would be as a manual bookkeeping exercise. In January each year you would add the total for the prior year to the summary sheet, just as you appear to have done now!
In the 1980s Dan Bricklin had the brilliant idea of a system that would allow users to access data by 'point and click' without programming knowledge. That would allow you to type '=', switch tabs, and click the total cell. That was regarded as just about as good as life could be, the user accesses their numbers without the "tedium" of defining variable names.
So what about now? The Excel formula now offers a Turing complete programming language but working across sheets is still not a great experience. Power Query (2010) would do the job of appending data from multiple Tables (2007) but may represent a measure of overkill, especially so if you don't have access to the code.
Personally, with 365, I would use a 3D helper range with the year and total
=TEXTAFTER(CELL("filename",A1),"]") = Data2001[[#Totals],[Value]]Naming the 3D range 'annualValues' the data for plotting would be returned by
= VSTACK(annualValues)A lot more effort by the way of set-up but with payoff later; that is the nature of programming!
K.I.S.S.
K.I.S.S.
A fine principle but the devil is in the interpretation! I tend to find myself travelling in the opposite direction to my financial modelling friends, each searching for 'simplicity'. For me, a single array formula that captures the entire problem is simplicity, for them it is the huge array of scalar formulas that is simple. For me, it is sticking to primitive elements of the calculation that makes the overall structure difficult to visualise.
