+ Reply to Thread
Results 1 to 3 of 3

IF Statement to match reference on separate worksheet and pull data through

  1. #1
    Registered User
    Join Date
    03-19-2019
    Location
    Essex, England
    MS-Off Ver
    2016
    Posts
    90

    IF Statement to match reference on separate worksheet and pull data through

    Hi All

    Thanks for looking!

    I have a workbook with 2 worksheets named 'Jobs Log' and "Bills".

    In column 'K' on the 'Bills' worksheet I need to add a formula to match the reference in column 'A' (still in the 'Bills' worksheet) to the corresponding ref on the 'Jobs Log' in column 'A' and check what is in column 'F'. If column 'F' meets certain criteria it will display 'N/A' or be left blank.

    Essentially all I want it to do is auto populate the cell in column 'K' (Bills) with 'N/A' so I know that an invoice does not need to be raised.

    If column 'F' on the 'Jobs Log' worksheet was on the same worksheet as the formula it would just be a simple - '=IFERROR(IFS(F2="Cadmium Analysis","N/A"),"")' but of course its not and I cant add it so need to find this work around.

    Any help you can offer would be greatly appreciated and if you need anything more from me please just let me know.

    Thanks

    Stuart

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,849

    Re: IF Statement to match reference on separate worksheet and pull data through

    Try it like this in K2 of the Bills sheet:

    =IF(COUNTIF('Jobs Log'!A:A,A2),IF(VLOOKUP(A2,'Jobs Log'!A:F,6,0)="Cadmium Analysis","N/A",""),"Not found")

    then copy down as required.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    03-19-2019
    Location
    Essex, England
    MS-Off Ver
    2016
    Posts
    90

    Re: IF Statement to match reference on separate worksheet and pull data through

    Thank you Pete_UK, worked like a charm.

    As always your help is very much appreciated.

    Stuart

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Match data on two sheets and pull identifer value from a separate column
    By kmrevolution in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-01-2014, 08:43 PM
  2. Replies: 2
    Last Post: 06-03-2013, 06:16 PM
  3. I'm lazy. There has to be a faster way to pull data & cross reference a worksheet, right?
    By colleendeborah in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-28-2013, 07:52 PM
  4. Replies: 6
    Last Post: 03-17-2009, 05:39 AM
  5. [SOLVED] How do I pull a date from a separate worksheet using the IF functi
    By Anne Troy in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 09-06-2005, 06:05 PM
  6. [SOLVED] How do I pull a date from a separate worksheet using the IF functi
    By Jerrod Mason in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 PM
  7. [SOLVED] How do I pull a date from a separate worksheet using the IF functi
    By Jerrod Mason in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1