Insert cell value into bookmark link

[rashl2]

I have a list of student names in an Excel 2010 worksheet in column A. Each student has two worksheets that I need to link to (from columns B and C). Can I create a bookmark link that takes its worksheet name from the column A. This would allow me to create spreadsheets for many different classes without having to manually hyperlink/bookmark for every student. Instead I could fill down for the two link columns.

I would also, ideally, like to have the student worksheet names generated from a cell value. Is this possible?

Thanks, in advance . . .

 

[nigelk]

You could try using the Indirect function to get a value from a sheet specified in your column.

As good as explanation as any:

Excel: Reference a sheet name in a formula using the INDIRECT Formula - YouTube

 

[Excelot]

Can I create a bookmark link that takes its worksheet name from the column A. This would allow me to create spreadsheets for many different classes without having to manually hyperlink/bookmark for every student.

I'm not sure about being able to link a bookmark to a cell value, but I had a similar problem in which I solved using the the 'HYPERLNK' formula. Here's how I went about it...

When the workbook opens, I save the workbook name to a named range 'DAT_FILE_NAME' within a sheet 'FILE_DATA'. The code should be placed in the ThisWorkbook object:

Private Sub Workbook_Open()
    Worksheets("FILE_DATA").Activate
    Worksheets("FILE_DATA").Range("DAT_FILE_NAME") = ActiveWorkbook.Name
End Sub

With that out of the way, next you need to create the hyperlink to jump to the sheet you're looking for. In this example, I've prefixed the sheet name with "i_" to indicate it's for an individual. The rest of the sheet name is the unique reference for the individual. So if I have a range as below...

....A...B
1) ID....Name
2) AAA Mickey
3) BBB Goofy
4) CCC Donald


I would have three sheets, 'i_AAA', 'i_BBB' and 'i_CCC'.

The hyperlink is created as follows:

HYPERLINK(CONCATENATE("[",DAT_FILE_NAME,"]", "i_",A2,"!A1"),"Link")

The "Link" (last part) of the HYPERLINK function is just the visible text you'd like to use for the hyperlink.

The 'CONCATENATE' function builds up the hyperlink then executed by the HYPERLINK function, so in the above example, the result of the CONCATENATE function would be '[Book1.xlsm]i_AAA!A1' (without single quotes). A1 is the cell that you want to jump to in the worksheet.

Because I use this with a unknown range, I further enhance the formula to display nothing if the target cell is blank:

=IF(NOT(ISBLANK(A2)),HYPERLINK(CONCATENATE("[",DAT_FILE_NAME,"]", "i_",A2,"!A1"),"Link"),"")

There might be a more simple way to do this, but this has worked really well for me. Of course you mentioned that you will jump to two different sheets, they you just need to adjust the formula for the name of the additional sheet and put it in the second column.

 

[rashl2]

I'm not sure about being able to link a bookmark to a cell value, but I had a similar problem in which I solved using the the 'HYPERLNK' formula. Here's how I went about it...

When the workbook opens, I save the workbook name to a named range 'DAT_FILE_NAME' within a sheet 'FILE_DATA'. The code should be placed in the ThisWorkbook object:

Private Sub Workbook_Open()
    Worksheets("FILE_DATA").Activate
    Worksheets("FILE_DATA").Range("DAT_FILE_NAME") = ActiveWorkbook.Name
End Sub

With that out of the way, next you need to create the hyperlink to jump to the sheet you're looking for. In this example, I've prefixed the sheet name with "i_" to indicate it's for an individual. The rest of the sheet name is the unique reference for the individual. So if I have a range as below...

....A...B
1) ID....Name
2) AAA Mickey
3) BBB Goofy
4) CCC Donald


I would have three sheets, 'i_AAA', 'i_BBB' and 'i_CCC'.

The hyperlink is created as follows:

HYPERLINK(CONCATENATE("[",DAT_FILE_NAME,"]", "i_",A2,"!A1"),"Link")

The "Link" (last part) of the HYPERLINK function is just the visible text you'd like to use for the hyperlink.

