Dropdown List

[Lynda]

I have a dropdown list on page 1, with numbers from 1 to 10. I have the
dropdown linked to the cell underneath (eg A1). If the number in the dropdown
(and cell A1) is greater than 1, I want it to go to a cell in sheet 2. I have
tried the ‘IF’ statement but I am obviously doing something wrong. Your help
would be greatly appreciated.

Lynda

 

[muddan madhu]

Sheet1 has 1 to 10 number,

sheet2 has some values

define the sheet1 range as shtn1
similarly define the sheet2 range as shtn2

now data validation - allow: list | source : =IF(A1>1,shtn2,shtn1) |
ok

 

[Luke M]

In your cell on sheet2, something like this:
=IF('Sheet1'!A1>1,'Sheet1'!A1,"")

 

[Lynda]

Sorry Folks but whatever has happened it is just not working. Thank you for
your help.

Cheers
Lynda

 

[Gord Dibben]

A formula can only return results. It can't take you anywhere.

If you want to go to.....select......another cell you must use VBA

First we name the cell to go to.

Select the cell on Sheet2 and Insert>Name>Define

Name it mycell

Right-click on Sheet1 tab and "View Code" Copy/paste this code into that
sheet module.

Edit to suit then Alt + q to go back to Excel.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo stoppit
Application.EnableEvents = False
If Target.Address = "$A$1" And Target.Value > 1 Then
Application.Goto Reference:="mycell"
End If
stoppit:
Application.EnableEvents = True
End Sub

Pick a number greater than 1 from the DV dropdown list.


Gord Dibben MS Excel MVP

 

[Gord Dibben]

A thought...........do you want to go to a certain sheet and cell depending
upon the number picked?

You just said > 1 go to Sheet2

Maybe you meant =5 go to Sheet5?


Gord

 

[Lynda]

Thank you Gord. Yes I did mean for the code to send it to sheet 2. What I
have with the drop down is that if the client wants to choose more than one
item then they will be sent to sheet 2 where there are more boxes for them to
enter their requests. then once they have comleted their requests they hit a
hyperlink which will send them back to sheet1.

I am having trouble getting your code to work. Will the fact that I am using
Excel 2007 at home be the problem, it’s just that we use 2003 at work which
is where I need to use the form I’m creating.

 

[Gord Dibben]

What is "trouble getting it to work"?

You have named the cell on sheet2 as "mycell"..........no quotes when naming
but you must use the quotes in the code as shown.

Does "mycell" refer to =Sheet2!cellref where cellref is your selected cell?

Did you paste the code into sheet1 module per instructions?

Is your dropdown in A1 on sheet1?

The code will work on 2007 and 2003.

Did you save the workbook as macro-enabled *.xlsm?


Gord

 

[Lynda]

Gord, I am so sorry for the delay in getting back to you, got sidetracked
with some other urgent stuff at work. Anyway I can’t get the code to work. I
have followed your instructions. I went to sheet 2 (two) and named the
destination cell ‘mycell’ without the quotation marks. I then clicked on the
tab to the sheet I have my dropdown in (which is called ‘Bulk Recruitment
Placements’) I right clicked and went to view code. In the code sheet I
copied the code you wrote for me,
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo stoppit
Application.EnableEvents = False
If Target.Address = "$G$28" And Target.Value > 1 Then
Application.Goto Reference:="mycell"
End If
stoppit:
Application.EnableEvents = True
End Sub

All I changed was the “$A$1†to “$G$28†which is where my dropdown ended up.
The dropdown is linked to this cell.
I keep getting the following error message -
The macro ’’Bulk Recruitment Placements.xls’!DropDown36_Change’ cannot be
found.
I have other code on the same page as it also needs to be there for it to
operate other dropdowns as well.
I have tried removing that code in case it was interfering with your code
and it doesn’t make any difference. I have tried lots of different things but
nothing seems to work. I just get really annoyed with myself that I can’t do
this stuff myself. Thank goodness for you people.

Lynda

 

[Gord Dibben]

Sounds like maybe you have sheetactivate code in sheet2 that tries to run a
macro when the sheet2 is activated(selected)

The sheetactivate code could also be in Thisworkbook module as

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
code here
End Sub

If you can't find anything, send the workbook to my email at

gorddibbATshawDOTca make the appropriate changes to address.

Gord

 

[Lynda]

Thank you Gord, you are a legend.

Cheers
Lynda

Sounds like maybe you have sheetactivate code in sheet2 that tries to run a
macro when the sheet2 is activated(selected)

The sheetactivate code could also be in Thisworkbook module as

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
code here
End Sub

If you can't find anything, send the workbook to my email at

gorddibbATshawDOTca make the appropriate changes to address.

Gord



.