0

I am working warehouse and I have to tick and cross on the paper if the item is back or not. but I want to make it easy using Excel.

I manage to create a function where if Search cell and the tracking ID is matched and then display the text word "HERE!" or highlight but when I change the incorrect tracking ID in the search cell, the word "HERE!" will disappear. What I want to do is, How do I add new text Yes and freeze whenever the word appears "HERE!" right next to it, If I change the tracking ID in the search, Here! text will be in a different cell, but Yes text should be in the same cell and not disappear whenever the word "HERE!" disappear.

Please see screenshot

MY EXCEL WORKSHEET

As you can see, Cell A1 is a search, I enter A3 cell function =IF(C3 = $A$1, "HERE!","") and autofill all the way to down, this method is only Appear word HERE! whenever the search cell and the tracking ID are matched. The word "Here!" will disappear in whenever the Search cell is blank. The main thing I want is, In Cell B column, I want to automatically Add new text "Yes" and freeze the text after Word "Here!" appear even if the search cell is empty after typing tracking ID.

Anyone give me a solution would be great, I only want to use the function, not Visual Studio or any code, because I can edit on a smartphone.

Rubén
  • 34,714
  • 9
  • 70
  • 166
K G
  • 1
  • 2
  • A formula is not a value, but a calculation to the value, therefore if the value refered to changes it will give a different outcome. You can't do this without VBA. – P.b Sep 07 '22 at 11:36
  • 1
    Welcome to S.O, as @P.b said, you cannot retain a value if the formula it relies on changes. From what I think you want (it looks like an 'in stock' inventory list), I would add another sheet with 2 columns, In, Out and use XLOOKUP to identify what is in the warehouse and what is out of the warehouse. – GoodJuJu Sep 07 '22 at 13:19
  • 1
    Adding to my previous comment, you cannot retain the value if the formula changes, but perhaps the 'AND' function will help you... Copy into B3 and paste down... =IF(AND(XLOOKUP($A$1,C3,C3,"",0)<>"",$A$1<>""),"yes","") – GoodJuJu Sep 07 '22 at 13:24
  • Thank you guy for reply. I understand now, that's sad. Is it possible to use vba on a computer, save it and then use it smartphone? if that work then I would copy cell from my manager and paste that have VBA in a smartphone. I cant take laptop in the warehouse but I can use smartphone. – K G Sep 08 '22 at 09:03
  • @GoodJuJu, your method is worked, but I only need to keep yes in the same cell as I change the tracking ID. But I understand you – K G Sep 08 '22 at 09:06
  • @P.b, I understand now, but can I do VBA in excel on the computer and do what I wanted, then save it and open in smartphone that VBA just created, this way I would copy whole cell and pasted into excel that just created VBA? – K G Sep 08 '22 at 09:10
  • No, VBA simply doesn't work on mobile Excel (maybe if you have a Windows mobile). – P.b Sep 08 '22 at 15:27
  • @P.b, hey, it the same question above where display yes freeze after here! display on the screen. Do you know how to run as vba? I want to give a try. Thank a lots – K G Sep 12 '22 at 10:40

0 Answers0