The 'CONCATENATE' function builds up the hyperlink then executed by the HYPERLINK function, so in the above example, the result of the CONCATENATE function would be '[Book1.xlsm]i_AAA!A1' (without single quotes). A1 is the cell that you want to jump to in the worksheet.

Because I use this with a unknown range, I further enhance the formula to display nothing if the target cell is blank:

=IF(NOT(ISBLANK(A2)),HYPERLINK(CONCATENATE("[",DAT_FILE_NAME,"]", "i_",A2,"!A1"),"Link"),"")

There might be a more simple way to do this, but this has worked really well for me. Of course you mentioned that you will jump to two different sheets, they you just need to adjust the formula for the name of the additional sheet and put it in the second column.

Hi,
Not sure if I've got the first bit right but for the second part I have;

=HYPERLINK(CONCATENATE("[",DAT_FILE_NAME,"]", "S1_",A7,"!A1"),"Link") where the cell A7 and S1_ both make up the worksheet name, S1_NAME, One.
It gives me an invalid name error.
I tried it also with the actual file name instead of DAT_FILE_NAME but with the same result.

For the first part I copied your code into the visual basic window in a new sheet called FILE_DATA and also into the sheet I'm trying to link from.

I may be a little out of my depth but really need a solution.

 

[Excelot]

Hi rashl2. Sorry for any confusion.

The following code needs to go in the ThisWorkbook object found in the VBAProject Explorer under VBAProject (YOUR WORKBOOK NAME) -> Microsoft Excel Objects -> ThisWorkbook.

Private Sub Workbook_Open()
    Worksheets("FILE_DATA").Activate
    Worksheets("FILE_DATA").Range("DAT_FILE_NAME") = ActiveWorkbook.Name
End Sub

'DAT_FILE_NAME' is a named range on a worksheet named 'FILE_DATA'.

I think the name error might be if you haven't created the named range. I should have made this more clear . If you're not sure how to create a named range, you can either type in the name you want to use for the cell/range in the text box that shows the cell reference (to the left of the formula bar), or click the 'Formulas' menu, then click 'Name Manager' button in the 'Defined Names' group. You can then create a new named range.

You don't need any code for the 'S1_' or 'FILE_DATA' worksheets.

Your HYPERLINK function looks right to me.

Once you've done all this, you will need to save the workbook as a macro enabled workbook (xlsm) and re-open the workbook so the startup script (in the code above) will save the file name to the named range 'DAT_FILE_NAME'. Of course, if the file name will never change (probably unlikely) you can just type the filename into the named range yourself. I prefer to use the startup script as you can then use that functionality in any other workbook. So if a user changes the name of your workbook, it will still work! Hope I made it more clear.

 

[rashl2]

hmmm, </SPAN>
</SPAN>
I’ve pasted the code; Private Sub Workbook_Open() etc into the ThisWorkbook object (got that thanks).</SPAN></SPAN>

I’ve pasted a column of student names into a worksheet called FILE_DATA. I’ve selected the range of names and named it DAT_FILE_NAME in the cell reference box. </SPAN></SPAN>

I have the formula; =HYPERLINK(CONCATENATE("[",DAT_FILE_NAME,"]", "S1_",A2,"!A1"),"Link")</SPAN></SPAN>
I’ve saved it as an xlsm, closed Excel and reopened the file.</SPAN></SPAN>

It now gives me an ‘error in value’ error. When I select the formula it highlights the A2 in blue. (A2 is the first name on the list in the FILE_DATA worksheet.) The worksheet FILE_DATA returns the spreadsheet filename all down the cells where I had pasted the student names. </SPAN></SPAN>

Thank you for your patience!</SPAN></SPAN>

 

[Excelot]

I’ve pasted a column of student names into a worksheet called FILE_DATA. I’ve selected the range of names and named it DAT_FILE_NAME in the cell reference box. </SPAN></SPAN>
</SPAN></SPAN>

