Tags: cell, derived, error, excel, microsoft, msdn, reference, referencing, row, software, statement, step, successfully, syntax, system, value, windows, worksheet

IF statement syntax error when referencing another worksheet cell

On Microsoft » Microsoft Excel

5,127 words with 5 Comments; publish: Fri, 23 May 2008 19:57:00 GMT; (30660.55, « »)

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

All Comments

Leave a comment...

  • 5 Comments
    • 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" <anonymous.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      news:4453CDA9-4FAC-4B29-940A-8B069CBE9056.excel.todaysummary.com.microsoft.com...

      > 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.

      >

      #1; Fri, 23 May 2008 19:59:00 GMT
    • 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" <anonymous.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      news:4453CDA9-4FAC-4B29-940A-8B069CBE9056.excel.todaysummary.com.microsoft.com...

      > 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.

      >

      #2; Fri, 23 May 2008 20:00:00 GMT
    • 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" <bob.phillips.excel.todaysummary.com.notheretiscali.co.uk> wrote in message

      news:uD5F4V2uDHA.1596.excel.todaysummary.com.TK2MSFTNGP10.phx.gbl...

      > 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" <anonymous.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      > news:4453CDA9-4FAC-4B29-940A-8B069CBE9056.excel.todaysummary.com.microsoft.com...

      > > 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.

      > >

      >

      #3; Fri, 23 May 2008 20:01:00 GMT
    • Bob,

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

      #4; Fri, 23 May 2008 20:02:00 GMT
    • 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.
      #5; Fri, 23 May 2008 20:03:00 GMT