HELP W/ FORMULA FOR PRICE LIST

lucrosus

New Member
Joined
May 8, 2004
Messages
47
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.
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

nbrcrunch

Well-known Member
Joined
Jan 1, 2003
Messages
2,062
quite simply just reference (on sheet2) the same cell on sheet1.

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

venkat1926

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
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

New Member
Joined
May 8, 2004
Messages
47
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

Well-known Member
Joined
Aug 21, 2005
Messages
4,824

ADVERTISEMENT

sorry forgot that
=int(formula)

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

lucrosus

New Member
Joined
May 8, 2004
Messages
47
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

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,117
Messages
5,570,278
Members
412,316
Latest member
JabirS
Top