How to save time updating cost prices

  • Thread starter Thread starter Kevin J Prince
  • Start date Start date
K

Kevin J Prince

Help please

I have a Sheet with some 600+ stock items on it

SHEET1
Stock Ref SuppRef Desc Note Selling Supplier Cost
Price
Price
AA999B VP2ER4 Widget11 abc123 0.84 Company1 1.40
AB999C VP2RR7 Widget13 rst33j 1.25 Company2 2.65

SHEET2
Stocktype percentpay Note
Largewidgets 73 Last updated 30/08/04
Smallwidgets 79 Last updated 30/08/04

the stocktype column A is transfered so that column B has a label
equating to the name in column A.

The way my stock pricing works is as follows ref above

The cost price in the cost column G
The selling price is a formulae e.g. =G35/100*Largewidgets

What I need to do is increase company1's prices by 2.56% without going
through each cell, how can I do this enbloc for Company1?

I would then produce a listing for shop use.

Hope it might make some sense???

Regards
 
Assuming that the data start at cell a1 and the cost in in
column g, you can put this formula in a helper column say
column i.
=if(g2= "company1",G2*.0256,g2)
then copy down the formula to the end of the data.
if column g contain company 1, it will do the math or else
just bring the value of g2 over. you can then copy the i
column and paste as values in column g. paste as values
will turn the formula in to hard numbers.
 
First of all, many thanks for the reply ....

Slight changes to what you said, give me the correct value. So a good
hint

=IF(F2="Company1",G2*0.0256+G2)

This gives me the new value, however if the supplier is company2 I get
FALSE in the column. This would also be time consuming grabbing the
Company1 prices and moving them back to column G individually.

I suppose I could sort on the supplier column, do the changes then
resort on stock references....?

What I was really trying to do was automate it slightly.

E.g. when company1 comes along with a 2.3% increase in all it's prices,
I could enter the company name somewhere, and the percentage increase or
decrease (should the gods take pity), press a key and hey-presto all the
values for company1 are updated.

Is this possible?


Regards
Kevin
 
OK OK Stupid me,,,,,,,

I forgot the ,G2 at the end, sorted now I think

=IF(F2="Company1",G2*0.055+G2,G2)

Thanks again, learnt something today, which is what I should hope to do
everyday....

Regards Kevin
 
Back
Top