Using the Offset function with a cell specified in the formula in another cell

Question

*UPDATED* FOUND A SOLUTION HERE: http://answers.microsoft.com/en-us/office/forum/office\_2007-excel/how-can-i-enter-different-worksheet-names-in-an/9326e6d7-908a-49ae-81fe-7edd064c50d5

I have an excel file that has a summary worksheet and 12 data entry worksheets (one for each month of the year).  The summary worksheet needs to collect and display the data from each month. 

Unfortunately, the way the worksheet is designed, each month's worksheet has the data to be transferred in different cells (did I mention that I didn't design this file?).  This means that I have to set the formula on the summary sheet by hand.  And there are FOUR totals columns on the summary sheet for which I have to do this hideous manual thing. 

The silver lining is that the "totals" cells on the individual worksheets are all in the same location WITH REGARD TO THE ORIGINAL hand entered cell. 

So I figure that if I hand enter the first summary column by hand, I should be able to say "pick the cell that is 1 row down and 2 columns to the left of the cell referenced in the formula in the cell to the left of this one" 

I tried to do this with the formula: =OFFSET(RIGHT(FORMULATEXT(F5),LEN(FORMULATEXT(F5))-1),1,-1) where F5 is the cell holding the reference to the cell I want to use as the starting point for the offset (in this case it is "=Aug15!Q24" where Aug15! is the worksheet holding the August data and the cell I want to reference is Q25).  This formula is giving me an error saying "there is a problem with this formula."  Help?  Once I figure this out, I need to also figure out how to point it to P25...but hopefully I can figure that out after I get this first step done.  Here is an abbreviated description of my worksheets:

Summary sheet layout

                      TOT $spent in Aug     TOT # purchased in Aug     AVG cost/unit    unit name....(these 4 columns repeat for each month)

apples

beets

broccoli

etc

Individual Worksheets' layout

                         spent                               #purchased                              cost/unit            unit designation

apples

apples

TOTALS FOR APPLES THIS MONTH

beets

TOTALS FOR BEETS THIS MONTH

broccoli

broccoli

broccoli

TOTALS FOR BROCCOLI THIS MONTH

For each month, there are a different # of purchases of each type of produce, so the "totals" cells aren't in a consistent location on each worksheet to be transfer to the summary sheet.

Answer 1

I found the answer on another thread here: http://answers.microsoft.com/en-us/office/forum/office\_2007-excel/how-can-i-enter-different-worksheet-names-in-an/9326e6d7-908a-49ae-81fe-7edd064c50d5

Answer 2

That's interesting too.  Unfortunately, my coworker wasn't consistent with how she entered the labels for the totals across the worksheets.

Answer 3

Thanks for the thoughts.  Unfortunately my coworker who set this sheet up has already done all the subsummarizing on the worksheets and has put things where she will.  Since that work is done, I'd like to take advantage of it (knowing that we can set this up much better in the future!).

I actually found the solution on a thread titled "

How can I enter different worksheet names in an 'offset' formula"

Answer 4

Another possible suggestion:

If you use (or there is) some unique but consistent format for the TOTAL lines on the individual monthly sheets, you could then use VLOOKUP to get the totals into the summary sheet:

For example

Individual Worksheets' layout

                         spent                               #purchased                              cost/unit            unit designation

apples

apples

TOTAL: Apples

beets

TOTAL: Beets

broccoli

broccoli

broccoli

TOTAL: Broccoli

Then on the summary sheet, to get spent for August for apples:

=VLOOKUP("TOTAL:apples", Aug15!A2:E10, 2,FALSE)

Regards

Murray

Answer 5

Re:  master and child sheets

An alternative to consider... the totals on the individual sheets don't have to go immediately below the list of costs.

If you never have more than, say X items purchased each month then the total on all sheets could go on row X +2,

or they could appear in row 1 on all sheets.

And, what is "FORMULATEXT"  ?

'---

Jim Cone

Portland, Oregon USA

http://jmp.sh/K95N3ee