We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

A TALKING MACRO OR FORMULA....

Medium Priority
451 Views
Last Modified: 2013-11-26
Hello,

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;
Roberto.  
talking-formula.xls
Comment
Watch Question

Try this

Ritesh
talking-formula.xls

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
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")))
Regards,
Roberto.

Author

Commented:
Sorry the formula posted was in Spanish...
SI = IF
SUMAR:SI = SUM.IF
Roberto.
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.

Ritesh
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.