#value error in a formula (repost)

[Trip Levert]

Hi All,
I am currently using the following formula in Excel 2002:

SUM(OFFSET(C2:E2,0,0,1,MATCH($G$1,$C$1:$E$1,0)))
The range $C$1:$E$ are months Jan, etc. so if you enter
into cell G1 Feb or some other month, it will then sum
through that point. My problem is that the some of the
data(ranges $C$1:$E$ and C2:E2)exist in another workbook.
It appears that the other workbook must be open to avoid
the #value message. Updating links does not help. Is there
a way to get around this?
Thanks
Trip

 

[Peter Atherton]

Hello Trip

I'd try putting the full path of the formula

SUM(OFFSET([anotherworkbook.xls]sheet2!C2:E2,0,0,1,MATCH
($G$1,[anotherworkbook]sheet2!$C$1:$E$1,0)))

Hope this works
Regards
Peter

 

[Peo Sjoblom]

It still won't work unless the other wb is open..

--

Regards,

Peo Sjoblom

Hello Trip

I'd try putting the full path of the formula

SUM(OFFSET([anotherworkbook.xls]sheet2!C2:E2,0,0,1,MATCH
($G$1,[anotherworkbook]sheet2!$C$1:$E$1,0)))

Hope this works
Regards
Peter

-----Original Message-----
Hi All,
I am currently using the following formula in Excel 2002:

SUM(OFFSET(C2:E2,0,0,1,MATCH($G$1,$C$1:$E$1,0)))
The range $C$1:$E$ are months Jan, etc. so if you enter
into cell G1 Feb or some other month, it will then sum
through that point. My problem is that the some of the
data(ranges $C$1:$E$ and C2:E2)exist in another workbook.
It appears that the other workbook must be open to avoid
the #value message. Updating links does not help. Is there
a way to get around this?
Thanks
Trip

.

 

[Harlan Grove]

...
...

SUM(OFFSET(C2:E2,0,0,1,MATCH($G$1,$C$1:$E$1,0))) ...
. . . My problem is that the some of the
data(ranges $C$1:$E$ and C2:E2)exist in another workbook.
It appears that the other workbook must be open to avoid
the #value message. Updating links does not help. Is there
a way to get around this?

OFFSET simply won't work work when its first argument is a reference to a range
in a closed file. One workaround would be

=SUMPRODUCT('[fileref]sheetref'!C2:E2,--(COLUMN(C2:E2)-CELL("Col",C2:E2)
<MATCH($G$1,'[fileref]sheetref'!$C$1:$E$1,0)))