OFFSET function on external sheets
 

Hello,

I use the OFFSET function in workbook1, with a reference in workbook2.
When I open only workbook1 (without opening workbook2) it does not give me a
result but only a #Value.
Does not matter ig I chose "update" or "do not update".
As soon as Workbook2 is open, the results shows...

I have no problem of that kind with the lookup function...

I there any way to solve this ?

OFFSET function on external sheets
 

There are some functions that don't work with closed workbooks. =offset() is
one of them.

Maybe you could use =index() instead?????

Raf wrote:

Hello,

I use the OFFSET function in workbook1, with a reference in workbook2.
When I open only workbook1 (without opening workbook2) it does not give me a
result but only a #Value.
Does not matter ig I chose "update" or "do not update".
As soon as Workbook2 is open, the results shows...

I have no problem of that kind with the lookup function...

I there any way to solve this ?

--

Dave Peterson

OFFSET function on external sheets
 

"Dave Peterson" wrote...
There are some functions that don't work with closed workbooks. =offset()
is one of them.
....

More specifically, functions that either require particular arguments be
range references or return range references can't work with closed
workbooks. OFFSET requires both: its 1st argument must be a range reference,
and it returns a range reference. Excel evaluates external references into
closed workbooks as either scalar (single) values or arrays, not as range
references in Excel's strict working definition of 'range'.

Some of the others are INDIRECT (always returns range reference), RANK (2nd
arg must be range reference, no matter what its online help says), COUNTIF
(1st arg must be range reference), SUMIF (1st and optional 3rd args must be
range references), CELL (optional 2nd arg must be range reference).

OFFSET function on external sheets
 

INDEX helped. Thank you for the explanation as well so I stop looking for
what was wrong

"Dave Peterson" skrev:

There are some functions that don't work with closed workbooks. =offset() is
one of them.

Maybe you could use =index() instead?????

Raf wrote:

Hello,

I use the OFFSET function in workbook1, with a reference in workbook2.
When I open only workbook1 (without opening workbook2) it does not give me a
result but only a #Value.
Does not matter ig I chose "update" or "do not update".
As soon as Workbook2 is open, the results shows...

I have no problem of that kind with the lookup function...

I there any way to solve this ?

--

Dave Peterson

OFFSET function on external sheets
 

Thank you for the details, especially regarding other formulas with the same
requirements. You solved my SUM.IF problemed befor it appeared.

"Harlan Grove" skrev:

"Dave Peterson" wrote...
There are some functions that don't work with closed workbooks. =offset()
is one of them.
....

More specifically, functions that either require particular arguments be
range references or return range references can't work with closed
workbooks. OFFSET requires both: its 1st argument must be a range reference,
and it returns a range reference. Excel evaluates external references into
closed workbooks as either scalar (single) values or arrays, not as range
references in Excel's strict working definition of 'range'.

Some of the others are INDIRECT (always returns range reference), RANK (2nd
arg must be range reference, no matter what its online help says), COUNTIF
(1st arg must be range reference), SUMIF (1st and optional 3rd args must be
range references), CELL (optional 2nd arg must be range reference).