? IF & GOTO ?

[Karen D]

If a condition is met in a cell (A7='Yes'), I want it to go to another sheet
in the sameworksheet. Does anyone have any suggestions on how to do this?

 

[Mike H]

Hi,

Maybe this

=IF(A7="Yes",Sheet2!A1,Sheet3!A1)

Mike

 

[Eduardo]

Hi,
in cell B7 you can enter

=if(a7="YES",sheet2!A1,"")

if cell a7 = Yes, B7 will pull the information from sheet2 cell A1 otherwise
will be blank

 

[Karen D]

I thought that might work but it doesn't bring the curose to that cell in
Sheet2, it pulls whatever value is in that cell...there used to be a "goto"
function I thought...?

 

[Karen D]

I thought that might work but it doesn't bring the cursor to that cell in
Sheet2, it pulls whatever value is in that cell...there used to be a "goto"
function I thought...?

 

[Eduardo]

Hi Karen,
could you provide an example with indication or your final result, thank you

 

[Karen D]

Here's the screen shot:

Interview? Schedule Interview
Yes #VALUE!

and here's the formula I have in cell K7:

=IF(J7+"Yes",sheet3!C2," ")

 

[Mike H]

Hi,

You can't do that with a formula, you need VB. Are you ready for that? If
you are then post back and we'll show you how but will need a bit of an
explanation of what your trying to do.


Mike

 

[Eduardo]

Hi Karen,
but your formula copy information from sheet3 cell C2, and is the same as my
formula, what do you want to achieve sorry but I am missing something

 

[Karen D]

No, not ready for VB (as much as I would like to be). What I am trying to
do: A person selects "Yes" or "No" in cell J7 on sheet1. If "yes" is
selected, I would like cursor to jump to sheet3 C2. Somehow if I could tell
it "IF J7=YES, GOTO Sheet3!C2" - am I making sense?

 

[Mike H]

Karen.

Right click the sheet1 sheet tab and view code. paste the code below in on
the right hand side. Close VB editor and you should be OK.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$J$7" Then Exit Sub
If ucase(Target.Value) = "YES" Then
Application.Goto Sheets("Sheet3").Range("C2")
End If
End Sub

Mike

 

[pshepard]

Hi Karen,

Select cell K7, right click to insert a hyperlink to cell C2, select Sheet3

In K7, =if(j7="Yes","Click to Schedule Interview","")

 

[Gord Dibben]

As Mike told you............a formula returns results.

Formulas cannot "jump" you to anywhere.

No such function as "Goto"

VBA is only way..........see Mike's event code.


Gord Dibben MS Excel MVP

 

[Pete_UK]

Hi Karen,

you could partially achieve what you want to do using a HYPERLINK
formula. Put this in an appropriate cell near to J7 in Sheet1 (eg in
K7):

=IF(J7="yes",HYPERLINK("#sheet3!C7","jump"),"")

Now if J7 contains Yes the cell with the formula in will show "jump",
and if you click on that it will take your cursor to Sheet3!C7. It
doesn't automatically take you there when J7 changes to Yes - you have
to click the cell containing the formula as well, but it nearly does
what you want without any VBA.

Hope this helps.

Pete

 

[Karen D]

I did that and it works but only in J7. I went to the code and tried taking
out the $ but that doesn't work (a total stab in the dark on my part). Any
suggestions?

 

[Gord Dibben]

Karen

I did that and it works but only in J7

Where do you want it work and where do you want to jump to?


Gord Dibben MS Excel MVP

 

[Karen D]

I want it to work all the way down the J column, J8, J9, J10. I want it to
jump to sheet3, C2.

 

[Gord Dibben]

Always jump to Sheet3!C2?

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "J8:J50" 'adjust to suit
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
On Error GoTo ws_exit
Application.EnableEvents = False
If UCase(Target.Value) = "YES" Then
Application.Goto Sheets("Sheet3").Range("C2")
End If
End If
ws_exit:
Application.EnableEvents = True
End Sub


Gord