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.
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

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,210
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,501
Messages
5,832,079
Members
430,110
Latest member
Chyke_mxl

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top