+ Reply to Thread
Results 1 to 8 of 8

VLOOKUP rows based on True/False

  1. #1
    Registered User
    Join Date
    04-09-2004
    Posts
    8

    VLOOKUP rows based on True/False

    Hi guys, looking for advise as to how to approach this....

    There is a sheet in my workbook called "Tanks". It has a couple of dozen rows that contain data about all the tanks on a vessel.

    A second sheet, called "Inspection", has perhaps half a dozen rows that contain data about the tanks that are selected for inspection. Column A in each worksheet is called "Tank Name". The next five columns in "Inspection" contain data from five of the columns in "Tanks".

    At the moment, the user enters the tank name of tanks to be inspected into column A of "Inspection", and a VLOOKUP function pulls over the data from "Tanks". However, if the user types the name in "Inspection" even slightly different from how he typed it in "Tanks" - he types 4 STBD in one, and then 4 Stbd or 4STBD, for example in the other - it errors.

    What I'd like is to have a field in "Tanks" for "Is Used?". If the user puts an 'X' in it, then "Tank Name" from that row is copied over into Column A of Inspection.

    How would I approach this?

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: VLOOKUP rows based on True/False

    Welcome to the forum.

    There are instructions at the top of the page explaining how to attach your sample workbook.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    04-09-2004
    Posts
    8

    Re: VLOOKUP rows based on True/False

    Here is a simplified book with an example of how it looks now. I'd like to add a column in 'Tanks" which, when not blank, causes that row tank name to be copied to Inspection.

    Richard
    Attached Files Attached Files

  4. #4
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    China Shanghai
    MS-Off Ver
    365 V2402 and WPS V2022
    Posts
    3,327

    Re: VLOOKUP rows based on True/False

    Inspection worksheet , B2 cell formula , Drag down and accross

    HTML Code: 

  5. #5
    Registered User
    Join Date
    04-09-2004
    Posts
    8

    Re: VLOOKUP rows based on True/False

    Thanks, wk, but that's not what I'm looking for.

    What I want is to have a field in each row of "Tanks" - I've made it Column 'L' in this revised sample - in which the user indicates whether that tank needs to be included in "Inspection"

    If 'Tanks'!L2 is X, then 'Tanks'!A2 is copied to 'Inspection'!A2.
    If 'Tanks'!L3 is blank, nothing happens.
    If 'Tanks'!L4 is X, then 'Tanks'!A4 is copied to 'Inspection'!A3. (Not A4, so as not to leave blank lines)
    Attached Files Attached Files

  6. #6
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    China Shanghai
    MS-Off Ver
    365 V2402 and WPS V2022
    Posts
    3,327

    Re: VLOOKUP rows based on True/False

    Quote Originally Posted by rickinnocal View Post
    Thanks, wk, but that's not what I'm looking for.

    What I want is to have a field in each row of "Tanks" - I've made it Column 'L' in this revised sample - in which the user indicates whether that tank needs to be included in "Inspection"

    If 'Tanks'!L2 is X, then 'Tanks'!A2 is copied to 'Inspection'!A2.
    If 'Tanks'!L3 is blank, nothing happens.
    If 'Tanks'!L4 is X, then 'Tanks'!A4 is copied to 'Inspection'!A3. (Not A4, so as not to leave blank lines)
    Inspection worksheet , A2:F2 cell array formula , Drag down



    Did I help significantly? If you wish, click on * Add Reputation to say thanks.
    If your problem has been resolved please select “Solved” from the Thread Tools menu
    Attached Files Attached Files
    Last edited by wk9128; 09-30-2020 at 10:34 PM.

  7. #7
    Registered User
    Join Date
    04-09-2004
    Posts
    8

    Re: VLOOKUP rows based on True/False

    Quote Originally Posted by wk9128 View Post
    Inspection worksheet , A2:F2 cell array formula , Drag down
    Thank you, that does exactly what I want.... Now I have to read and reread it and figure out why and how it works :-)

  8. #8
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    China Shanghai
    MS-Off Ver
    365 V2402 and WPS V2022
    Posts
    3,327

    Re: VLOOKUP rows based on True/False

    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'.

    If you don’t understand the formula #6, you can use this formula instead

    A2 cell , array formula
    HTML Code: 
    Last edited by wk9128; 10-01-2020 at 02:06 AM.

+ 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. Replies: 1
    Last Post: 06-26-2019, 09:25 AM
  2. VLookup True or False
    By maxhecht2 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-31-2017, 02:04 AM
  3. VLOOKUP true or false by #na
    By jagadeesh.rt in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-18-2015, 03:51 AM
  4. [SOLVED] Vlookup with True/False
    By garyi in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 10-07-2013, 10:17 AM
  5. Hide Show rows based on cell value true or false using VBA
    By gopal904 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-12-2013, 07:31 AM
  6. Deleting rows based on True/False Criteria
    By CatherineN in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-15-2007, 02:40 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