Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Condition for worksheet reference
This is the function I currently have:
=IF(L2<"",INDIRECT("'"&L2&"'!B"&ROW($A$2)),"") I want to modify it in the following way.... =IF(L2<"", {IF STATEMENT} INDIRECT("'"&L2&"'!B"&ROW($A$2)),"") I want to add another if statement where shown above to check if there is a sheet called '"&L2&'"! . So basically IF('"&L2&'"! EXISTS, TRUE, FALSE) Thanks in advance for any help! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Condition for worksheet reference
Think IF(ISERROR(..) could be used
Try it in this manner: =IF(L2="","",IF(ISERROR(INDIRECT("'"&L2&"'!B"&ROW( $A$2))),"",INDIRECT("'"&L2&"'!B"&ROW($A$2)))) -- Max Singapore http://savefile.com/projects/236895 Downloads:20,500 Files:363 Subscribers:64 xdemechanik --- "JBoyer" wrote: This is the function I currently have: =IF(L2<"",INDIRECT("'"&L2&"'!B"&ROW($A$2)),"") I want to modify it in the following way.... =IF(L2<"", {IF STATEMENT} INDIRECT("'"&L2&"'!B"&ROW($A$2)),"") I want to add another if statement where shown above to check if there is a sheet called '"&L2&'"! . So basically IF('"&L2&'"! EXISTS, TRUE, FALSE) Thanks in advance for any help! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Condition for worksheet reference
Nice Work.
THANKS! "Max" wrote: Think IF(ISERROR(..) could be used Try it in this manner: =IF(L2="","",IF(ISERROR(INDIRECT("'"&L2&"'!B"&ROW( $A$2))),"",INDIRECT("'"&L2&"'!B"&ROW($A$2)))) -- Max Singapore http://savefile.com/projects/236895 Downloads:20,500 Files:363 Subscribers:64 xdemechanik --- "JBoyer" wrote: This is the function I currently have: =IF(L2<"",INDIRECT("'"&L2&"'!B"&ROW($A$2)),"") I want to modify it in the following way.... =IF(L2<"", {IF STATEMENT} INDIRECT("'"&L2&"'!B"&ROW($A$2)),"") I want to add another if statement where shown above to check if there is a sheet called '"&L2&'"! . So basically IF('"&L2&'"! EXISTS, TRUE, FALSE) Thanks in advance for any help! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Condition for worksheet reference
That'll work but there is a caveat.
This would probably only apply if you're using the default sheet names. If the sheet already exists then it's no problem. If the sheet does not exist and you insert a new sheet the formula will not update until some event triggers a calculation (like renaming a newly inserted sheet!). Deleting the sheet referenced will trigger a calcualtion and the formula would update. -- Biff Microsoft Excel MVP "Max" wrote in message ... Think IF(ISERROR(..) could be used Try it in this manner: =IF(L2="","",IF(ISERROR(INDIRECT("'"&L2&"'!B"&ROW( $A$2))),"",INDIRECT("'"&L2&"'!B"&ROW($A$2)))) -- Max Singapore http://savefile.com/projects/236895 Downloads:20,500 Files:363 Subscribers:64 xdemechanik --- "JBoyer" wrote: This is the function I currently have: =IF(L2<"",INDIRECT("'"&L2&"'!B"&ROW($A$2)),"") I want to modify it in the following way.... =IF(L2<"", {IF STATEMENT} INDIRECT("'"&L2&"'!B"&ROW($A$2)),"") I want to add another if statement where shown above to check if there is a sheet called '"&L2&'"! . So basically IF('"&L2&'"! EXISTS, TRUE, FALSE) Thanks in advance for any help! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Condition for worksheet reference
Welcome, thanks.
-- Max Singapore http://savefile.com/projects/236895 Downloads:20,500 Files:363 Subscribers:64 xdemechanik --- "JBoyer" wrote: Nice Work. THANKS! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Condition for worksheet reference
Thanks for the info.
"T. Valko" wrote: That'll work but there is a caveat. This would probably only apply if you're using the default sheet names. If the sheet already exists then it's no problem. If the sheet does not exist and you insert a new sheet the formula will not update until some event triggers a calculation (like renaming a newly inserted sheet!). Deleting the sheet referenced will trigger a calcualtion and the formula would update. -- Biff Microsoft Excel MVP "Max" wrote in message ... Think IF(ISERROR(..) could be used Try it in this manner: =IF(L2="","",IF(ISERROR(INDIRECT("'"&L2&"'!B"&ROW( $A$2))),"",INDIRECT("'"&L2&"'!B"&ROW($A$2)))) -- Max Singapore http://savefile.com/projects/236895 Downloads:20,500 Files:363 Subscribers:64 xdemechanik --- "JBoyer" wrote: This is the function I currently have: =IF(L2<"",INDIRECT("'"&L2&"'!B"&ROW($A$2)),"") I want to modify it in the following way.... =IF(L2<"", {IF STATEMENT} INDIRECT("'"&L2&"'!B"&ROW($A$2)),"") I want to add another if statement where shown above to check if there is a sheet called '"&L2&'"! . So basically IF('"&L2&'"! EXISTS, TRUE, FALSE) Thanks in advance for any help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
deleting rows in a worksheet if condition is met | Excel Discussion (Misc queries) | |||
copy row content from one worksheet to another, if a condition met | Excel Worksheet Functions | |||
copy data from 1 worksheet to another based on a condition | Excel Worksheet Functions | |||
External reference as a condition | Excel Worksheet Functions | |||
External reference as a condition | Excel Worksheet Functions |