-1

I am working on an inventory management list where barcodes are scanned out and scanned back in. My sheet is attached to forms. I have set the item sku# on the main inventory list to highlight red when checked out and pull the location of the item from the checked out list over to the inventory sheet (column L), but I am struggling to make it show when an item is checked back in. Ideally I would like the item sku# to highlight red as shown when it is checked out and have the location show in yellow when checked out, but I need the item sku# to go back to no color when checked in and remove the item location from the main inventory list when checked in. I attempted using a formula I found on the status tab, but it is not showing when items are scanned multiple times. Is there a way to code this so that it does it with each scan in/out? I have tried using several formulas, but I am not all that great with excel. any help is greatlyenter link description here appreciated.

7
  • Hi and welcome. Your spreadsheet requires access permission; would you please change this allow access by anyone (if it contains private information, remove it and substitute dummy transactions). It seems that you have been able to have the SKU display in RED when scanned out; why not apply the same logic to making the location display in yellow. As " it is not showing when items are scanned multiple times", would you explain EXACTLY what this means and why it would be a problem. Lastly, you mention Excel but have tagged Google Sheets -which are you using for this task?
    – Tedinoz
    Sep 13 at 19:35
  • sorry about that! Here you go: docs.google.com/spreadsheets/d/… Sep 13 at 20:08
  • I have tried to use the same logic however I get an error. Once an item is checked out, it shows, but once I scan in I cannot get it to do the same function because it is being overridden by my original formula. I was also having an issue with the multiple scan function. It will only show when the sku is initially scanned, but will not take into account multiple scans. These items will be checked out and checked in regularly so it will need to be able to reach multiple scans. Sep 13 at 20:10
  • 1
    Is this dummy data? It looks remarkably detailed and includes data that I would regards as confidential??
    – Tedinoz
    Sep 13 at 20:20
  • 1
    Pls edit your question. You have put too much into comments, while all the info must stay in the question.
    – ZygD
    Sep 14 at 4:06
0

You have an inventory sheet showing relevant standing data including the unique SKU; you also want to record the location of each item, and format the SKU and Location fields depending on the status of the item. Issues In and Out are recorded on Google Forms, and details of those responses are included on other sheets in the same spreadsheet.

Use this formula in cell L3 on "INVENTORY"

=arrayformula(iferror(vlookup(J3:J,query({query({CHECKEDIN!A2:D},"select Col1, Col4,'IN' where Col1 is not null order by Col1 desc LABEL 'IN' '' ");query({CHECKEDOUT!A2:E}," select Col1,Col4, Col5 where Col1 is not null order by Col1 desc ")},"Select Col2, Col3 order by Col1 desc"),2,0),""))

Conditional formatting - Column L (Location)

  • Custom formula
  • =NOT(or(J3="IN",len(J3)=0))

Conditional; formatting = Column K (SKU)

  • repeat the same custom formula but chnage the background colour to suit.

Logic

  • Combine the records of CheckedOUT and CheckedIN
  • Add a column to "CheckedIN" so that it has the same number of columns as Checked Out
  • Sort the results by date desc
  • VLOOKUP the SKU in the results of the query and return the location.

Sample checkedIN Note: the transaction highlighted in Yellow.

IN


Sample checkedOUT Note: the extra transaction highlighted in Yellow

Out


Inventory (conditional formatted) Inventory

9
  • I cant thank you enough! I think this is exactly what I need. I am attempting it, but I am getting an error. My data is not translating like yours did. I know according to the formula it should be listed as 'in". Any ideas? The conditional formatting is not pulling properly. Also, do I need to keep my original conditional format on the SKU column like I had and should I keep my same vlookup on the location column Sep 15 at 23:42
  • Formula is =arrayformula(iferror(vlookup(N3:N,query({query({'CHECKED IN'!A2:D},"select Col1, Col4,'IN' where Col1 is not null order by Col1 desc LABEL 'IN' '' ");query({'CHECKED OUT'!A2:E}," select Col1,Col4, Col5 where Col1 is not null order by Col1 desc ")},"Select Col2, Col3 order by Col1 desc"),2,0),"")). 3 differences 1)CHECKED IN has space in the sheet name; 2) ditto CHECKED OUT; 3) the CheckedOut query is combined with the CheckIn query - this is done by separating them with a ; (semi-colon) - this is the specific section: ;query({'CHECKED OUT'(you have a comma here).
    – Tedinoz
    Sep 16 at 5:22
  • Conditional formatting: "Project Use"-the first row of the range must align with the custom formula. You had P1:P50000+, but the custom formula references row#3. Suggest you alter the range to be P3:P50000+.
    – Tedinoz
    Sep 16 at 5:38
  • It looks like this is working! You are amazing. I cant thank you enough. Sep 16 at 5:39
  • Conditional formatting: "SKU" - 1) change the range to start at row #3 (as above). 2) the custom formula needs to reference the "Project Use" column
    – Tedinoz
    Sep 16 at 5:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.