( by Simon Scott )
I need to know how to solve the problem of having to manually type every "daily total" formula results from the identical cells on each daily sheet template, into my monthly total sheet.
It is far to labour intensive to have to do this task one cell at a time and there has to be a way to speed this up with an existing method? As I will be creating new sheets for every month of the year and many years going forward, there must be a better way.
I have completed the first results that I want as you can see in the sheet labelled "July, 2013" and at cells C4:J4
The Screenshot of my sheets to see my problem:
The Screenshot Sheet '1st':
The Screenshot Sheet 'July 2013':
Thank you for your contributions!
Simon
Macbook Pro on MacOSX 10.7.5
==========
I have posted the question in another thread I started (but seem to have no takers as yet).
This is definitely an ongoing problem of mine for years with docs and it seems to come up many times with the same answer which appears to be "no".
I don't have enough doc sheet formula experience to answer this, or to have found the right combination of calculations to find the solution.
But....
I have an idea that may lead us to a solution for all.
Is it possible to do something like the following?
Is there was a way to pull the tab names, which would be listed in a "master sheet", and this sheet (because it's used only to format such a thing) is then hidden from the workbook.
For example: It seems the only way currently to do this is manually and one cell at a time like this (in a top to bottom row in this case):
PRIMARY-SHEET-CELL A1 =SUM('SHEET 1'!C$60:Y$60), and then
PRIMARY-SHEET-CELL A2 =SUM('SHEET 2'!C$60:Y$60) and so on.....
all the while having to manually open the cell to change each sheet name.
However, is it possible to place a formula inside this current formula like this:
PRIMARY-SHEET-CELL A1 =SUM('TEXT-CONTENTS-OF-
PRIMARY-SHEET-CELL A2 =SUM('TEXT-CONTENTS-OF-MASTER-
In my case, with my workbook being one with month "day" numbers as the tab names (1st, 2nd, 3rd etc...), although it isn't pulling the actual tab names.... (but data in a real cell on a real page within the workbook that matches the name), can something like this be worked into a solution?
As the formula does not actually attempt to reference the sheet name directly but rather a "exact duplicate text string" in a cell found elsewhere, I'm hoping that somebody has a way to put this theory into practical use somehow. Using much more knowledge than I possess.
Feel free to try your work arounds on my sheet as linked above or if you can solve it using this idea, please share it in a response.
Thank you so much for your efforts!
Simon
Solution:
Have a look at the screenshot of my Spreadsheet having solution:
The formula in Cell C3 of Sheet 'July 2013' is:
=transpose(query('1'!B60:AK67;"select B"))
Note: I have changed the sheet name '1st' to '1'
And from '2nd' to '2'
from '3rd' to '3'
and so on....
The formula in Cell C4 of Sheet 'July 2013' is:
=transpose(query(indirect("'"&row()-3&"'!A60:AK67");"select Col4+Col7+Col10+Col13+Col16+Col19+Col22+Col25+Col28+Col31+Col34+Col37 label Col4+Col7+Col10+Col13+Col16+Col19+Col22+Col25+Col28+Col31+Col34+Col37 '' "))
the above formula will auto populate Cells D4,D5,D6,D7 and so on..
Now, drag the formula from Cell C4 to C5... C6... and so on....
After dragging the formula in Cell C5 will be:
=transpose(query(indirect("'"&row()-3&"'!A60:AK67");"select Col4+Col7+Col10+Col13+Col16+Col19+Col22+Col25+Col28+Col31+Col34+Col37 label Col4+Col7+Col10+Col13+Col16+Col19+Col22+Col25+Col28+Col31+Col34+Col37 '' "))
I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.
I also take up private and confidential projects:
If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html
No comments:
Post a Comment