IF Stmt

Puntarenas · May 2nd, 2002, 12:46 PM

I need to change the value of a cell if a criteria is met otherwise leave the current value of that cell intact - How do I do that?

waderw · May 2nd, 2002, 12:48 PM

You could try some VB.

If Activecell.value = (your criteria) then
activecell.value = (new value)
else
activecell.offsett(1,0).select 'go to next cell
end if

phiore · May 2nd, 2002, 12:56 PM

you can also use a formula for this.
=if(celllocation=criteria, newvalue,celllocation)
substitute the cell address, criteria and newvalue for the names above.

Puntarenas · May 2nd, 2002, 05:17 PM

Phiore,
When I use your formula I get a 'circular reference' error. This is due to trying to leave the value of the
'test' cell when the 'criteria' is not met. Any other ideas
PS. I am not a VB programer

[ This Message was edited by: Puntarenas on 2002-05-02 16:19 ]

anno · May 2nd, 2002, 05:29 PM

Quote:

...I get a 'circular reference' error.

could you provide some more information including cell refs and exactly what you want to have the formula return?

Puntarenas · May 18th, 2002, 01:32 PM

Anno,
Sorry taken so long to reply (traveling).
Basically, what I am trying to do is to leave the value of the cell being tested intact unless the value of another cell matches a criteria
Ex.
A2= 8
A5= Y
I want to test cell A2 so that if A5 = Y change the value to 10 else leave 8.
Does this help?
JAC

sen_edp · May 18th, 2002, 03:05 PM

Hello Jac

take a look, is this what you mean or am i missing something?

Microsoft Excel - HtmlMaker.xls
File(F) Edit(E) View(V) Insert(I) Tool(T) Data(D) Window(W) Help(H)
A1		=
	A	B	C	D	E
1
2	:alert('10')>=IF(A5="Y",10,8)
3
4
5	Y
6
7
8
9
Sheet1

You can see the value of cells only click each above hyperlinks

Puntarenas · Jun 10th, 2002, 01:50 PM

Adrian,
Thank you for your response. You almost had it right. Using your example:
For the value of A2:
If A5 = 'Y' then place the value of '10' in A2 else place whatever the current value of A5 in A2.
JAC

[ This Message was edited by: Puntarenas on 2002-06-10 12:58 ]

[ This Message was edited by: Puntarenas on 2002-06-10 12:59 ]

[ This Message was edited by: puntarenas on 2002-07-11 17:01 ]

ErieShoreShucker · Jan 16th, 2007, 09:24 PM

This is my question also. I want to test the value of a cell. If it is void, empty, my If statement will be true and text will be entered. If it is false, ie there is text or numbers in the cell, (and there ussally will be), I want it left alone. Something like
a b c
1
2 45 $1 1
3 46 $2 2
IF(A1="","Page " &C2". do nothing

Jan 16th, 2007, 09:24 PM	#9
ErieShoreShucker New Member Join Date: Jan 2007 Posts: 3	If true, do something, do nothing (NOT "" ) This is my question also. I want to test the value of a cell. If it is void, empty, my If statement will be true and text will be entered. If it is false, ie there is text or numbers in the cell, (and there ussally will be), I want it left alone. Something like a b c 1 2 45 $1 1 3 46 $2 2 IF(A1="","Page " &C2". do nothing

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 2nd, 2002, 12:46 PM	#1
Puntarenas New Member Join Date: Apr 2002 Posts: 6	I need to change the value of a cell if a criteria is met otherwise leave the current value of that cell intact - How do I do that?

May 2nd, 2002, 12:48 PM	#2
waderw Board Regular Join Date: Apr 2002 Posts: 85	You could try some VB. If Activecell.value = (your criteria) then activecell.value = (new value) else activecell.offsett(1,0).select 'go to next cell end if

May 2nd, 2002, 12:56 PM	#3
phiore New Member Join Date: May 2002 Posts: 11	you can also use a formula for this. =if(celllocation=criteria, newvalue,celllocation) substitute the cell address, criteria and newvalue for the names above.

May 2nd, 2002, 05:17 PM	#4
Puntarenas New Member Join Date: Apr 2002 Posts: 6	Phiore, When I use your formula I get a 'circular reference' error. This is due to trying to leave the value of the 'test' cell when the 'criteria' is not met. Any other ideas PS. I am not a VB programer [ This Message was edited by: Puntarenas on 2002-05-02 16:19 ]

May 18th, 2002, 01:32 PM	#6
Puntarenas New Member Join Date: Apr 2002 Posts: 6	Anno, Sorry taken so long to reply (traveling). Basically, what I am trying to do is to leave the value of the cell being tested intact unless the value of another cell matches a criteria Ex. A2= 8 A5= Y I want to test cell A2 so that if A5 = Y change the value to 10 else leave 8. Does this help? JAC

Jun 10th, 2002, 01:50 PM	#8
Puntarenas New Member Join Date: Apr 2002 Posts: 6	Adrian, Thank you for your response. You almost had it right. Using your example: For the value of A2: If A5 = 'Y' then place the value of '10' in A2 else place whatever the current value of A5 in A2. JAC [ This Message was edited by: Puntarenas on 2002-06-10 12:58 ] [ This Message was edited by: Puntarenas on 2002-06-10 12:59 ] [ This Message was edited by: puntarenas on 2002-07-11 17:01 ]