how i can apply any formula in a cell where already something written.

[ankit bidawatka]

i want to apply a logical formula in a cell where already something written, and if the condition goes true then the result will be what i want but if the condition goes false then i want the same value which already in the cell.

for eg. in cell A1 60 is written, and if formula becomes true then i want "60E" in the cell and if formula becomes false then i want the same value which already in the cell ie 60.

 

[Sajan]

Hello ankit,

Could you describe the logic you are looking to apply for those cells?


If it is something simple, you can accomplish what you are after using conditional formatting.


For example, if you want to add the suffix "E" when the value is greater than or equal to 50, but no suffix if value is less than 50, you could apply the following custom conditional format

[<50]#;[>=50]#"E"


If you are looking to apply complex logic, you have some choices:

1) Use a helper column, where the helper column will show the results you are after

2) or, use VBA


Cheers,

Sajan.

 

[ankit bidawatka]

Thanks sajan for ur help. but still i am unable to solve my query.

let me tell u in detail what i want-

1. in cell A1 suppose any number from 1 to 100 is written.

2. in cell A5 1 to 400 number and in A6 1 to 800 number is written.

3. i want that if i apply logical formula in cell A1, and where number is 60 or more written and in cell A5 no.<200 and in A6<400 is written then value in cell A1 will be its pre written number with suffix "E", for eg. "60E",and if formula goes false then no suffix "E" is required.

 

[Sajan]

Hello ankit,

It sounds like your question can be solved with my original suggestion, but repeated separately for each of the cells (A1, A5 and A6).


For A1, use the following custom format:

[<60]#;[>=60]#"E"


For cell A5, use the following custom format:

[>=200]#;[<200]#"E"


For cell A6, use the following custom format:

[>=400]#;[<400]#"E"


Cheers,

Sajan.

 

[SirJB7]

@Sajan

Hi!


Am I wrong or the 3 conditions A1>=60, A5<200 and A6<400 should happen at same time to add an "E" to A1 contents? If so the only solution is to use the worksheet change event.


Code:

-----

[pre]

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
' constants
Const ksrng1 = "A1"
Const kirng1 = 60
Const ksrng2 = "A5"
Const kirng2 = 200
Const ksrng3 = "A6"
Const kirng3 = 400
' declarations
Dim rng1 As Range, rng2 As Range, rng3 As Range
' start
Set rng1 = Range(ksrng1)
Set rng2 = Range(ksrng2)
Set rng3 = Range(ksrng3)
If Application.Intersect(Target, rng1) Is Nothing And _
Application.Intersect(Target, rng2) Is Nothing And _
Application.Intersect(Target, rng3) Is Nothing And _
Target.Cells.Count > 1 Then Exit Sub
' process
With rng1
If .Value >= kirng1 And rng2.Value < kirng2 And rng3.Value < kirng3 Then
.NumberFormat = "General""E"""
Else
.NumberFormat = "General"
End If
End With
' end
Set rng3 = Nothing
Set rng2 = Nothing
Set rng1 = Nothing
End Sub

[/pre]
-----


Regards!

 

[Sajan]

Hello @SirJB7,

I think you are correct. I misread the OP's post. I think the OP wants to combine the logic and apply it in single cell. Hence the only viable option would be VBA, like you posted.


Cheers,

Sajan.