# Solved Excel 07, Formula to minus from a qty between sheets

February 6, 2014 at 07:56:32
Specs: Windows 7
 Hi, hope someone can help here.I want to create a spreadsheet and I am a pretty basic user.In Sheet1 will have the following as headers and all the info underneath - (A1) Part number (B1) Item Description (C1) System QTY, In Columns D,E,F and G will be information which I want to read off Sheet2, 3, 4 and 5In Each of these other sheets will be (A1) RMA number (B1) Carton number (C1) Part number (D1) Serial numberI need a formula on Sheet1 (D1) to count the items in sheet2 and deduct the Qty from the (C1) System Qty in sheet1.I also need something that will tell me if a part number is not available in the System Qty on sheet1 when I have the parts in the other sheets.Its a bit hectic to explain. hope someone understands me.CheersAndy

See More: Excel 07, Formula to minus from a qty between sheets

#1
February 7, 2014 at 07:23:32
 re: "Its a bit hectic to explain"I agree. I'm not quite sure what you are asking for.In one sentence you say..."In Each of these other sheets will be (A1) RMA number (B1) Carton number (C1) Part number (D1) Serial number"...and then you say:"I need a formula on Sheet1 (D1) to count the items in sheet2 and deduct the Qty from the (C1) System Qty in sheet1."I don't see where you have any quantities on Sheet2 to use for the calculation.re: "I also need something that will tell me if a part number is not available in the System Qty on sheet1 when I have the parts in the other sheets."I'm not sure what you mean by "tell me". Are you asking for some type of pop-up? If so, when would you want that pop-up to appear? Are you asking for a formula? If so, where do you want that formula?It might help if you posted some example data and explained what you are trying to do based on that data. Please read the instructions found by clicking on the following line before posting any example data.

Report •

#2
February 7, 2014 at 08:30:02
 Hi Derby. Thanks for your reply.Re: I don't see where you have any quantities on Sheet2 to use for the calculation.I scan the serial number of goods into another speadsheet when they come back which gives me a list of part numbers and serial numbers which i pull into sheet2.Managed to create a couple formulas which grabs the qty from a pivot table of the items in sheet 2 and flag errors as yellow=IFERROR(VLOOKUP(A3,'Sheet2'!F:G,2,FALSE),"0")=VLOOKUP(F1,TOTALS!A:A,1,FALSE)Seems to work.Thanks for answering though

Report •

#3
February 7, 2014 at 13:26:40