IF=text then go to...

[testing]

Hi,
I've this trouble...

I want that if cell A1 value of sheet1 is equal to one word (example X) then
go to cell A2 of sheet2.

Is it possible?

TIA

 

[Dave Peterson]

Formulas don't do the "goto" stuff. They retrieve values from other locations.
That's about it.

 

[Bernard Liengme]

No, there is no formula that will do this

 

[joeu2004]

I want that if cell A1 value of sheet1 is equal
to one word (example X) then go to cell A2 of sheet2.
Is it possible?

=if(Sheet1!A1 = "X", Sheet2!A2, "")

 

[Pete_UK]

You could put this formula in B1 of Sheet1:

=IF(A1="X",HYPERLINK("#Sheet2!A2","jump"),"")

and this will show the word "jump" in that cell if A1 contains X. If
you click on the word jump it will take you to A2 in Sheet2. Not quite
what you wanted, but close to it.

Hope this helps.

Pete

 

[ShaneDevenshire]

Hi,

Stop and consider - if A1 = X then I want to go to Sheet2!A1 but at the
same time in if cell B1 = Y then I wand to go to Sheet3!A2. Now what happens
when A1=X and B1=Y? Excel can't go to two places at the same time.

Second analysis - If A1 =X then go to Sheet2!A1. So if A1 = X Excel moves
to Sheet2. Now you click on Sheet3. But back on Sheet1 A1 is still = X and
that means Excel should be in cell A1 of Sheet2, so Excel could not let you
move anywhere as long as A1 = X. So you can't even move to sheet1 to remove
the X.

So forget formulas, they aren't designed to lead to this kind of problem.
You should consider VBA if what you want is to move to sheet2 WHEN the user
types X into A1.

The code for that would be:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
Set isect = Application.Intersect(Target, Range("A1"))
If Not isect Is Nothing Then
Sheets("Sheet2").Activate
Range("A1").Select
End If
End Sub

This needs to be in the Sheet1 object in the VBE.

 

[testing]

Hi,

Stop and consider - if A1 = X then I want to go to Sheet2!A1 but at the
same time in if cell B1 = Y then I wand to go to Sheet3!A2. Now what
happens
when A1=X and B1=Y? Excel can't go to two places at the same time.

Second analysis - If A1 =X then go to Sheet2!A1. So if A1 = X Excel moves
to Sheet2. Now you click on Sheet3. But back on Sheet1 A1 is still = X
and
that means Excel should be in cell A1 of Sheet2, so Excel could not let
you
move anywhere as long as A1 = X. So you can't even move to sheet1 to
remove
the X.

So forget formulas, they aren't designed to lead to this kind of problem.
You should consider VBA if what you want is to move to sheet2 WHEN the
user
types X into A1.

The code for that would be:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
Set isect = Application.Intersect(Target, Range("A1"))
If Not isect Is Nothing Then
Sheets("Sheet2").Activate
Range("A1").Select
End If
End Sub

This needs to be in the Sheet1 object in the VBE.

Thank you for your suggest...
I try what you tell me but I'm afraid that i can't do this for my bad
knowledge with excel VBA....

The other solution:

=if(Sheet1!A1 = "X", Sheet2!A2, "")

and

=IF(A1="X",HYPERLINK("#Sheet2!A2","jump"),"")

return to me an error on formula.

I used Excel 2007.

Thanks

 

[joeu2004]

The other solution:
=if(Sheet1!A1 = "X", Sheet2!A2, "")
[....]
return to me an error on formula.

Well, there is no way to help you if you do not provide details.

And first, you need to clarify what you really want.

I ass-u-me-d that when you said "go to there", you really meant "get
the value from there".

If that is not what you meant, the paradigm I provided above will not
be useful to you anyway.