Results 1 to 5 of 5

Thread: Need help: cell should switch to "Y" when A1 matches with column K, to "N" when not

  1. #1
    Acolyte Brecht's Avatar
    Join Date
    Sep 2014
    Location
    Netherlands
    Posts
    31
    Articles
    0

    Need help: cell should switch to "Y" when A1 matches with column K, to "N" when not



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,

    I am working on an excelsheet that, via the import-function of our webshop, automatically hides the items on our webshop that are not in stock with our supplier. I would combine our own export of products (columns A-I) with their product listings (one for not in stock (columns K-T), and one for in stock (columns V-AE).

    Now what I am searching for is a formula that does the following:
    If the data in cell G1*, occurs exactly the same somewhere in column K,** cell I1 should switch to "Y". If the data in cell G1 occurs somewhere in column V,*** cell I1 should switch to "N".

    * which is the article code corresponding with the code of our supplier
    ** which lists the article codes which are not available at our supplier.
    *** which lists the article codes which are available at our supplier.

    ***

    Or, what would probably work as well, if I only add one of the product listings.
    In that case, the formula would need to say:
    If the data in cell G1, occurs exactly the same somewhere in column K, cell I1 should switch to "Y". If it doesn't occur in column K, cell I1 should switch to "N".

    Your help would be much appreciated!

  2. #2
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    766
    Articles
    0
    Excel Version
    2010
    Try this in Cell G1:

    =IF(COUNTIF( K:K,G1)>0, "Y", IF(COUNTIF(V:V,G1)>0, "N",""))

    This includes all of columns K and V. You made need to use precise ranges to fit your stock list if these columns have other unrelated data in them.

  3. #3
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    766
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by Hercules1946 View Post
    Try this in Cell G1:

    =IF(COUNTIF( K:K,G1)>0, "Y", IF(COUNTIF(V:V,G1)>0, "N",""))

    This includes all of columns K and V. You made need to use precise ranges to fit your stock list if these columns have other unrelated data in them.
    Sorry - I should have said enter the formula in I1, not G1.
    Last edited by Hercules1946; 2014-10-21 at 01:20 PM.

  4. #4
    Acolyte Brecht's Avatar
    Join Date
    Sep 2014
    Location
    Netherlands
    Posts
    31
    Articles
    0
    Quote Originally Posted by Hercules1946 View Post
    =IF(COUNTIF( K:K,G1)>0, "Y", IF(COUNTIF(V:V,G1)>0, "N",""))
    That is exactly what I was looking for! Thank you so much

  5. #5
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    766
    Articles
    0
    Excel Version
    2010
    Glad to help, and thanks for letting me know

Posting Permissions

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