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

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
quite simply just reference (on sheet2) the same cell on sheet1.

For example, Sheet2, cell A1, the formula is:
=Sheet1!A1
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
sorry forgot that
=int(formula)

regarading color I shall try to solve as I have not fully understood.
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,216,578
Messages
6,131,516
Members
449,654
Latest member
andz

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
Back
Top