Need to SUM a dynamic range

calebsg

New Member
Joined
Sep 12, 2002
Messages
3
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.
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Jim North

MrExcel MVP
Joined
Jun 20, 2002
Messages
791
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

New Member
Joined
Sep 12, 2002
Messages
3
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,128
Messages
5,576,251
Members
412,709
Latest member
Rishu
Top