=IF Statement formula or conditional formatting
 

I'm trying to solve a problem with a formula I'd like to write. I think I'm
doing it correctly, but it keeps giving me a circular reference error.

What I want:
If data entry in the cell is 1, make the cell read One-Man, otherwise make
the cell read Two-Man. (It will only ever be 1 or 2.)

What I was trying to use:
=IF(B205=1,"One-Man","Two-Man")
or some iteration of that.

Maybe I should be using a different formula function. I'm not sure. Help!

Abi,

If you are putting that formula in B205, QED, circular reference.

A formula cannot refer to its own containing cell, only other cells. So you
formula needs to go in C205 or somewhere.

--
HTH

-------

Bob Phillips
"Abi" wrote in message
...
I'm trying to solve a problem with a formula I'd like to write. I think
I'm
doing it correctly, but it keeps giving me a circular reference error.

What I want:
If data entry in the cell is 1, make the cell read One-Man, otherwise make
the cell read Two-Man. (It will only ever be 1 or 2.)

What I was trying to use:
=IF(B205=1,"One-Man","Two-Man")
or some iteration of that.

Maybe I should be using a different formula function. I'm not sure.
Help!

Hi Abi

because you're trying to change a value in the same cell as your formula you
will get a circular reference error however, one way to approach it is via
VBA

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Range("B205") = 1 Then
Range("B205") = "One-Man"
Elseif Range("B205") = 2 then
Range("B205") = "Two-Man"
Else
Range("B205") = ""
End If
Application.EnableEvents = True
End Sub

- to use this code, right mouse click on the sheet tab where you want the
code and choose view code
copy & paste the code there

Cheers
JulieD

"Abi" wrote in message
...
I'm trying to solve a problem with a formula I'd like to write. I think
I'm
doing it correctly, but it keeps giving me a circular reference error.

What I want:
If data entry in the cell is 1, make the cell read One-Man, otherwise make
the cell read Two-Man. (It will only ever be 1 or 2.)

What I was trying to use:
=IF(B205=1,"One-Man","Two-Man")
or some iteration of that.

Maybe I should be using a different formula function. I'm not sure.
Help!

It works great! Thanks! (And thanks for the easy-to-use instructions - some
days I'm not too quick!) :)

Do you have any recommendations for a website or book (preferrably the
website) that has a "VBA for slow people" approach to learning some of the VB
basics?

Thanks!

"JulieD" wrote:

Hi Abi

because you're trying to change a value in the same cell as your formula you
will get a circular reference error however, one way to approach it is via
VBA

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Range("B205") = 1 Then
Range("B205") = "One-Man"
Elseif Range("B205") = 2 then
Range("B205") = "Two-Man"
Else
Range("B205") = ""
End If
Application.EnableEvents = True
End Sub

- to use this code, right mouse click on the sheet tab where you want the
code and choose view code
copy & paste the code there

Cheers
JulieD

"Abi" wrote in message
...
I'm trying to solve a problem with a formula I'd like to write. I think
I'm
doing it correctly, but it keeps giving me a circular reference error.

What I want:
If data entry in the cell is 1, make the cell read One-Man, otherwise make
the cell read Two-Man. (It will only ever be 1 or 2.)

What I was trying to use:
=IF(B205=1,"One-Man","Two-Man")
or some iteration of that.

Maybe I should be using a different formula function. I'm not sure.
Help!

You know, last week when I entered the script into my worksheet, it worked.
Then, I had some odd stuff happen to my template and lost some info so I had
to re-add the script - and now I can't get it to work. I'm literally just
cut and pasting it and just changing the cell from 205 to 208 (the actual
reference cell).

I'm stumped!

"JulieD" wrote:

Hi Abi

because you're trying to change a value in the same cell as your formula you
will get a circular reference error however, one way to approach it is via
VBA

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Range("B205") = 1 Then
Range("B205") = "One-Man"
Elseif Range("B205") = 2 then
Range("B205") = "Two-Man"
Else
Range("B205") = ""
End If
Application.EnableEvents = True
End Sub

- to use this code, right mouse click on the sheet tab where you want the
code and choose view code
copy & paste the code there

Cheers
JulieD

"Abi" wrote in message
...
I'm trying to solve a problem with a formula I'd like to write. I think
I'm
doing it correctly, but it keeps giving me a circular reference error.

What I want:
If data entry in the cell is 1, make the cell read One-Man, otherwise make
the cell read Two-Man. (It will only ever be 1 or 2.)

What I was trying to use:
=IF(B205=1,"One-Man","Two-Man")
or some iteration of that.

Maybe I should be using a different formula function. I'm not sure.
Help!

Okay - I got it to work, but I'm confused. When it didn't work, I was
pasting at the bottom of the screen (past the other scripts I have.) This
time I pasted it at the very top and it worked. Is that supposed to matter?
(I am VERY green about VB.)

Thanks!

"JulieD" wrote:

Hi Abi

because you're trying to change a value in the same cell as your formula you
will get a circular reference error however, one way to approach it is via
VBA

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Range("B205") = 1 Then
Range("B205") = "One-Man"
Elseif Range("B205") = 2 then
Range("B205") = "Two-Man"
Else
Range("B205") = ""
End If
Application.EnableEvents = True
End Sub

- to use this code, right mouse click on the sheet tab where you want the
code and choose view code
copy & paste the code there

Cheers
JulieD

"Abi" wrote in message
...
I'm trying to solve a problem with a formula I'd like to write. I think
I'm
doing it correctly, but it keeps giving me a circular reference error.

What I want:
If data entry in the cell is 1, make the cell read One-Man, otherwise make
the cell read Two-Man. (It will only ever be 1 or 2.)

What I was trying to use:
=IF(B205=1,"One-Man","Two-Man")
or some iteration of that.

Maybe I should be using a different formula function. I'm not sure.
Help!

Hi Abi

glad you solved it - sorry i didn't get back to you before now - we've got
bushfires near us and the power hasn't been that reliable lately.

when you did the view code option the cursor generally goes to the place
where you should paste the code - it needed to be against the worksheet
itself and not in a module.

Hope this helps
Cheers
JulieD

"Abi" wrote in message
...
Okay - I got it to work, but I'm confused. When it didn't work, I was
pasting at the bottom of the screen (past the other scripts I have.) This
time I pasted it at the very top and it worked. Is that supposed to
matter?
(I am VERY green about VB.)

Thanks!

"JulieD" wrote:

Hi Abi

because you're trying to change a value in the same cell as your formula
you
will get a circular reference error however, one way to approach it is
via
VBA

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Range("B205") = 1 Then
Range("B205") = "One-Man"
Elseif Range("B205") = 2 then
Range("B205") = "Two-Man"
Else
Range("B205") = ""
End If
Application.EnableEvents = True
End Sub

- to use this code, right mouse click on the sheet tab where you want the
code and choose view code
copy & paste the code there

Cheers
JulieD

"Abi" wrote in message
...
I'm trying to solve a problem with a formula I'd like to write. I
think
I'm
doing it correctly, but it keeps giving me a circular reference error.

What I want:
If data entry in the cell is 1, make the cell read One-Man, otherwise
make
the cell read Two-Man. (It will only ever be 1 or 2.)

What I was trying to use:
=IF(B205=1,"One-Man","Two-Man")
or some iteration of that.

Maybe I should be using a different formula function. I'm not sure.
Help!