Working an If formula

[Daleen]

I have 4 columns that I am concerned with. Say F6 is a fixed number, column
G & H are Yes and No columns. Column I6 could change. I want my formula to
see if there is an X in G6. If there is, then I want I6 to equal F6. If the
X is in H6 I want a manual figure to be put in. How do I make this formula
work? Please help.

 

[Marcelo]

Are you looking for a manual input and a formula at the same cell? if you
entre an manual input you will lose the formula, I suggest you to use an
auxilar column j for instance to enter the manual data that you want on I if
the X is on H column.

so the formula on I6 in this case should be:

=IF(G6="X",F6,IF(H6="X",J6))

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Daleen" escreveu:

 

[JLatham]

A cell may either contain a formula or a value, but not both at the same
time. When your user types in a manual input, the formula is going to be
deleted.

About the only way you could deal with this is to use one more cell in the
process to accept that additional input to be used when there is an X in H6.
Lets say this new entry is in column I and your final result now moves to
column J.

Your formula in J6 might look something like this:
=IF(G6="X",F6,I6)
We don't need to specifically test for H6 = "X" if we can reasonably expect
that if "YES" hasn't been chosen that "NO" must have been.

 

[dan dungan]

I had this twist:

=IF(G6="x",F6,IF(AND(H6="x",ISBLANK(J6)),"Enter the manual value in
j6",J6))

 

[JLatham]

It's not a bad idea at all.

 

[Gord Dibben]

And another twist..............

You could use event code and not bother with any formulas or an extra
column.

This seems to do the job.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False

If Target.Cells.Column = 7 Then '7 is G
n = Target.Row
If UCase(Target.Value) = "X" Then
Range("I" & n).Value = Range("F" & n).Value
End If
End If
If Target.Cells.Column = 8 Then '8 is H
n = Target.Row
If UCase(Target.Value) = "X" Then
Range("I" & n).Value = InputBox("enter a number")
End If
End If

enditall:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP

 

[Daleen]

Thanks so much. I just needed that last line of tweaking!! I can't tell you
how much this helps.

 

[Daleen]

You all are fantastic. I have good knowledge of the basic math functions,
but get lost in the IF and SUMIF. Your help on both answers was greatly
appreciate.

 

[Daleen]

Can't thank you enough for the input. I have two scenerios now and will see
which works best. Thank you for the great help.

 

[Daleen]

You gave me another option also. Maybe more than I can handle, but trust me
I am going to try and see which works best for my boss and me. Thanks so
much.