IF statement syntax error when referencing another worksheet cell

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

System: MS Windows 97 on Windows N

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
 
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 said:
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
Click to expand...
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
Click to expand...
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")
 
QEM,

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

--

HTH

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

QEM said:
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
Click to expand...
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
Click to expand...
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")
 
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 said:
QEM,

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

--

HTH

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

QEM said:
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
Click to expand...
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
Click to expand...
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.
Click to expand...
Click to expand...
 
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!
 
Back
Top