formula in one cell places value in another?

[ls62]

Hi,

I'm sure this must be easy but...

I want to allow the user to fill in cell A1 and if it is filled in then calculate (with a formula) and place the result in B1. If the user doesn't fill in A1 they may just fill in B1.

ex: if they fill in a1 with 100... calc b1 as (a1/10) and put value in B1 (10)

or

user could leave A1 blank and fill in

Thanks

 

[Hasit]

Use the IF clause in this form:

IF(ISBLANK(A1),"",A1/10)

and put it in B1. The formula will check A1 and if its blank, then it will leave B1 blank, otherwise it will divide the number in A1 by 10.

 

[getitdaily]

in b1 use this

=IF(A1="","",A1/10)

If A1 is null put nothing in B1 if A1 has a value then do a calculation in B1.

This won't check to make sure A1 is a number, but the user should get an error if A1 has something like 'test'.

If the user leaves A1 blank and just fills in B1 then the formula in B1 will be overwritten with the user value.

 

[ls62]

Thanks,

Is there a way that I can do this so that if the user manually fills in B1 it doesn't wipe out the formula. I knew I could do what you suggested.... it just tht i'll be reusing this worksheet and clearing certain data for re-entry and I don't want to have to keep replacing the B1 formula if it gets erased.

Do I have to do this using a macro instead?

Lee

 

[Hasit]

you can protect the cell which would stop anyone from overwriting the contents of the B1 cell.

To do this click on FORMAT-CELLS and ensure that the cell you want to protect has the "LOCKED" option cell in the PROTECTION tab.

For any cell, that you want to allow entry, uncheck the "LOCKED" option. Then select TOOLS-PROTECTION and then PROTECT WORKSHEET.

 

[getitdaily]

Sounds like a macro will be a good shot at what you want. Will you create new sheets and just copy the format?

 

[ls62]

I can't protect the cell, because if the don't enter anything in A1 then they will have to fill in B1.

I think I solved the problem by using a worksheet_change macro and update the value of Bx when the targe column Ax changes. This at least updates B1 when A1 is filled in and at the same time allows them to override the calc'd value and fill in B1.

See below:
Private Sub Worksheet_Change(ByVal target As Excel.Range)
If target.Row > 5 Or target.Row < 21 Then
If target.Column = 3 Then
If IsNumeric(target.Value) Then
target.Offset(0, 1).Value = target.Value / 0.04
End If
End If
End If
End Sub

Might not be perfect, but seems to do what I need.

Thanks for your help and suggestions.

LEE

 

[ls62]

I'm not sure what the best way to duplicate this would be? New sheet or copy format. If I use copy then I would have to modify macro for target row/col's each time a create new. If I use new sheet I don't think I would, but I could possibly have many sheets then.

Any suggestions.

Lee