Monday, March 12, 2012 12:42 PM
I have a spreadsheet for tracking fundraising.
I have a Summary sheet and then I have the individual users users with their own sheets
Sheet 1 = Summary
Sheet 2 = Bob Smith
Sheet 3 = Sally Sunshine
I have a marco that will rename each sheet based off of the value in Summary:B6:B30
i.e. in B6 is the text "Bob Smith" that relates to Sheet 2 which is automatically renamed "Bob Smith"
I have 5 "Person 19" "Person 20" "Person 21" etc Those sheets are named "Person 19".... etc
I have to manually re-create the hyper-link that I have in Cell A6 if their name changes (i.e. married, or changing Person 19 to an actual name)
How can I have the Hyperlink be dynamic?
the link in there today is this: \samplefile.xlsm - 'Betty Frank'!A1">file:///<path>\samplefile.xlsm - 'Betty Frank'!A1
I've been fighting this for a long time!!!! Any help is much appreciated!
Monday, March 12, 2012 1:08 PM
You can use the HYPERLINK function to create a dynamic hyperlink.
The trick you need to know of is two-fold:
- the first argument of the function must look exactly like an off-sheet cell reference in a formula:
'Sheet 1'!A1 (if sheetname contains a space)
This cell reference then must be pre-pended with the hash character, like so:
So suppose your sheetname is in cell A1, then this formula will create a hyperlink to cell A1 of that sheet. The friendlyname will show the sheetname:
=HYPERLINK("#'" & A1 & "'!A1",A1)
Regards, Jan Karel Pieterse|Excel MVP|http://www.jkp-ads.com
- Marked As Answer by scottrm20 Monday, March 12, 2012 2:25 PM
Monday, March 12, 2012 2:25 PM
THANK YOU THANK YOU THANK YOU!
That worked perfectly! Save's me a ton of extra work! Thank you so much!
Monday, March 12, 2012 3:33 PM
Thursday, June 21, 2012 1:06 PM
What about if I nees to make summary e.g. in sheet 1 and I need to collect data from the same excell page, but from sheet 2,3,4,5,..... from one fixed cell.
in the sammry sheet( sheet1) from cell C11 up to Z11 i want to put the data for cell H11 from all sheet(2,3,4,5,,.....)
that is means I want to fix the cell and the sheet will be the variable
I need the answer ASAP, please
Thursday, June 21, 2012 1:20 PM