Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 440
  • Last Modified:

A TALKING MACRO OR FORMULA....

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
0
Pabilio
Asked:
Pabilio
  • 2
  • 2
1 Solution
 
riteshparakhCommented:
Try this

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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now