Posted on 2009-02-13
Last Modified: 2013-11-26

My situation is this:

I have an Excel Spreadshet with two sheets...the first one is populated with invoices and the contents of this purchases (Items codes) Sheet 2 have my inventory separated in ready Items and Raw Items (need finishing before delivery)

The sheet goes like this:

Sheet1: (Invoice Details)
Column A: Invoice Number
Column B: Date of Purchase
Column C: Customer Name
Column D: Item Code
Column E: Qty Purchased.
Column F: (Here come´s the formula I need)

Sheet2: (Items information)
Column A: Item Code
Column B: TOTAL Item Inventory (ready + raw items)
Column D: Items Ready to load
Column E: Raw Items (Need Finishing before be loaded)

What I need:

In column F on Sheet 1 I need a Macro or a formula to display the STATUS situation on each Invoice product based on the ITEMS RAWS, ITEMS READY or No available ITEMS....and the Criteria to asign the product ready comes from the Date of purchase.

If Posible, The Macro/Formula should display in column F on sheet 1 the action to be taken i.e:
"To Assemble" (When the total of purchased items is bigger than Ready Items but minor than Ready + Raws
"To Order" (When the total of purchased is bigger than Ready and Raws available.

The solution is probably comes from a combination of Counts, If and something else...

I appreciate very much any help or directions.

Best regards;
Question by:Pabilio
    LVL 10

    Accepted Solution

    Try this

    LVL 5

    Author Closing Comment

    Thank you Ritesh,
    That´s exactly what I was looking for.
    Just if anyone is interested I added an extra IF condition in case there is not value in the next cell.
    The final formula is: =SI(A10=0;"";SI(SUMAR.SI($D$2:D10;D10;$E$2:E10)<=SUMAR.SI(Hoja2!$A$2:$A$4;Hoja1!D10;Hoja2!$C$2:$C$4);"Ready";SI(SUMAR.SI($D$2:D10;D10;$E$2:E10)<=SUMAR.SI(Hoja2!$A$2:$A$4;Hoja1!D10;Hoja2!$D$2:$D$4);"Raw";"To Order")))
    LVL 5

    Author Comment

    Sorry the formula posted was in Spanish...
    SI = IF
    LVL 10

    Expert Comment

    Hi Roberto,

    You are welcome any time.
    Good to hear that you extended the formula yourself that is what is expected ... since you have extended it, i understand that you have understood it.
    Its always better to do things once you understand them.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
    When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now