#value error in a formula (repost)

  • Thread starter Thread starter Trip Levert
  • Start date Start date
T

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
 
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
 
It still won't work unless the other wb is open..

--

Regards,

Peo Sjoblom


Peter Atherton said:
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

.
Click to expand...
Click to expand...
 
...
...
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?
Click to expand...

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)))
 
Back
Top