validation list or combo boxes with hyperlinks

[Guest]

Hi,

I have a spreadsheet with around 100 entries in one sheet related to a
hotel's rate contracts. Each contract (entry) covers around 30-35 lines. I
have already defined the print areas and I have named them accordingly. I
would like however to create a drowdown menu at the top of the sheet, from
where I could pick up the name of the contract and from there the equivalent
contract (as defined on the print area) to appear at the top of the sheet -
something like a hyperlink. Can anyone help please?

Thanks a lot in advance

 

[Guest]

Is this what you want? I have a data validation list in A2 with Contract
Names and I assumed the printareas have the same name. Selecting a contract
positions a copy the equivalent printarea at C2 and places a hyperlink to the
area in B1.

Code to be placed in the worksheet containing the contracts/print areas
Right click on worksheet tab, "view code" and copy.

HTH

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo wsexit:
Application.EnableEvents = False
If Target.Address = "$A$2" Then
Range("B1").Hyperlinks(1).SubAddress = Target.Value
Range(Target.Value).Copy Range("C2")
End If
wsexit:
Application.EnableEvents = True
End Sub

 

[Guest]

You can use your list to create a data validation dropdown. It sounds like
the list of entries is on a different sheet. You can select them and do
Insert=>Name=>Define and type in the name List1 in the name box and click OK
(the refers to box should default to the cells selected - if not adjust it to
refer to your list).

Now go to the cell where you want to make the selection and do

Data=>validation, select the list Option and in the source box type

=List

Now you can select from the dropdown.

go back to the Insert=>Names=>Define and select the Print_Area name for that
sheet. Change the refers to box to

=If(Datasheet!$B$9="",Indirect("Hotel1"),Indirect(DataSheet!$B$9))

Where DataSheet!B9 is the cell where you applied the data validation dropdown.

the only drawback, is if you go into pagesetup, I believe your formula will
be overwritten.

But once you have everything set up, you shouldn't need to.