Hi

I'd wonder if anyone can help. I've put my situation in to simple terms
below .......

I have a column in worksheet 1 which tells me how many oranges I have in
stock. When an order is presented, I enter the details on worksheet 2. Is
there anyway I can enter a formula to check worksheet 1, minus the number of
oranges ordered in worksheet two, and enter the value somewhere in worksheet
2? I'm thinking this would be a good way of telling me when I am low on stock.

Thanks all - used some info on this site before - really brilliant!

This is a very simplistic response, but here goes.

Assume your inventory is in a 2 column range named Inventory. Left column
contains Oranges, Bananas, etc, and the right column contains the # on hand.

In another worksheet you have sales in let's say 4 columns: Date, Customer,
Product, Quantity. These are in columns A thru D. Now you have another
range of 2 columns for remaining inventory, and the left column contains
Oranges, Bananas, etc. Assume this range is in columns F & G.

With "Oranges" in F1, this is the formula you would use in G1.

=VLOOKUP(F1,Inventory,2,0)-SUMIF(B1:B100,F1,D1:D100)

This formula takes the beginning inventory for oranges and subtracts all the
oranges sold. It will update as you add new sales of oranges.

"Billjary" wrote:

Hi

I'd wonder if anyone can help. I've put my situation in to simple terms
below .......

I have a column in worksheet 1 which tells me how many oranges I have in
stock. When an order is presented, I enter the details on worksheet 2. Is
there anyway I can enter a formula to check worksheet 1, minus the number of
oranges ordered in worksheet two, and enter the value somewhere in worksheet
2? I'm thinking this would be a good way of telling me when I am low on stock.

Thanks all - used some info on this site before - really brilliant!

Hi

If your orders are in column C on sheet2 and your stock is in A2 on sheet1,
this:
=sheet1!A2-SUM(sheet2!C:C)
should give you the stock less the sales.

Andy.

"Billjary" wrote in message
...
Hi

I'd wonder if anyone can help. I've put my situation in to simple terms
below .......

I have a column in worksheet 1 which tells me how many oranges I have in
stock. When an order is presented, I enter the details on worksheet 2. Is
there anyway I can enter a formula to check worksheet 1, minus the number
of
oranges ordered in worksheet two, and enter the value somewhere in
worksheet
2? I'm thinking this would be a good way of telling me when I am low on
stock.

Thanks all - used some info on this site before - really brilliant!

Thanks for the help so far guys but I think I missed a few areas off that
might mean these functions don't work. I have the following on a worksheet
called Assetts :

Description Prod No Purchase Price List Price Qty

I then have a sales tab with :

Product ID Date Sold Qty Sale Price Customer ID Price Paid Assetts now

I'd like, when I enter a product ID (number format), the sheet to
automatically check the Qty value of this prodcut ID, then take off the QTY
in the sales worksheet and enter the value in the Assetts now column.

Sorry if I was cryptic with the "oranges" but it sounded ok in my head!!! Am
I asking too much?

THanks for all your help so .


"Andy" wrote:

Hi

If your orders are in column C on sheet2 and your stock is in A2 on sheet1,
this:
=sheet1!A2-SUM(sheet2!C:C)
should give you the stock less the sales.

Andy.

"Billjary" wrote in message
...
Hi

I'd wonder if anyone can help. I've put my situation in to simple terms
below .......

I have a column in worksheet 1 which tells me how many oranges I have in
stock. When an order is presented, I enter the details on worksheet 2. Is
there anyway I can enter a formula to check worksheet 1, minus the number
of
oranges ordered in worksheet two, and enter the value somewhere in
worksheet
2? I'm thinking this would be a good way of telling me when I am low on
stock.

Thanks all - used some info on this site before - really brilliant!

The solution I gave you will do that, except:
1) Define the name Inventory to start with Prod No and include Qty
2) put this revised formula in the Assets Now column
=VLOOKUP(A1,Inventory,4,0)-SUMIF(A$1:A$100,A1,C$1:C$100)
(this assumes that the layout you've provided starts in column A)



"Billjary" wrote:

Thanks for the help so far guys but I think I missed a few areas off that
might mean these functions don't work. I have the following on a worksheet
called Assetts :

Description Prod No Purchase Price List Price Qty

I then have a sales tab with :

Product ID Date Sold Qty Sale Price Customer ID Price Paid Assetts now

I'd like, when I enter a product ID (number format), the sheet to
automatically check the Qty value of this prodcut ID, then take off the QTY
in the sales worksheet and enter the value in the Assetts now column.

Sorry if I was cryptic with the "oranges" but it sounded ok in my head!!! Am
I asking too much?

THanks for all your help so .


"Andy" wrote:

Hi

If your orders are in column C on sheet2 and your stock is in A2 on sheet1,
this:
=sheet1!A2-SUM(sheet2!C:C)
should give you the stock less the sales.

Andy.

"Billjary" wrote in message
...
Hi

I'd wonder if anyone can help. I've put my situation in to simple terms
below .......

I have a column in worksheet 1 which tells me how many oranges I have in
stock. When an order is presented, I enter the details on worksheet 2. Is
there anyway I can enter a formula to check worksheet 1, minus the number
of
oranges ordered in worksheet two, and enter the value somewhere in
worksheet
2? I'm thinking this would be a good way of telling me when I am low on
stock.

Thanks all - used some info on this site before - really brilliant!

Duke

This is blowing me away!!

I've got most of what you said but missing out on point 1. The Tab that I
need to enter the assetts Now (this formula) is called Sales. The tab that
holds the starting info is called Assetts (prod no is in Colum D and Qty in
Col G), so should I replace Inventory with Assetts? Where do I enter Prod No?

In the sales tab, the layout is A-H(exactly) :

Product ID Date Sold Quantity Req Sale Price Customer ID Price Paid
Dif Assetts Now

Sorry again!!!!


"Duke Carey" wrote:

The solution I gave you will do that, except:
1) Define the name Inventory to start with Prod No and include Qty
2) put this revised formula in the Assets Now column
=VLOOKUP(A1,Inventory,4,0)-SUMIF(A$1:A$100,A1,C$1:C$100)
(this assumes that the layout you've provided starts in column A)



"Billjary" wrote:

Thanks for the help so far guys but I think I missed a few areas off that
might mean these functions don't work. I have the following on a worksheet
called Assetts :

Description Prod No Purchase Price List Price Qty

I then have a sales tab with :

Product ID Date Sold Qty Sale Price Customer ID Price Paid Assetts now

I'd like, when I enter a product ID (number format), the sheet to
automatically check the Qty value of this prodcut ID, then take off the QTY
in the sales worksheet and enter the value in the Assetts now column.

Sorry if I was cryptic with the "oranges" but it sounded ok in my head!!! Am
I asking too much?

THanks for all your help so .


"Andy" wrote:

Hi

If your orders are in column C on sheet2 and your stock is in A2 on sheet1,
this:
=sheet1!A2-SUM(sheet2!C:C)
should give you the stock less the sales.

Andy.

"Billjary" wrote in message
...
Hi

I'd wonder if anyone can help. I've put my situation in to simple terms
below .......

I have a column in worksheet 1 which tells me how many oranges I have in
stock. When an order is presented, I enter the details on worksheet 2. Is
there anyway I can enter a formula to check worksheet 1, minus the number
of
oranges ordered in worksheet two, and enter the value somewhere in
worksheet
2? I'm thinking this would be a good way of telling me when I am low on
stock.

Thanks all - used some info on this site before - really brilliant!