Ok, you're so close!! It's probably best that you leave the list of names in a separate worksheet from FILE_DATA, in Sheet1 for example. For the purpose of testing this, all you want in the FILE_DATA worksheet is to pick one cell (say B1) and name that one cell 'DAT_FILE_NAME'. So the named range will only refer to that one cell (a range can be just one cell).

When the workbook opens, it will copy the workbook name to the named range 'DAT_FILE_NAME' which is cell B1 on worksheet FILE_DATA. So, when the HYPERLINK function references the named range 'DAT_FILE_NAME', it will pick up the workbook name stored to cell B1 in FILE_DATA.

I think that will work for you now.

Let me know how you go.

 

[rashl2]

Hi Excelot,

I now have one cell, B1 named as DAT_FILE_NAME in the sheet, FILE_DATA.

The spreadsheet shows an error before I open it in the 'recent files' list and opens straight to the FILE_DATA sheet showing the file name in B1.

The formula is now =HYPERLINK(CONCATENATE("[",DAT_FILE_NAME,"]", "S1_",A7,"!A1"),"Link") where A7 is the first student name on the list.
I'm no longer getting the 'error in value' error but when I select it, it pops up as 'reference not valid'. It still highlights the A7 in the formula bar as if this is an error.

I feel like I am close. but not quite there . .

 

[rashl2]

Ok, you're so close!! It's probably best that you leave the list of names in a separate worksheet from FILE_DATA, in Sheet1 for example. For the purpose of testing this, all you want in the FILE_DATA worksheet is to pick one cell (say B1) and name that one cell 'DAT_FILE_NAME'. So the named range will only refer to that one cell (a range can be just one cell).

When the workbook opens, it will copy the workbook name to the named range 'DAT_FILE_NAME' which is cell B1 on worksheet FILE_DATA. So, when the HYPERLINK function references the named range 'DAT_FILE_NAME', it will pick up the workbook name stored to cell B1 in FILE_DATA.

I think that will work for you now.

Let me know how you go.

Hi Excelot,

I now have one cell, B1 named as DAT_FILE_NAME in the sheet, FILE_DATA.

I’m putting this link into the sheet with the student list of names;</SPAN>
=HYPERLINK(CONCATENATE("[",DAT_FILE_NAME,"]",A7,"_S1","!A2"),"Link")</SPAN>
where;</SPAN>
A7 is the cell where the first student name appears (once I get this working, I can fill down for a full list of students),</SPAN>
“_S1” is what I’m adding to the student name to make up their first worksheet name (NAME, One_S1),</SPAN>
“!A2” is the cell in their worksheet that it should jump to and</SPAN>
“Link” is the text displayed in the link cell.</SPAN>

I swapped the A7 and "_S1" around to reflect the worksheet name but has the same result either way.
It looks right but I'm still getting the popup "Reference is not valid" and A7 is still highlighted in blue in the formula bar.

Not there yet . . .
</SPAN>

 

[Excelot]

It looks right but I'm still getting the popup "Reference is not valid" and A7 is still highlighted in blue in the formula bar.

Not there yet . . .
</SPAN>

Hi rashl2. I think you're getting the reference error because the worksheet name that you are using I'm guessing has spaces in it if you're creating the worksheet off of a name (e.g. firstname surname). The example I had given you is based on a worksheet that has no spaces in it's name. To account for the spaces, just adjust the hyperlink formula as below and it should now work.

=HYPERLINK(CONCATENATE("[",DAT_FILE_NAME,"]","'", A7,"_S1","'!A2"),"Link")

Basically, the difference is that we are wrapping the worksheet name between the single quote (') marks. This will account for spaces in the worksheet name. I would caution basing the worksheet names based on the name of the student as names are not unique. As I hinted at earlier, I would name the sheet based on some unique value such as a student ID, or perhaps a mixture of student name plus last 4 digits of their student ID for example.

BTW, the cell A7 being surrounded by a blue outline and coloured blue in the formula bar is just to visually way to illustrate what cells/ranges have been included in the forumula. I'm not sure what the error is you're getting. If in doubt, start again with a new workbook.