Tab Name Error

[MacGuy]

I have a user's file that I would like to use the sheet tab name in formulas
that pull data from another file. For example, "Chicago" is a string (no
formula) in cell A1, and is used in various Vlookup and Sumif formulas to
populate the schedule. The formulas reference the source file, and when
opened with "do not update" references there is no problem (source file not
opened).

When I put the formula to pull the tab name - using the MID worksheet
function to popluate "Chicago" in cell A1 - I get a #REF error on every
formula until the source file is opened. I've also used the formula where
RIGHT is used instead of MID.

In short, using the either formula, I get #REF errors until the source file
is opened; without the MID formula, no errors. Any ideas?

 

[Dave Peterson]

I bet your formulas include the =indirect() function. If that's true, then that
function returns an error if the sending workbook is closed.

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that may help you.
function returns an error if the sending workbook is closed.

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that may help you.

 

[MacGuy]

Dave,

There's no indirect() function used. Thanks for the reply, tho.

 

[Dave Peterson]

Other worksheet functions don't work with closed workbooks.

=sumif() and =countif() are a couple

but =vlookup() will work with closed workbooks.

Maybe you could replace the =sumif() with an equivalent =sumproduct() function.

But without more details, I don't think you'll get a specific suggestion.

Dave,

There's no indirect() function used. Thanks for the reply, tho.