jallenamdg
New Member
- Joined
- Oct 21, 2020
- Messages
- 3
- Office Version
- 365
- Platform
- Windows
OK....I'm trying to add an additional block to find the value on another sheet (with the named references). I've formatted it for easier comparison but in actuality this formula is set within a VBA module with a simple assignment statement.
Here's the Original - Obviously, it works fine as is and works if I substitute 4 for 3 on the "..CCR_X" reference (Ex. WO_KeyCCR_4)
=IF(
RC4="","",
IF(NOT(ISERROR(MATCH(RC4,WO_KeyCCR_1,0))),INDEX(WO_TblCCR_1,MATCH(RC4,WO_KeyCCR_1,0),MATCH("WO Qty",WO_HdrCCR_1,0)),
IF(NOT(ISERROR(MATCH(RC4,WO_KeyCCR_2,0))),INDEX(WO_TblCCR_2,MATCH(RC4,WO_KeyCCR_2,0),MATCH("WO Qty",WO_HdrCCR_2,0)),
IF(NOT(ISERROR(MATCH(RC4,WO_KeyCCR_3,0))),INDEX(WO_TblCCR_3,MATCH(RC4,WO_KeyCCR_3,0),MATCH("WO Qty",WO_HdrCCR_3,0)),
"Not in Source Data"))))
New - Application Error during assignment statement (R/T Error 1004 - Application-defined or object-defined error)
=IF(
RC4="","",
IF(NOT(ISERROR(MATCH(RC4,WO_KeyCCR_1,0))),INDEX(WO_TblCCR_1,MATCH(RC4,WO_KeyCCR_1,0),MATCH("WO Qty",WO_HdrCCR_1,0)),
IF(NOT(ISERROR(MATCH(RC4,WO_KeyCCR_2,0))),INDEX(WO_TblCCR_2,MATCH(RC4,WO_KeyCCR_2,0),MATCH("WO Qty",WO_HdrCCR_2,0)),
IF(NOT(ISERROR(MATCH(RC4,WO_KeyCCR_3,0))),INDEX(WO_TblCCR_3,MATCH(RC4,WO_KeyCCR_3,0),MATCH("WO Qty",WO_HdrCCR_3,0)),
IF(NOT(ISERROR(MATCH(RC4,WO_KeyCCR_4,0))),INDEX(WO_TblCCR_4,MATCH(RC4,WO_KeyCCR_4,0),MATCH("WO Qty",WO_HdrCCR_4,0)),
"Not in Source Data")))))
While the number of IF blocks in the new one doesn't exceed 7, I'm wondering if the additional block is exceeding some other limit on complexity?
Thanks!
Here's the Original - Obviously, it works fine as is and works if I substitute 4 for 3 on the "..CCR_X" reference (Ex. WO_KeyCCR_4)
=IF(
RC4="","",
IF(NOT(ISERROR(MATCH(RC4,WO_KeyCCR_1,0))),INDEX(WO_TblCCR_1,MATCH(RC4,WO_KeyCCR_1,0),MATCH("WO Qty",WO_HdrCCR_1,0)),
IF(NOT(ISERROR(MATCH(RC4,WO_KeyCCR_2,0))),INDEX(WO_TblCCR_2,MATCH(RC4,WO_KeyCCR_2,0),MATCH("WO Qty",WO_HdrCCR_2,0)),
IF(NOT(ISERROR(MATCH(RC4,WO_KeyCCR_3,0))),INDEX(WO_TblCCR_3,MATCH(RC4,WO_KeyCCR_3,0),MATCH("WO Qty",WO_HdrCCR_3,0)),
"Not in Source Data"))))
New - Application Error during assignment statement (R/T Error 1004 - Application-defined or object-defined error)
=IF(
RC4="","",
IF(NOT(ISERROR(MATCH(RC4,WO_KeyCCR_1,0))),INDEX(WO_TblCCR_1,MATCH(RC4,WO_KeyCCR_1,0),MATCH("WO Qty",WO_HdrCCR_1,0)),
IF(NOT(ISERROR(MATCH(RC4,WO_KeyCCR_2,0))),INDEX(WO_TblCCR_2,MATCH(RC4,WO_KeyCCR_2,0),MATCH("WO Qty",WO_HdrCCR_2,0)),
IF(NOT(ISERROR(MATCH(RC4,WO_KeyCCR_3,0))),INDEX(WO_TblCCR_3,MATCH(RC4,WO_KeyCCR_3,0),MATCH("WO Qty",WO_HdrCCR_3,0)),
IF(NOT(ISERROR(MATCH(RC4,WO_KeyCCR_4,0))),INDEX(WO_TblCCR_4,MATCH(RC4,WO_KeyCCR_4,0),MATCH("WO Qty",WO_HdrCCR_4,0)),
"Not in Source Data")))))
While the number of IF blocks in the new one doesn't exceed 7, I'm wondering if the additional block is exceeding some other limit on complexity?
Thanks!