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.

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

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

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