Tab Name Error

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

MacGuy - 14 Apr 2008 22:06 GMT

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?

Signature

MacGuy

Reply to this Message

Dave Peterson - 14 Apr 2008 23:24 GMT

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.

> 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
[quoted text clipped - 12 lines]
> --
> MacGuy

Signature

Dave Peterson

Reply to this Message

MacGuy - 15 Apr 2008 14:38 GMT

Dave,

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

Signature

MacGuy

> I bet your formulas include the =indirect() function. If that's true, then that
> function returns an error if the sending workbook is closed.
[quoted text clipped - 26 lines]
> > --
> > MacGuy

Reply to this Message

Dave Peterson - 15 Apr 2008 16:45 GMT

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,
>
[quoted text clipped - 36 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message