OFFSET formula does not appear: #VALUE!

  • Thread starter Thread starter man
  • Start date Start date
M

man

This one does not work:

=+OFFSET('U:\MWDOC\Local Project Development\[Local
Projects.xls]OCWD'!$A$13,'Management Options'!$B$38,D21+24)

It appears as #VALUE! in the cell, but when the worksheet
is opened in EXCEL, the cell has the correct value.

Do you know why?

Thank you for your help in advance.
 
Thank you Don for the reply. This function gives me a
#VALUE! only when I have the "reference" file closed.
With the file open, the cell has a correct value.

Do you know of a workaround for this?

Thank you.
-----Original Message-----
Look in HELP index for OFFSET
OFFSET(reference,rows,cols,height,width)

man said:
This one does not work:

=+OFFSET('U:\MWDOC\Local Project Development\[Local
Projects.xls]OCWD'!$A$13,'Management Options'! $B$38,D21+24)

It appears as #VALUE! in the cell, but when the worksheet
is opened in EXCEL, the cell has the correct value.

Do you know why?

Thank you for your help in advance.
Click to expand...


.
Click to expand...
 
Thank you Don for the reply. This function gives me a
#VALUE! only when I have the "reference" file closed.
With the file open, the cell has a correct value.

Do you know of a workaround for this?
Click to expand...

There are no simple work-arounds.

Ranges in open files are range objects. Ranges in closed files aren't - they're
treated as arrays. OFFSET can only accept ranges as 1st argument, so when the
file is closed, OFFSET chokes.

Your formula (dropping the unnecessary unary +),

=OFFSET('U:\MWDOC\Local Project Development\[Local Projects.xls]OCWD'!$A$13,
'Management Options'!$B$38,D21+24)

is offsetting from a single cell, so returning a single value (since you're
implicitly using default 4th and 5th arguments). You could use INDEX to do this,
since INDEX can accept either ranges or arrays as 1st argument. Depending on the
reasonable range for your 2nd and 3rd arguments to OFFSET, try

=INDEX('U:\MWDOC\Local Project Development\[Local Projects.xls]
OCWD'!$A$13:$AZ$1012,'Management Options'!$B$38+1,D21+25)

More complicated alternatives may be found in

http://www.google.com/[email protected]
 
Back
Top