IF=text then go to...

  • Thread starter Thread starter testing
  • Start date Start date
T

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
 
Formulas don't do the "goto" stuff. They retrieve values from other locations.
That's about it.
 
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
 
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.
 
ShaneDevenshire said:
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.
Click to expand...

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
 
The other solution:
=if(Sheet1!A1 = "X", Sheet2!A2, "")
[....]
return to me an error on formula.
Click to expand...

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.
 
Back
Top