IF statement syntax error when referencing another worksheet cell

One way

=IF(INDIRECT("'[Project_Status.xls]TestItems'!"&(CONCATENATE("$F$",J5)))0,"
ERROR", "PROCEED")

now the drawback,for indirect to work both workbooks have to be open.

--

Regards,

Peo Sjoblom


"QEM" wrote in message
...
System: MS Windows 97 on Windows NT

Step 1 worksheet 1 - Successfully Derived the value of the row to
reference in worksheet 2 using
=MATCH(A8,'[Project_Status.xls]TestItems'!$A$1:$A$2000,0)

Step 2 worksheet 1 - Successfully concatenated the row value derived in
step 1 with the column variable to create a cell
location. =CONCATENATE("$F$",J5)

Step 3 worksheet 1 - Trying to use an IF statement to evaluate the
contents of the cell in worksheet 2 and issue an error if the cell has a
value.=IF('[Project_Status.xls]TestItems'!(CONCATENATE("$F$",J5))0,"ERROR",
"PROCEED")

PROBLEM: Syntax error occurs between TestItems'!(CON... I can't find any
info on Help or the book.

QEM,

How about
=IF('[Project_Status.xls]TestItems'!(INDIRECT(CONCATENATE("$F$",J5)))0,"ER R
OR", "PROCEED")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"QEM" wrote in message
...
System: MS Windows 97 on Windows NT

Step 1 worksheet 1 - Successfully Derived the value of the row to
reference in worksheet 2 using
=MATCH(A8,'[Project_Status.xls]TestItems'!$A$1:$A$2000,0)

Step 2 worksheet 1 - Successfully concatenated the row value derived in
step 1 with the column variable to create a cell
location. =CONCATENATE("$F$",J5)

Step 3 worksheet 1 - Trying to use an IF statement to evaluate the
contents of the cell in worksheet 2 and issue an error if the cell has a
value.=IF('[Project_Status.xls]TestItems'!(CONCATENATE("$F$",J5))0,"ERROR",
"PROCEED")

PROBLEM: Syntax error occurs between TestItems'!(CON... I can't find any
info on Help or the book.

Oops, got the INDIRECT in the wrong place. See Peo's post for the right
place.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Bob Phillips" wrote in message
...
QEM,

How about

=IF('[Project_Status.xls]TestItems'!(INDIRECT(CONCATENATE("$F$",J5)))0,"ER R
OR", "PROCEED")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"QEM" wrote in message
...
System: MS Windows 97 on Windows NT

Step 1 worksheet 1 - Successfully Derived the value of the row to
reference in worksheet 2 using
=MATCH(A8,'[Project_Status.xls]TestItems'!$A$1:$A$2000,0)

Step 2 worksheet 1 - Successfully concatenated the row value derived in
step 1 with the column variable to create a cell
location. =CONCATENATE("$F$",J5)

Step 3 worksheet 1 - Trying to use an IF statement to evaluate the
contents of the cell in worksheet 2 and issue an error if the cell has a

value.=IF('[Project_Status.xls]TestItems'!(CONCATENATE("$F$",J5))0,"ERROR",
"PROCEED")

PROBLEM: Syntax error occurs between TestItems'!(CON... I can't find any
info on Help or the book.

Thanks A million!!!!!!!!! I tried INDIRECT but I didn't have the & before concatenate so I was still getting errors. You have saved me a ton of time.

Bob,

Thanks for the feedback. I have it working. I really appreciate your quick response!