Excel 2010 : linking cells across worksheets and still being able to sort
Hopefully this is possible without complex coding as I won't be the person curating this spreadsheet...
We have a large spreadsheet that is row upon row of case information, with each row representing a separate case. One of the columns in the information is "patient name". I've been asked if we can link that cell (patient name) from the main worksheet to a second worksheet (Summaries) that would simply be row after row of patient information with each patient being a separate row. So if you click on [Smith, John] in the main worksheet, you'll be taken to [Smith, John]'s row in the Summaries worksheet.
On the surface this appears simple. Hyperlink the [Smith, John] cell in the first worksheet to the corresponding cell in Summaries. However, names will be added as time goes on, and the Summaries sheet will need to be sorted alphabetically when that happens, so even though [Smith, John] might be Row 25 today, he could be row 40 in a week. Referencing Cell A25 from his entry on the main worksheet won't work, as next week A25 will have someone else's information.
So, how can I create a link from [Smith, John] on the main worksheet to [Smith, John] on the Summaries worksheet that will automatically adjust when the Summaries worksheet is resorted? I'm not sure if the main worksheet will ever be resorted, I suppose it's possible, so the link should stand up to that happening as well.
Impossible task? I can't seem to find a combination of hyperlink settings that work. Thanks for any suggestions you can provide.
We have a large spreadsheet that is row upon row of case information, with each row representing a separate case. One of the columns in the information is "patient name". I've been asked if we can link that cell (patient name) from the main worksheet to a second worksheet (Summaries) that would simply be row after row of patient information with each patient being a separate row. So if you click on [Smith, John] in the main worksheet, you'll be taken to [Smith, John]'s row in the Summaries worksheet.
On the surface this appears simple. Hyperlink the [Smith, John] cell in the first worksheet to the corresponding cell in Summaries. However, names will be added as time goes on, and the Summaries sheet will need to be sorted alphabetically when that happens, so even though [Smith, John] might be Row 25 today, he could be row 40 in a week. Referencing Cell A25 from his entry on the main worksheet won't work, as next week A25 will have someone else's information.
So, how can I create a link from [Smith, John] on the main worksheet to [Smith, John] on the Summaries worksheet that will automatically adjust when the Summaries worksheet is resorted? I'm not sure if the main worksheet will ever be resorted, I suppose it's possible, so the link should stand up to that happening as well.
Impossible task? I can't seem to find a combination of hyperlink settings that work. Thanks for any suggestions you can provide.
Martin Liss
Can you supply a sample workbook?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I am sure these are both valid solutions, however they are way beyond my level of Excel comprehension, and I have to turn this spreadsheet over to someone who knows very little about Excel beyond the basics, so I think the answer to my question is probably "It's possible, but too difficult to manage given the situation."
I really don't want to add VB Code to the spreadsheet, as nobody that touches it after me will have a clue what is happening. The use of INDIRECT when referencing cells looks like it might be workable, but I don't understand what is going on there from the description. Where do I enter the INDIRECT formula, and how does it reference the original cell and the row on the second sheet?
I really don't want to add VB Code to the spreadsheet, as nobody that touches it after me will have a clue what is happening. The use of INDIRECT when referencing cells looks like it might be workable, but I don't understand what is going on there from the description. Where do I enter the INDIRECT formula, and how does it reference the original cell and the row on the second sheet?
Remy,
What i'm basically doing find the row number where the value you are trying to find is present..Now once i find the row number i concatenate that with sheet1!a which is basically a address value written as text..Now indirect converts this entire value back from text to a range and refers to that range automatically which then i use in hyperlink to link your value..
Once you understand the logic you will know its not that difficult..Again you dont have to set the code in the workbook..You just have to use it once to set hyperlink by macro of the calculate name range..once the hyperlink is set..It will automatically works and will link you to the desired value what you are looking for..and post that you can remove the macro code since hyperlink will be still their and it will work when you enter a new value...
Saurabh...
What i'm basically doing find the row number where the value you are trying to find is present..Now once i find the row number i concatenate that with sheet1!a which is basically a address value written as text..Now indirect converts this entire value back from text to a range and refers to that range automatically which then i use in hyperlink to link your value..
Once you understand the logic you will know its not that difficult..Again you dont have to set the code in the workbook..You just have to use it once to set hyperlink by macro of the calculate name range..once the hyperlink is set..It will automatically works and will link you to the desired value what you are looking for..and post that you can remove the macro code since hyperlink will be still their and it will work when you enter a new value...
Saurabh...