#value error in a formula

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

Trip Levert

Hi All,
I am currently using the following formula:

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
 
Hi

At start there is something weird with your formula. Maybe it must be
=SUM(OFFSET($C2,,1,MATCH($G$1,$C$1:$E$1,0)))

This is when source data are on same sheet. When not, then
=SUM(OFFSET(SheetName!C2,,1,MATCH($G$1,SheetName!$C$1:$E$1,0)))

When source data are external (in another workbook), then you can mirror
them on separate sheet(s) in target workbook (the one with formula in it),
and refer to those mirrored sheets. An example when source data are on sheet
SourceSheet in workbook SourceFile which is placed in folder 'C:\My
Documents\':

Create a sheet in your workbook, p.e. SourceMirror.
Into cell C1 in SourceMirror enter the formula:
=IF('C:\My Documents\[SourceFile.xls]SourceSheet'!C1="","",'C:\My
Documents\[SourceFile.xls]SourceSheet'!C1)
Copy the formula into range C1:E#, where # is at least same as number of
rows in source table - a copy of source data is created.
Hide the sheet SourceMirror

Now the formula will be:
=SUM(OFFSET(SourceMirror!C2,,1,MATCH($G$1,SourceMirror!$C$1:$E$1,0)))
and data are updated without problems
 
Back
Top