# HELP W/ FORMULA FOR PRICE LIST

#### lucrosus

##### New Member
Good day to everyone.

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

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.

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.

sorry forgot that
=int(formula)

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

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!

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.

Replies
9
Views
304
Replies
5
Views
299
Replies
24
Views
776
Replies
5
Views
84
Replies
11
Views
468

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.

### Which adblocker are you using?

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

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