I can get two formulas to correctly display a value, but when I try to combine the formulas for simplicity I get a "The formula you typed contains an error" error message. The "incorrect" formula (two combined) is:
=HLOOKUP(INDIRECT("'Eq. List-to-P&ID template'!"&CHAR(65+MATCH(LEFT(IF(NOT(LEFT(RIGHT(E3,6),1)="-"),RIGHT(E3,6),RIGHT(E3,5)),2)&"xxx",'Eq. List-to-P&ID template'!$B28:$K28,0))&"30"),'Eq List 5.11.12'!$A$2:$CV$598,MATCH(E3,'Eq List 5.11.12'!$A2:$A598,0),0)
, and it should display "406 GAL", but I get the error message.
The formula broken into two chunks works:
=INDIRECT("'Eq. List-to-P&ID template'!"&CHAR(65+MATCH(LEFT(IF(NOT(LEFT(RIGHT(E3,6),1)="-"),RIGHT(E3,6),RIGHT(E3,5)),2)&"xxx",'Eq. List-to-P&ID template'!$B28:$K28,0))&"30") gives me "Capacity" in cell E34, then
=HLOOKUP(E34,'Eq List 5.11.12'!$A$2:$CV$598,MATCH(E3,'Eq List 5.11.12'!$A2:$A598,0),0) displays "406 GAL" as it should.
So the problem is with combining the two. When I replace the INDIRECT(...) statement in for E34 in the HLOOKUP, I get the error statement. I know this is difficult to track a bug without having the spreadsheet, but does anybody know why this might be happening? All I'm doing is replacing values that already work!
Thanks guys!
Bookmarks