Sign in
 
Home20132010Other VersionsLibraryForumsGallery
TechCenter >
> 
> 
Excel 2010, Hyperlink to a sheet with variable

Answered Excel 2010, Hyperlink to a sheet with variable

  • Monday, March 12, 2012 12:42 PM
     
     
    Sign In to Vote
    0

    I have a spreadsheet for tracking fundraising.

    I have a Summary sheet  and then I have the individual users users with their own sheets

    i.e.

    Sheet 1 = Summary
    Sheet 2 = Bob Smith
    Sheet 3 = Sally Sunshine
    etc.

    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!

     

All Replies

  • Monday, March 12, 2012 1:08 PM
     
     Answered
    Sign In to Vote
    1

    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:

    Sheet1!A1

    or

    'Sheet 1'!A1  (if sheetname contains a space)

    This cell reference then must be pre-pended with the hash character, like so:

    #'Sheet 1'!A1

    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
     
     
    Sign In to Vote
    1

    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
     
     
    Sign In to Vote
    1

    You're welcome!

    Regards, Jan Karel Pieterse|Excel MVP|http://www.jkp-ads.com
     
  • Thursday, June 21, 2012 1:06 PM
     
     
    Sign In to Vote
    0

    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.

    for example;

    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

    Thanks

     
  • Thursday, June 21, 2012 1:20 PM
     
     
    Sign In to Vote
    0

    Hi Ahmad,

    Have a look at the INDIRECT worksheet function. If your sheet name is in cell A1 then this formula shows the value of cell C3 of that sheet:

    =INDIRECT("'" & A1 & "'!C3")

    Regards, Jan Karel Pieterse|Excel MVP|http://www.jkp-ads.com