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 5

In Each of these other sheets will be (A1) RMA number (B1) Carton number (C1) Part number (D1) Serial number

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 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.

Cheers

Andy



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

Report •

#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.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


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
✔ Best Answer
I'm glad you found something that works, but I have a few comments/suggestions:

First, by using F:G as your lookup_table , you are asking VLOOKUP to search the entire Column F - all 1,048,576 rows. It might be more efficient to only check the maximum number rows that you think you'll ever need. Even more efficient would be to use a Dynamic Named Range which can adjust itself to the actual size of the data.

Second, when you use IFERROR, you will get that "0" for any error that the VLOOKUP returns. Typically IF(ISNA(VLOOKUP(...) is used when you want to deal with a VLOOKUP that didn't find what it was looking for. That way, if some other error occurs, such as a #VALUE, #REF, etc., you'll know about it. IFERROR would essentially mask those errors and return "0", never letting you know that an actual error exists.

Finally, when you put quotes around a number, e.g. "0", Excel will return the text string 0, not the number 0. In most cases this won't matter, but there are certain functions that will deal with a "text number" differently than an actual number.

Drop the quotes and you'll get the number 0, which is what I believe you are looking for.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
February 11, 2014 at 00:43:22
Thankyou Derby. Your help is much appreciated :)

Report •

Ask Question