Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tab Name Error
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tab Name Error
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 wrote: 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 -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tab Name Error
Dave,
There's no indirect() function used. Thanks for the reply, tho. -- MacGuy "Dave Peterson" wrote: 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 wrote: 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 -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tab Name Error
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. MacGuy wrote: Dave, There's no indirect() function used. Thanks for the reply, tho. -- MacGuy "Dave Peterson" wrote: 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 wrote: 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 -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
run time error 1004 general odbc error excel 2003 vba | Excel Programming | |||
Counting instances of found text (Excel error? Or user error?) | Excel Worksheet Functions | |||
Error Handling - On Error GoTo doesn't trap error successfully | Excel Programming | |||
run-time error '1004': Application-defined or object-deifined error | Excel Programming | |||
Automation Error, Unknown Error. Error value - 440 | Excel Programming |