HELP W/ FORMULA FOR PRICE LIST

[lucrosus]

Good day to everyone.

Please bear with me.

I have 2 sheets. First sheet, I use for putting down all items and prices of my supplier. This is how it looks.

COL-A COL-B COL-C COL-D COL-E COL-F

COL-A : Item Description
COL-B : Item Size
COL-C : Item Weight

All of the above remains constant.

COL-D : Supplier Price
COL-E : Supplier Price
COL-F : Supplier Price

Columns D to F are prices of 3 models of items.

Now, in sheet no. 2, I have the exact same layout. I wanted to make it so that the prices in sheet no. 2 will change automatically when I change the price in sheet no. 1.

The formula looks something like this:

sheet no. 1 price + 15 + 5%, round off to the next higher integer. If background color of price in sheet no. 1 is grey (any background color other than the default white), the formula is:

sheet no. 1 price + 25 + 5%, round off to the next higher integer.

Hope this is clear enough.

Thanks a lot.

 

[nbrcrunch]

quite simply just reference (on sheet2) the same cell on sheet1.

For example, Sheet2, cell A1, the formula is:
=Sheet1!A1

 

[venkat1926]

In sheet2 D2 type this formula

=Sheet1!D2+15+5%*Sheet1!D2

copy this formula in sheet2 E2 and F2
if necessary you can also copy down the columns D,E and F.

see wheher the formula is correct if not make slight changes

now if you change any number(or introduce a number) in D2 in sheet1 it will make corresponding changes in D2 of sheet 2. similarly for E2 and F2 etc.

 

[lucrosus]

Thanks for the replies.

But what about the background color? In sheet 1, basically, I have 2 kinds of prices. one that I need to add 15 and another, 25, before adding the 5% markup.

Although this should not be too much of a concern... there is, of course, a workaround.

How about integrating the formula to round off to the next higher integer?

Thanks again.

 

[venkat1926]

sorry forgot that
=int(formula)

regarading color I shall try to solve as I have not fully understood.

 

[lucrosus]

Follow-up question:

I used the roundup function successfully.

In sheet 1, there are some cells that do not have amounts entered. Using the formula, what happens is that empty cells are treated as zero.

What I want is, if the cell from sheet 1 is empty, then the cells in sheet 2 will become empty too.

How to do that?

Thanks!

 

[venkat1926]

you have to use an IF function

something like this
=if(int(formula)="","",int(formula))

this means if int(formula) is nothing you make the cell blank
other wise (i.e. if(int(formula)) is something enter that.

modify to suit you.