Results 1 to 10 of 10

Thread: Help needed: cell should switch to Y or N, but formula doesn't work

  1. #1
    Acolyte Brecht's Avatar
    Join Date
    Sep 2014
    Location
    Netherlands
    Posts
    33
    Articles
    0
    Excel Version
    Version 2007

    Help needed: cell should switch to Y or N, but formula doesn't work



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

    Hi all,

    similar one of my last topics (http://www.excelguru.ca/forums/showt...of-other-cells ), 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-H) with the inventory of the supplier (columns L-P). Unfortunately the layout of the inventory of this supplier is different than the last.

    Now what I am searching for is a formula that does the following:
    If the value of G2 is found in column L, and the corresponding cell O* reads "0", cell I2 (where the code should be entered) should read "Y". If G2 is found in column L, and the corresponding cell O reads "1", or a value above 1, cell I2 should read "N".

    *so if the value of G2 is found in cell L2, the corresponding cell O is O2

    I made the following formula, based on the formula you kindly helped me with in my last topic:
    IFERROR(IF(INDEX(O:O;MATCH(F3;K:K;0))=0;"Y";"N");"")

    but this formula works only partly, for it also puts a Y in cells that have a corresponding value higher than 0. What am I doing wrong?

    Again, thank you very much for your help, excel-wizards :-)

    Best wishes,

    Brecht

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    That formula should only work if the value in column O where the match occurs in K is exactly equal to 0.

    Can you post a sample workbook showing that it is not doing this?


  3. #3
    Acolyte Brecht's Avatar
    Join Date
    Sep 2014
    Location
    Netherlands
    Posts
    33
    Articles
    0
    Excel Version
    Version 2007
    Hi (again) NBVC,

    I believe this should work:

    Example sheet 2.xlsx

    Best wishes,

    Brecht

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    Your values in column K are actually formatted as text, this is why you are getting an error for the match, and because your IFERROR() says to return a "Y" if an error occurs, you are getting all "Y's".

    If you intend to leave column K as is, then you need to adjust the formula to accomodate and convert the column F value in situ:

    =IFERROR(IF(INDEX(O:O;MATCH(""&F2;K:K;0))=0;"Y";"N");"Y")

    or you can convert column O to numeric, by selecting the column, going to Data|Text to Columns and simply clicking Finish.


  5. #5
    Acolyte Brecht's Avatar
    Join Date
    Sep 2014
    Location
    Netherlands
    Posts
    33
    Articles
    0
    Excel Version
    Version 2007
    Thank you for the reply!
    I believe I have to keep K as standard, because otherwise excel will insert a . to mark a thousand, whereas in this document the . is used to distinguish between the main article number and the product option.
    I adjusted the formula and it works now! Thank you so much again!

  6. #6
    Acolyte Brecht's Avatar
    Join Date
    Sep 2014
    Location
    Netherlands
    Posts
    33
    Articles
    0
    Excel Version
    Version 2007
    Hi,

    sorry for troubling you again on this question. I thought the formula worked, but it appears it doesn't make an exact match. So for example, when I have an article number 572, and there is an article number 1572 in column L, it gets the status of this cell, instead of the status matching with the cell containing 572. I hope I explain it clearly.
    What did I do wrong?

  7. #7
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    Can you show a sample workbook with the problem?


  8. #8
    Acolyte Brecht's Avatar
    Join Date
    Sep 2014
    Location
    Netherlands
    Posts
    33
    Articles
    0
    Excel Version
    Version 2007
    example sheet 3.xls

    Here you are.
    Cell F2 should have an exact match in cell K17, directing us to cell O17, and setting cell I2 to "N", but this doesn't happen.
    I checked whether cell I2 would switch to "N" if you'd place a number higher than 0 in O2 (and the following cells), but that doesn't work either.

  9. #9
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    As I had mentioned before, your column K and your F2 entry are not consistent as far as format. The 1122 in K17 is entered as a number, but the formula was written to accomodate column K being entered as text.

    If you format column K as TEXT, then re-enter the 1122 in K17, you should see the "Y" in I2.


  10. #10
    Acolyte Brecht's Avatar
    Join Date
    Sep 2014
    Location
    Netherlands
    Posts
    33
    Articles
    0
    Excel Version
    Version 2007
    I am sorry, I misread your original instructions. Now it does work alright.
    Thank you for your patience & guidance.

Posting Permissions

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