how to create list of selections that individually link to other files or workbooks

Is this what you are trying to achieve - see attached workbook

This formula in cell A6

Formula:

Please Login or Register  to view this content.

See Table F13 for the item I tested on my PC

Table should contain full path for all files.

But if all the files are in one place, then table could just include File Name (incl extension) and the formula amended thus:

Formula:

Please Login or Register  to view this content.

HyperlinkLinkToFiles.jpg

This does it via a simple macro based on changing value in A6
Open the file, put in some valid file paths and then amend the values in A2 and A4

I will try to achieve it without a macro tomorrow and update the thread after that.



As written it should trigger whenever the value in cell A6 changes (ie if A2 or A4 change)- you could amendA6 to A4 or even A2 if you prefer
To amend the macro right-click on sheet tab "SelectHere" and select "View Code"

Please Login or Register  to view this content.

You are welcome

To upload a file:
Click on REPLY, then GO ADVANCED , then look below for MANAGE ATTACHMENTS

ManageAttachments3.jpg

I will look at this for you later today

If not confused, I may be a little uncertain as to what you want! I suspect you may need a "mix and match" of what is now included in the file.

If this does not give you what you want, then please attach a clean file that:
- contains only what you want the end file to look like - including all tabs
- PLUS a few textboxes on each sheet containing explanations of what should hyperlink to what and which cells are to contain dropdowns selections that drive hyperlinks

Setting aside (for now) "linking to "Funder Profile" file" , let's focus on exactly what you want the main file to do

sheet Table is used as our lookup
- columns D:E have dropdowns with values based on the 3 related sheets
- column C will contain links to Funder Profile (IGNORE FOR NOW)
- column B - has a link to Project Details

sheet SelectHere
- if "Project Detail" selected in A4, VBA kicks in and takes user to correct Project in sheet "Project Detail"
- if any other value is selected in A4, then values A2 & A4 are looked up in sheet "Table" and A6 shows the result
- A6 is now either
a hyperlink (if A4 = Project Detail)
OR
a value looked up in sheet "Table"
NOTE - A2/A4/A6 combo could also contain a hyperlink to Funder Profile File - IGNORE FOR NOW
A6 formula:

Formula:

Please Login or Register  to view this content.

I was confused by the extra lines you added to sheet "SelectHere" - not sure where these fit in to the main file = so added a link to Project Detail on each line. Note use made of helper column H to avoid the cell looking up itself for a value.
formula in A10 copied down:

Formula:

Please Login or Register  to view this content.

Hyperlinks.jpg

VBA code
- in this case the VBA does not make use of the hyperlink formula
- cell reference is derived via lookup
- then sheet and cell selected

Please Login or Register  to view this content.

NOTE 1
whilst the =HYPERLINK formula works as a link in Excel itself, it will not work as a link with Hyperlinks().Follow function in VBA

Please Login or Register  to view this content.

does not work if the link in A6 is derived by formula

NOTE 2
A sheet name containing a "space" must be enclosed in single quotation marks ' ' when built into references
That is not required when the name does not contain space(s)

formula components in A2-A8
Buiding hyperlinks as a formula means that they are not all having to be created individually (via the cell right click/insert hyperlink method)

- formula build up
=HYPERLINK(link address,friendly name)

the formula we need to get the link to work:

Formula:

Please Login or Register  to view this content.

- working back a step at a time
"Grant_0001" is in cell H2
=HYPERLINK("#'Project Details'!A3",H2)

To find correct cell (=A3) in Project Details:

we get row number from:
=MATCH(H2,A:A,0)
=MATCH("Grant_0001",A:A,0)
(look for "Grant_0001", in column A ,find exact match)
That gives the row number = 3

We want column A (=column 1)

to convert row 3 and column 1 to A3 we can use =ADDRESS
=ADDRESS(row,column,how displayed)
=ADDRESS(3,1,how displayed)
=ADDRESS(3,1,1)

and this is how it all joins up

Formula:

Please Login or Register  to view this content.

[NOTE - if you prefer, you could use the method below and build each hyperlink manually by selecting "Place in this document" instead of "Existing File or Webpage"]

To create a hyperlink in M4 with the text you want displayed either use the formula method:

Formula:

Please Login or Register  to view this content.

or
Copy the link to clipboard
Right click on cell M4 and select "Hyperlink"

Hyperlink.jpg