Need to SUM a dynamic range

[calebsg]

Hi folks,

I thought I knew something about Excel, but this one has me beat.

I have this function: SUM(Sheet1:Sheet10!C68) and it works. But, I need the H68 part to be dynamically generated. I have a cell (A4) where a date is entered, then that date should be matched against Sheet 1 and the cell reference is grabbed. The cell reference won't change from page to page, but the value of the cell will.

I can look up and grab the cell reference corresponding to the date with ADDRESS(MATCH(A4,Sheet1!A1:A94,0)-1,7,4,TRUE) to return the cell C68 on the first sheet.

When I try to put the SUM and ADDRESS/MATCH functions together like this:
SUM(Sheet1:Sheet10!ADDRESS(MATCH(A4,Sheet1!A1:A94,0)-1,7,4,TRUE))
I get an error message saying my formula contains an error. What can I do?

If I need to clarify a point just say so.

Thanks in Advance.

 

[Jim North]

I'm at home with no resources... but try:

SUM("Sheet1:Sheet10!" & ADDRESS(MATCH(A4,Sheet1!A1:A94,0)-1,7,4,TRUE))

or

SUM(Sheet1:Sheet10! & ADDRESS(MATCH(A4,Sheet1!A1:A94,0)-1,7,4,TRUE))

Hope this helps

 

[calebsg]

The first one gives #N/A and the second an error.

Any other ideas?

 

[calebsg]

beg your pardon, the first one gives a #VALUE error. I stripped out the ADDRESS(MATCH()) portion and it returns a value of G68. I checked G68 on the pages and they all have numbers in them.

 

[Aladin Akyurek]

On 2002-09-13 15:47, calebsg wrote:
beg your pardon, the first one gives a #VALUE error. I stripped out the ADDRESS(MATCH()) portion and it returns a value of G68. I checked G68 on the pages and they all have numbers in them.

I don't believe constructing 3d refs bit by bit is possible.