Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Creating a link between cells in two different spreadsheets
Can it be done?!
I have a master spreadsheet which has a column called 'Corp Risk ID' which consists of numbers 1,2,3 etc - I then have a dependant spreadsheet which has a corresponding column called 'Corp Risk ID' so that I can compare how that 'Corp Risk ID' is used in both spreadsheets (run reports, see patterns of themes etc). For ease when I'm presenting this dependent spreadsheet, I would love to be able to click on a cell in the 'Corp Risk ID' column of the dependent spreadsheet and for it to take me to its corresponding cell in the 'Corp Risk ID' column of the master spreadsheet - has anybody else attempted this?! Gina. |
#2
|
|||
|
|||
How many of those IDs do you have?
If there's not too many, maybe you could use a hyperlink that points at that other location. (Insert|Hyperlink) If I had lots of IDs, I'd put a button from the forms toolbar in Row 1 (and freeze row 1 so that it's always visible when I scroll up/down the worksheet). Then I'd assign a macro to that button that worked off the location of the active cell. Option Explicit Sub testme() Dim myRng As Range Dim myCell As Range Dim myCol As Long Dim myDepCol As Long Dim DepWks As Worksheet Dim res As Variant 'Change these Set DepWks = Worksheets("dependent") 'what's the name myCol = 1 'column A myDepCol = 2 'Column B Set myCell = ActiveSheet.Cells(ActiveCell.Row, myCol) res = Application.Match(myCell.Value, DepWks.Columns(myDepCol), 0) If IsError(res) Then MsgBox ActiveCell.Value & " wasn't found on " & DepWks.Name Else Application.Goto DepWks.Cells(res, myDepCol), scroll:=True End If End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Gina O'Brien wrote: Can it be done?! I have a master spreadsheet which has a column called 'Corp Risk ID' which consists of numbers 1,2,3 etc - I then have a dependant spreadsheet which has a corresponding column called 'Corp Risk ID' so that I can compare how that 'Corp Risk ID' is used in both spreadsheets (run reports, see patterns of themes etc). For ease when I'm presenting this dependent spreadsheet, I would love to be able to click on a cell in the 'Corp Risk ID' column of the dependent spreadsheet and for it to take me to its corresponding cell in the 'Corp Risk ID' column of the master spreadsheet - has anybody else attempted this?! Gina. -- Dave Peterson |
#3
|
|||
|
|||
Dave
I will only have 1 ID - so far example - '8' in the dependant spreadsheet correlates to '8' in the master spreadsheet. So, if I click on '8' in the dependent spreadsheet when I'm presenting I want it to be able to open up the master spreadsheet at '8' so that I can highlight the difference between how ID 8 is being used in the 23 spreadsheets. (To explain further, the ID is a numeric coded system which correlates to objectives e.g. 8 = meet planning targets. So the very existence of 8 in my dependent spreadhseet shows that team have highlighted it as a priority, 8 in the master spreadsheet shows how the corporation have highlighted it as a priority. Another dependant spreadsheet might not have 8 identified at all and by its absence shows that team does not see it as a priority. Can you create hyperlinks between spreadsheets to do this? Gina "Dave Peterson" wrote: How many of those IDs do you have? If there's not too many, maybe you could use a hyperlink that points at that other location. (Insert|Hyperlink) If I had lots of IDs, I'd put a button from the forms toolbar in Row 1 (and freeze row 1 so that it's always visible when I scroll up/down the worksheet). Then I'd assign a macro to that button that worked off the location of the active cell. Option Explicit Sub testme() Dim myRng As Range Dim myCell As Range Dim myCol As Long Dim myDepCol As Long Dim DepWks As Worksheet Dim res As Variant 'Change these Set DepWks = Worksheets("dependent") 'what's the name myCol = 1 'column A myDepCol = 2 'Column B Set myCell = ActiveSheet.Cells(ActiveCell.Row, myCol) res = Application.Match(myCell.Value, DepWks.Columns(myDepCol), 0) If IsError(res) Then MsgBox ActiveCell.Value & " wasn't found on " & DepWks.Name Else Application.Goto DepWks.Cells(res, myDepCol), scroll:=True End If End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Gina O'Brien wrote: Can it be done?! I have a master spreadsheet which has a column called 'Corp Risk ID' which consists of numbers 1,2,3 etc - I then have a dependant spreadsheet which has a corresponding column called 'Corp Risk ID' so that I can compare how that 'Corp Risk ID' is used in both spreadsheets (run reports, see patterns of themes etc). For ease when I'm presenting this dependent spreadsheet, I would love to be able to click on a cell in the 'Corp Risk ID' column of the dependent spreadsheet and for it to take me to its corresponding cell in the 'Corp Risk ID' column of the master spreadsheet - has anybody else attempted this?! Gina. -- Dave Peterson |
#4
|
|||
|
|||
Select the cell with the ID and then do Insert|Hyperlink.
Follow that dialog to go to the other worksheet. Gina O'Brien wrote: Dave I will only have 1 ID - so far example - '8' in the dependant spreadsheet correlates to '8' in the master spreadsheet. So, if I click on '8' in the dependent spreadsheet when I'm presenting I want it to be able to open up the master spreadsheet at '8' so that I can highlight the difference between how ID 8 is being used in the 23 spreadsheets. (To explain further, the ID is a numeric coded system which correlates to objectives e.g. 8 = meet planning targets. So the very existence of 8 in my dependent spreadhseet shows that team have highlighted it as a priority, 8 in the master spreadsheet shows how the corporation have highlighted it as a priority. Another dependant spreadsheet might not have 8 identified at all and by its absence shows that team does not see it as a priority. Can you create hyperlinks between spreadsheets to do this? Gina "Dave Peterson" wrote: How many of those IDs do you have? If there's not too many, maybe you could use a hyperlink that points at that other location. (Insert|Hyperlink) If I had lots of IDs, I'd put a button from the forms toolbar in Row 1 (and freeze row 1 so that it's always visible when I scroll up/down the worksheet). Then I'd assign a macro to that button that worked off the location of the active cell. Option Explicit Sub testme() Dim myRng As Range Dim myCell As Range Dim myCol As Long Dim myDepCol As Long Dim DepWks As Worksheet Dim res As Variant 'Change these Set DepWks = Worksheets("dependent") 'what's the name myCol = 1 'column A myDepCol = 2 'Column B Set myCell = ActiveSheet.Cells(ActiveCell.Row, myCol) res = Application.Match(myCell.Value, DepWks.Columns(myDepCol), 0) If IsError(res) Then MsgBox ActiveCell.Value & " wasn't found on " & DepWks.Name Else Application.Goto DepWks.Cells(res, myDepCol), scroll:=True End If End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Gina O'Brien wrote: Can it be done?! I have a master spreadsheet which has a column called 'Corp Risk ID' which consists of numbers 1,2,3 etc - I then have a dependant spreadsheet which has a corresponding column called 'Corp Risk ID' so that I can compare how that 'Corp Risk ID' is used in both spreadsheets (run reports, see patterns of themes etc). For ease when I'm presenting this dependent spreadsheet, I would love to be able to click on a cell in the 'Corp Risk ID' column of the dependent spreadsheet and for it to take me to its corresponding cell in the 'Corp Risk ID' column of the master spreadsheet - has anybody else attempted this?! Gina. -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I link cells, sheet to sheet, to recognize row deletions? | Excel Discussion (Misc queries) | |||
How to link website data to Excel cells | Excel Discussion (Misc queries) | |||
Can you Link 3 cells into 1 in Excel 2003 | Excel Worksheet Functions | |||
Formulas link cells | Excel Worksheet Functions | |||
How do I link two cells as to allow me to change the value in eit. | Excel Discussion (Misc queries) |