Posted to microsoft.public.excel.newusers
|
|
dropdown list to a page in worksheet
I included IF() as part of the formula (VLOOKUP, HYPERLINK). If you want to
see my formula, please holler back. I have to run off now.
Epinn
"Jared" wrote in message
...
Epinn,
Where exactly do you add the "if()", in the list cell or the adjecent
cell with the hyperlink?
Jared
"Epinn" wrote:
I followed Aladin's suggestion and added IF() to check for a blank cell
so
that #N/A! wouldn't be displayed. It works beautifully. It's amazing
that
one formula does the job. My previous concern about clicking is not
necessary. As soon as an item on the drop-down list is clicked upon, a
hyperlink (in an adjacent cell) is created instantly, confirming
Aladin's
statement. Then when I click on the link, I am taken to the appropriate
sheet.
All in all, it is easier than I have anticipated once the "click"
concern is
out of the way.
Thank you for the question and I have learned something practical.
Epinn
"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Thanks for the link. Aladin is pretty sharp.
Jared could probably use that method.
Hope he is still hanging around.
Gord
On Tue, 29 Aug 2006 17:26:27 -0400, "Epinn"
wrote:
Gord,
I found the following link on drop down list and hyperlink without
using
VBA. Mind you it was Excel 2000. I am not sure about the suggestion
and
Jared's need, and wonder what you think.
One line caught my eyes - "This formula creates immediately a
clickable
hyperlink."
http://www.mrexcel.com/archive/Formulas/19517.html
Epinn
"Gord Dibben" <gorddibbATshawDOTca wrote in message
.. .
Jared
Do you have the ranges named in InsertName Define?
You could use event code with the DV list in B1
Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("B1")) Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
Select Case Target.Value
Case "peter"
Application.Goto Reference:="peter"
Case "paul"
Application.Goto Reference:="paul"
Case "mary"
Application.Goto Reference:="mary"
End Select
endit:
Application.EnableEvents = True
End Sub
If ranges are not named, just replace Application.Goto
Reference:="peter"
with
Sheets("Sheet2").Range("A1").Select
A lot of work if you have a great whack of names.
Gord Dibben MS Excel MVP
On Tue, 29 Aug 2006 11:22:02 -0700, Jared
wrote:
Epinn,
Okay, the first link got me through making the list i needed,
but
how
do
i add a hyper link per name? so clicking the name will get me to a
specific
cell. but each name needs to be to a different location. i might
need
to
add
a event click in vba, but i don't know how to add this to a list.
thanks
Jared
"Epinn" wrote:
Jared,
Shall I say the second link is a continuation of the first link.
If
your
list of valid entries is in a **different workbook** and you
follow
the
instructions from link 1, you may get an error message "You may
not
use
references to other worksheets or workbooks for Data Validation
criteria."
I did and I had to do a get around by using the instructions in
link
2.
It
may not be obvious to you until you try it. As I said in my
first
post, I
had to use INDIRECT() which was the second method stated in link
2.
Depending on your arrangement, you may or may not need link 2
for
the
fix.
I included it just in case.
Please look at link 1 and link 2 as part I and part II. If link
1
doesn't
help you at all, you can ignore link 2. You can also forget
about
link
2,
if you use ONE worksheet and not two workbooks.
I included the links for your reference as you mentioned drop
down
list.
That's all.
Epinn
"Jared" wrote in message
...
Epinn,
I looked through both links, but i couldn't figure out
exactly
what
exactly was i suppose to see in the pages. The first link got
me
through
the
list, but the second link i did not understand at all how was
connected to
my
situation
thanks
"Epinn" wrote:
Hi,
Since you used the term "drop down list," I would like to
highlight
the
following links. However, the purpose is for data
entry/validation. I
am
not surprised if your approach may have to be something very
different -
a
formula/procedure plus hyperlink? I'll let the experts fill
in
the
blank.
http://office.microsoft.com/en-us/as...022151033.aspx
http://support.microsoft.com/default...b;en-us;211548
Please note that I had to use INDIRECT() in the case of
referencing
a
**different** workbook (as per the second link above).
Thank you for the question and I am sure I can learn
something
here.
Epinn
"Jared" wrote in message
...
Hi,
I have a list of names in worksheet1
I have a Page for each name in Worksheet2
I need to creat a dropdown list in worksheet2 of names in
worksheet1
which
will allow me to jump to his specific page.
how do i do this?
Thanks,
Jared
Gord Dibben MS Excel MVP
|