Excel question
 

Hi

This is probably a little OT but I'm trying to do a new price list and am wondering if is there anyway that you can have two cells interdependant on eachother....

ie I have a cell called "Sale Price" and another called "Mark-up". Currently prices are done off Markup, (Sale Price= Markup*Cost Price). Is there anyway that you can either change "Sale Price" or "Markup" and the other will change, so I could either set the mark up or the sale price and the other would fill?

Thanks

That is called a circular reference which Excel won't allow.

Ahh I had a feeling I was out of luck...No real way around it either.

Have you considered whether using conditionals ("IF") might work for you? Take a look at the IF function in the help menu and see if you might be able to use it for your situtation.

I have but I would still end up with a circular ref, wouldn't I? Unless I created a third point....I'll have to investigate.

Try having 2 sets of the 3 columns (Sale price, Markup, and Cost). One set is your data entry set and the other would have IF formulas. You should then be able to enter any 2 of the 3 and have the 3rd calculated, with the other 2 carried over.

Thats done the trick thanks. Is there anyway now I can get the numbers in the data entry set to update to the data in the IF formulas as in if i enter say m/up and cost and it fills the fields in the "IF formulas columns" can I get it to reverse back and fill the blank sale price cell in the data entry set or is this circular???

thanks for all you help everyone:)

Check out http://www.mrexcel.com/board2/

I've gotten a lot of questions answered there.

Our pleasure -- just pass the favor on to someone else when you can be of help.

There's a discussion board for everything...

Well, there is kind of a way to do it with just the three columns (Sale Price, Markup and Cost).

A formula will give you the circular reference, but you can use VBA code to get around that.

if you were to write a macro on the worksheet, under the Change event you can do it. For example, the data you are trying to manipulate is on a sheet called Margin which is the first sheet in the workbook.

If you open the VBA editor (F11) then double click on Microsoft Excel Objects, Sheet1(Margin) and change the lefthand dropdown from General to Worksheet, then change the righthand dropdown to Change you will get the lines below in Blue. Put the lines in Red between the others, and anytime you make a change on that worksheet, this macro runs. (note, there are three lines that will turn green in VBA, they are the comments)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim intSalePriceColumn As Integer
Dim intMarkupColumn As Integer
Dim intCostColumn As Integer
' determining which columns have the data
For x = 1 To 256
If Cells(1, x) = "Sale Price" Then intSalePriceColumn = x
If Cells(1, x) = "Markup" Then intMarkupColumn = x
If Cells(1, x) = "Cost" Then intCostColumn = x
Next x
' if you have just changed the sale price, calculate the markup
If ActiveCell.Column = intSalePriceColumn Then
Cells(ActiveCell.Row - 1, intMarkupColumn) = FormatPercent(Cells(ActiveCell.Row - 1, intSalePriceColumn) / Cells(ActiveCell.Row - 1, intCostColumn), 1, vbTrue, vbTrue)
End If
' if you have just changed the markup, calculate the sale price
If ActiveCell.Column = intMarkupColumn Then
Cells(ActiveCell.Row - 1, intSalePriceColumn) = FormatCurrency(Cells(ActiveCell.Row - 1, intMarkupColumn) * Cells(ActiveCell.Row - 1, intCostColumn))
End If
End Sub