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?
--
MacGuy