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 . . .

 

[rashl2]

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.

That's brilliant! It works! It was the space in the worksheet.
Thank you so much for your knowledge and patience.

Thank you to nigelk too as I think I have a use for the INDIRECT function.

 

[Excelot]

My pleasure. I'm glad we got it working .