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.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,553
Messages
5,838,075
Members
430,527
Latest member
MyFace2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top