trying to create a check out sheet to add inventory quantity to inventory sheet


im looking into trying to make a checkout sheet that hold serial numebrs of products to an inventory sheet and to add those serial numbers, also to locate duplicates and delete the duplicates serial number found.


this is the sheet we will be taking orders and using row serial number to either add or subtract from our inventory.


Checkout sheet: and the bottom sheet is the inventory we would want to remove the serial number when an order is placed.


Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @BryanOramas

    I have to admit I'm not quite sure how to do this with your current set-up. What I would personally do here is configure the Inventory Tracker a little differently.

    • Instead of multiple columns, use ONE Primary Column with Parent/Child relationships
    • Parent rows are your current column names, child rows are the numbers
    • This means in another column you can use a formula to check a box if the number is used in a different sheet
    • =IF(COUNTIF({Column Reference}, [Primary Column]@row) > 0, 1, 0)
    • Then set up Conditional Formatting to cross out a value if the box is checked

    Does that make sense?

    Then for your other sheet, I would create a Report from that sheet and Group it by the number to see if there are any duplicates. You could also use a formula to flag a row if there's a duplicate:

    =IF(COUNTIF([INC #/ PO]:[INC #/ PO], [INC #/ PO]@row) > 1, 1, 0)

    Or you could just simply count and then return the count. If it's over 1, you can use Conditional Formatting to highlight the rows.

    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Answers

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @BryanOramas

    I have to admit I'm not quite sure how to do this with your current set-up. What I would personally do here is configure the Inventory Tracker a little differently.

    • Instead of multiple columns, use ONE Primary Column with Parent/Child relationships
    • Parent rows are your current column names, child rows are the numbers
    • This means in another column you can use a formula to check a box if the number is used in a different sheet
    • =IF(COUNTIF({Column Reference}, [Primary Column]@row) > 0, 1, 0)
    • Then set up Conditional Formatting to cross out a value if the box is checked

    Does that make sense?

    Then for your other sheet, I would create a Report from that sheet and Group it by the number to see if there are any duplicates. You could also use a formula to flag a row if there's a duplicate:

    =IF(COUNTIF([INC #/ PO]:[INC #/ PO], [INC #/ PO]@row) > 1, 1, 0)

    Or you could just simply count and then return the count. If it's over 1, you can use Conditional Formatting to highlight the rows.

    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions