use formula if cell is blank

[stigger]

OK Question is I want I have 2 columns one with kilometres and the other in miles. Because I get the figure in each I want to be able to enter data in either column and have the other populate.

ie. in cell c1 =if(c1="",c1=d1*0.6,c1) and in d1 =if(d1="",c1*1.6,d1)

but obviously that dosen't work.

Help please...

 

[mintjulep]

As you have found out, any given cell cannot contain both a formula and a constant value simultaneously.

To do this you will need some VBA.

 

[randy700]

Or, you could store the values on one worksheet and formulas on another.

Cell C1: =if(Sheet2!C1 = "m", Sheet2!D1 * 0.6, Sheet2!D1)
Cell D1: =if(Sheet2!C1 = "k", Sheet2!D1 * 1.6, Sheet2!D1)



Randy

 

[AnotherHiggins]

Just to flesh that out a bit....

The reason you cannot refer to the c1 in a formula that resides in C1 is because the cell doesn't contain a value until the formula is resolved. So you wind up with a circular reference.

But to get what you are after, you have a few options:

[tab]- The easiest and most straightforward solution is to use two other columns. So in, say, E1, you would put [COLOR=blue white]=if(c1="",d1*0.6,c1)[/color] and in F1 you would put [COLOR=blue white]=if(d1="",c1*1.6,d1)[/color]

[tab]- If you want to adjust existing data, you could select the used rows of column C, go to Edit > Goto > Special > Blanks, then type in a formula like [COLOR=blue white]=d1*0.6[/color] and enter with [Ctrl]+{Enter]. Using [Ctrl]+[Enter] instead of just [Enter] will fill in the formula for all selected cells. Repeat for column D.

[tab]- Use a macro to actually get the full functionality you are looking for. If you want to explore that option, please post a new thread in forum707.


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.