Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
link a cell in the 'Master' worksheet list to a 'Detail' worksheet
I have a 'Master' worksheet containing a list of the other worksheets in this
workbook. The list will be added to regularly. I want to create a macro that when run will take the worksheet name that is highlighted in the 'master' list and GoTo cell A1 on the related 'Detail' sheet. Any help would be much appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
link a cell in the 'Master' worksheet list to a 'Detail' worksheet
=INDIRECT("'"&A2&"'!A1)
where A2 holds the sheet name -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Paul Condron" wrote in message ... I have a 'Master' worksheet containing a list of the other worksheets in this workbook. The list will be added to regularly. I want to create a macro that when run will take the worksheet name that is highlighted in the 'master' list and GoTo cell A1 on the related 'Detail' sheet. Any help would be much appreciated. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
link a cell in the 'Master' worksheet list to a 'Detail' works
Bob,
Many thanks but I can't get this to work. I have played around with the " ' and ( ) but I can't seem to get the logic right. I also tried to insert a , before A1 but that didn't seem to help either. I'm probably doing something stupid! Regards Paul "Bob Phillips" wrote: =INDIRECT("'"&A2&"'!A1) where A2 holds the sheet name -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Paul Condron" wrote in message ... I have a 'Master' worksheet containing a list of the other worksheets in this workbook. The list will be added to regularly. I want to create a macro that when run will take the worksheet name that is highlighted in the 'master' list and GoTo cell A1 on the related 'Detail' sheet. Any help would be much appreciated. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
link a cell in the 'Master' worksheet list to a 'Detail' works
Paul,
The syntax is =INDIRECT(double-quote single-quote double-quote & A2 & double-quote single-quote double-quote ! A1 ) "Paul Condron" wrote in message ... Bob, Many thanks but I can't get this to work. I have played around with the " ' and ( ) but I can't seem to get the logic right. I also tried to insert a , before A1 but that didn't seem to help either. I'm probably doing something stupid! Regards Paul "Bob Phillips" wrote: =INDIRECT("'"&A2&"'!A1) where A2 holds the sheet name -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Paul Condron" wrote in message ... I have a 'Master' worksheet containing a list of the other worksheets in this workbook. The list will be added to regularly. I want to create a macro that when run will take the worksheet name that is highlighted in the 'master' list and GoTo cell A1 on the related 'Detail' sheet. Any help would be much appreciated. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
link a cell in the 'Master' worksheet list to a 'Detail' works
Chip,
Thanks. I followed this exactly - I think but it still tells me I have an error. The particular worksheet I tried it on is labelled - Accel_Lifts - and so I got: =INDIRECT("'"&Accel_Lifts&"'"!A1) But it comes up as a syntax error! Paul "Chip Pearson" wrote: Paul, The syntax is =INDIRECT(double-quote single-quote double-quote & A2 & double-quote single-quote double-quote ! A1 ) "Paul Condron" wrote in message ... Bob, Many thanks but I can't get this to work. I have played around with the " ' and ( ) but I can't seem to get the logic right. I also tried to insert a , before A1 but that didn't seem to help either. I'm probably doing something stupid! Regards Paul "Bob Phillips" wrote: =INDIRECT("'"&A2&"'!A1) where A2 holds the sheet name -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Paul Condron" wrote in message ... I have a 'Master' worksheet containing a list of the other worksheets in this workbook. The list will be added to regularly. I want to create a macro that when run will take the worksheet name that is highlighted in the 'master' list and GoTo cell A1 on the related 'Detail' sheet. Any help would be much appreciated. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
link a cell in the 'Master' worksheet list to a 'Detail' works
I omitted the trailing quote. You don't need to substitute the cell value,
Excel will do that, so use =INDIRECT("'"&A2&"'!A1") -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Paul Condron" wrote in message ... Chip, Thanks. I followed this exactly - I think but it still tells me I have an error. The particular worksheet I tried it on is labelled - Accel_Lifts - and so I got: =INDIRECT("'"&Accel_Lifts&"'"!A1) But it comes up as a syntax error! Paul "Chip Pearson" wrote: Paul, The syntax is =INDIRECT(double-quote single-quote double-quote & A2 & double-quote single-quote double-quote ! A1 ) "Paul Condron" wrote in message ... Bob, Many thanks but I can't get this to work. I have played around with the " ' and ( ) but I can't seem to get the logic right. I also tried to insert a , before A1 but that didn't seem to help either. I'm probably doing something stupid! Regards Paul "Bob Phillips" wrote: =INDIRECT("'"&A2&"'!A1) where A2 holds the sheet name -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Paul Condron" wrote in message ... I have a 'Master' worksheet containing a list of the other worksheets in this workbook. The list will be added to regularly. I want to create a macro that when run will take the worksheet name that is highlighted in the 'master' list and GoTo cell A1 on the related 'Detail' sheet. Any help would be much appreciated. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
link a cell in the 'Master' worksheet list to a 'Detail' works
Bob,
thanks again but I obviously haven't explained my problem correctly and what you sent me won't work. I'll try again because this is really getting to me (and hope you still have the patience to reply!!)!! On Worksheet 1 - my 'Master' or 'Front-end' WS, I have a listing of the companies whose details will be shown on other Worksheets within this Workbook. This list has 4 columns and I want one specific one of these to be a link to its associated Worksheet so that when I reference this cell in a GoTo macro, it will go to cell A1 of that particular Worksheet. I'm assuming that if the macro is created as 'relative' that it will take me to the sheet that relates to the list entry being highlighted on the 'Master' WS. The list on the Master WS will be expanded as time goes on and so this automatic link to the associated detail sheet is a key part to making it work. Once again, my thanks for any help I get. Paul "Bob Phillips" wrote: I omitted the trailing quote. You don't need to substitute the cell value, Excel will do that, so use =INDIRECT("'"&A2&"'!A1") -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Paul Condron" wrote in message ... Chip, Thanks. I followed this exactly - I think but it still tells me I have an error. The particular worksheet I tried it on is labelled - Accel_Lifts - and so I got: =INDIRECT("'"&Accel_Lifts&"'"!A1) But it comes up as a syntax error! Paul "Chip Pearson" wrote: Paul, The syntax is =INDIRECT(double-quote single-quote double-quote & A2 & double-quote single-quote double-quote ! A1 ) "Paul Condron" wrote in message ... Bob, Many thanks but I can't get this to work. I have played around with the " ' and ( ) but I can't seem to get the logic right. I also tried to insert a , before A1 but that didn't seem to help either. I'm probably doing something stupid! Regards Paul "Bob Phillips" wrote: =INDIRECT("'"&A2&"'!A1) where A2 holds the sheet name -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Paul Condron" wrote in message ... I have a 'Master' worksheet containing a list of the other worksheets in this workbook. The list will be added to regularly. I want to create a macro that when run will take the worksheet name that is highlighted in the 'master' list and GoTo cell A1 on the related 'Detail' sheet. Any help would be much appreciated. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
link a cell in the 'Master' worksheet list to a 'Detail' works
Paul,
Maybe you need an event macro that works upon selecting a cell with a sheet name in it. I have used columns C:F for those names, you can change to suit. 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. Private Sub Worksheet_SelectionChange(ByVal Target As Range) Const WS_RANGE As String = "C:F" If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then If Target.Value < "" Then Worksheets(Tragte.Value).Activate ActiveSheet.Range("A1").Select End If End If End Sub -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Paul Condron" wrote in message ... Bob, thanks again but I obviously haven't explained my problem correctly and what you sent me won't work. I'll try again because this is really getting to me (and hope you still have the patience to reply!!)!! On Worksheet 1 - my 'Master' or 'Front-end' WS, I have a listing of the companies whose details will be shown on other Worksheets within this Workbook. This list has 4 columns and I want one specific one of these to be a link to its associated Worksheet so that when I reference this cell in a GoTo macro, it will go to cell A1 of that particular Worksheet. I'm assuming that if the macro is created as 'relative' that it will take me to the sheet that relates to the list entry being highlighted on the 'Master' WS. The list on the Master WS will be expanded as time goes on and so this automatic link to the associated detail sheet is a key part to making it work. Once again, my thanks for any help I get. Paul "Bob Phillips" wrote: I omitted the trailing quote. You don't need to substitute the cell value, Excel will do that, so use =INDIRECT("'"&A2&"'!A1") -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Paul Condron" wrote in message ... Chip, Thanks. I followed this exactly - I think but it still tells me I have an error. The particular worksheet I tried it on is labelled - Accel_Lifts - and so I got: =INDIRECT("'"&Accel_Lifts&"'"!A1) But it comes up as a syntax error! Paul "Chip Pearson" wrote: Paul, The syntax is =INDIRECT(double-quote single-quote double-quote & A2 & double-quote single-quote double-quote ! A1 ) "Paul Condron" wrote in message ... Bob, Many thanks but I can't get this to work. I have played around with the " ' and ( ) but I can't seem to get the logic right. I also tried to insert a , before A1 but that didn't seem to help either. I'm probably doing something stupid! Regards Paul "Bob Phillips" wrote: =INDIRECT("'"&A2&"'!A1) where A2 holds the sheet name -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Paul Condron" wrote in message ... I have a 'Master' worksheet containing a list of the other worksheets in this workbook. The list will be added to regularly. I want to create a macro that when run will take the worksheet name that is highlighted in the 'master' list and GoTo cell A1 on the related 'Detail' sheet. Any help would be much appreciated. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
link a cell in the 'Master' worksheet list to a 'Detail' works
Bob,
Thank you. I will play with this until I get it right. You have been a great help. Paul "Bob Phillips" wrote: Paul, Maybe you need an event macro that works upon selecting a cell with a sheet name in it. I have used columns C:F for those names, you can change to suit. 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. Private Sub Worksheet_SelectionChange(ByVal Target As Range) Const WS_RANGE As String = "C:F" If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then If Target.Value < "" Then Worksheets(Tragte.Value).Activate ActiveSheet.Range("A1").Select End If End If End Sub -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Paul Condron" wrote in message ... Bob, thanks again but I obviously haven't explained my problem correctly and what you sent me won't work. I'll try again because this is really getting to me (and hope you still have the patience to reply!!)!! On Worksheet 1 - my 'Master' or 'Front-end' WS, I have a listing of the companies whose details will be shown on other Worksheets within this Workbook. This list has 4 columns and I want one specific one of these to be a link to its associated Worksheet so that when I reference this cell in a GoTo macro, it will go to cell A1 of that particular Worksheet. I'm assuming that if the macro is created as 'relative' that it will take me to the sheet that relates to the list entry being highlighted on the 'Master' WS. The list on the Master WS will be expanded as time goes on and so this automatic link to the associated detail sheet is a key part to making it work. Once again, my thanks for any help I get. Paul "Bob Phillips" wrote: I omitted the trailing quote. You don't need to substitute the cell value, Excel will do that, so use =INDIRECT("'"&A2&"'!A1") -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Paul Condron" wrote in message ... Chip, Thanks. I followed this exactly - I think but it still tells me I have an error. The particular worksheet I tried it on is labelled - Accel_Lifts - and so I got: =INDIRECT("'"&Accel_Lifts&"'"!A1) But it comes up as a syntax error! Paul "Chip Pearson" wrote: Paul, The syntax is =INDIRECT(double-quote single-quote double-quote & A2 & double-quote single-quote double-quote ! A1 ) "Paul Condron" wrote in message ... Bob, Many thanks but I can't get this to work. I have played around with the " ' and ( ) but I can't seem to get the logic right. I also tried to insert a , before A1 but that didn't seem to help either. I'm probably doing something stupid! Regards Paul "Bob Phillips" wrote: =INDIRECT("'"&A2&"'!A1) where A2 holds the sheet name -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Paul Condron" wrote in message ... I have a 'Master' worksheet containing a list of the other worksheets in this workbook. The list will be added to regularly. I want to create a macro that when run will take the worksheet name that is highlighted in the 'master' list and GoTo cell A1 on the related 'Detail' sheet. Any help would be much appreciated. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
link a cell in the 'Master' worksheet list to a 'Detail' works
Typo
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "Bob Phillips" wrote in message ... Paul, Maybe you need an event macro that works upon selecting a cell with a sheet name in it. I have used columns C:F for those names, you can change to suit. 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. Private Sub Worksheet_SelectionChange(ByVal Target As Range) Const WS_RANGE As String = "C:F" If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then If Target.Value < "" Then Worksheets(Tragte.Value).Activate ActiveSheet.Range("A1").Select End If End If End Sub -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Paul Condron" wrote in message ... Bob, thanks again but I obviously haven't explained my problem correctly and what you sent me won't work. I'll try again because this is really getting to me (and hope you still have the patience to reply!!)!! On Worksheet 1 - my 'Master' or 'Front-end' WS, I have a listing of the companies whose details will be shown on other Worksheets within this Workbook. This list has 4 columns and I want one specific one of these to be a link to its associated Worksheet so that when I reference this cell in a GoTo macro, it will go to cell A1 of that particular Worksheet. I'm assuming that if the macro is created as 'relative' that it will take me to the sheet that relates to the list entry being highlighted on the 'Master' WS. The list on the Master WS will be expanded as time goes on and so this automatic link to the associated detail sheet is a key part to making it work. Once again, my thanks for any help I get. Paul "Bob Phillips" wrote: I omitted the trailing quote. You don't need to substitute the cell value, Excel will do that, so use =INDIRECT("'"&A2&"'!A1") -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Paul Condron" wrote in message ... Chip, Thanks. I followed this exactly - I think but it still tells me I have an error. The particular worksheet I tried it on is labelled - Accel_Lifts - and so I got: =INDIRECT("'"&Accel_Lifts&"'"!A1) But it comes up as a syntax error! Paul "Chip Pearson" wrote: Paul, The syntax is =INDIRECT(double-quote single-quote double-quote & A2 & double-quote single-quote double-quote ! A1 ) "Paul Condron" wrote in message ... Bob, Many thanks but I can't get this to work. I have played around with the " ' and ( ) but I can't seem to get the logic right. I also tried to insert a , before A1 but that didn't seem to help either. I'm probably doing something stupid! Regards Paul "Bob Phillips" wrote: =INDIRECT("'"&A2&"'!A1) where A2 holds the sheet name -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Paul Condron" wrote in message ... I have a 'Master' worksheet containing a list of the other worksheets in this workbook. The list will be added to regularly. I want to create a macro that when run will take the worksheet name that is highlighted in the 'master' list and GoTo cell A1 on the related 'Detail' sheet. Any help would be much appreciated. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
link a cell in the 'Master' worksheet list to a 'Detail' works
Typo
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Const WS_RANGE As String = "C:F" If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then If Target.Value < "" Then Worksheets(Target.Value).Activate ActiveSheet.Range("A1").Select End If End If End Sub -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Bob Phillips" wrote in message ... Paul, Maybe you need an event macro that works upon selecting a cell with a sheet name in it. I have used columns C:F for those names, you can change to suit. 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. Private Sub Worksheet_SelectionChange(ByVal Target As Range) Const WS_RANGE As String = "C:F" If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then If Target.Value < "" Then Worksheets(Tragte.Value).Activate ActiveSheet.Range("A1").Select End If End If End Sub -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Paul Condron" wrote in message ... Bob, thanks again but I obviously haven't explained my problem correctly and what you sent me won't work. I'll try again because this is really getting to me (and hope you still have the patience to reply!!)!! On Worksheet 1 - my 'Master' or 'Front-end' WS, I have a listing of the companies whose details will be shown on other Worksheets within this Workbook. This list has 4 columns and I want one specific one of these to be a link to its associated Worksheet so that when I reference this cell in a GoTo macro, it will go to cell A1 of that particular Worksheet. I'm assuming that if the macro is created as 'relative' that it will take me to the sheet that relates to the list entry being highlighted on the 'Master' WS. The list on the Master WS will be expanded as time goes on and so this automatic link to the associated detail sheet is a key part to making it work. Once again, my thanks for any help I get. Paul "Bob Phillips" wrote: I omitted the trailing quote. You don't need to substitute the cell value, Excel will do that, so use =INDIRECT("'"&A2&"'!A1") -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Paul Condron" wrote in message ... Chip, Thanks. I followed this exactly - I think but it still tells me I have an error. The particular worksheet I tried it on is labelled - Accel_Lifts - and so I got: =INDIRECT("'"&Accel_Lifts&"'"!A1) But it comes up as a syntax error! Paul "Chip Pearson" wrote: Paul, The syntax is =INDIRECT(double-quote single-quote double-quote & A2 & double-quote single-quote double-quote ! A1 ) "Paul Condron" wrote in message ... Bob, Many thanks but I can't get this to work. I have played around with the " ' and ( ) but I can't seem to get the logic right. I also tried to insert a , before A1 but that didn't seem to help either. I'm probably doing something stupid! Regards Paul "Bob Phillips" wrote: =INDIRECT("'"&A2&"'!A1) where A2 holds the sheet name -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Paul Condron" wrote in message ... I have a 'Master' worksheet containing a list of the other worksheets in this workbook. The list will be added to regularly. I want to create a macro that when run will take the worksheet name that is highlighted in the 'master' list and GoTo cell A1 on the related 'Detail' sheet. Any help would be much appreciated. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
link a cell in the 'Master' worksheet list to a 'Detail' works
Hey guys I like this code but I need it to look at just Column B for teh
Sheet names. "Bob Phillips" wrote: Typo Private Sub Worksheet_SelectionChange(ByVal Target As Range) Const WS_RANGE As String = "C:F" If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then If Target.Value < "" Then Worksheets(Target.Value).Activate ActiveSheet.Range("A1").Select End If End If End Sub -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Bob Phillips" wrote in message ... Paul, Maybe you need an event macro that works upon selecting a cell with a sheet name in it. I have used columns C:F for those names, you can change to suit. 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. Private Sub Worksheet_SelectionChange(ByVal Target As Range) Const WS_RANGE As String = "C:F" If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then If Target.Value < "" Then Worksheets(Tragte.Value).Activate ActiveSheet.Range("A1").Select End If End If End Sub -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Paul Condron" wrote in message ... Bob, thanks again but I obviously haven't explained my problem correctly and what you sent me won't work. I'll try again because this is really getting to me (and hope you still have the patience to reply!!)!! On Worksheet 1 - my 'Master' or 'Front-end' WS, I have a listing of the companies whose details will be shown on other Worksheets within this Workbook. This list has 4 columns and I want one specific one of these to be a link to its associated Worksheet so that when I reference this cell in a GoTo macro, it will go to cell A1 of that particular Worksheet. I'm assuming that if the macro is created as 'relative' that it will take me to the sheet that relates to the list entry being highlighted on the 'Master' WS. The list on the Master WS will be expanded as time goes on and so this automatic link to the associated detail sheet is a key part to making it work. Once again, my thanks for any help I get. Paul "Bob Phillips" wrote: I omitted the trailing quote. You don't need to substitute the cell value, Excel will do that, so use =INDIRECT("'"&A2&"'!A1") -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Paul Condron" wrote in message ... Chip, Thanks. I followed this exactly - I think but it still tells me I have an error. The particular worksheet I tried it on is labelled - Accel_Lifts - and so I got: =INDIRECT("'"&Accel_Lifts&"'"!A1) But it comes up as a syntax error! Paul "Chip Pearson" wrote: Paul, The syntax is =INDIRECT(double-quote single-quote double-quote & A2 & double-quote single-quote double-quote ! A1 ) "Paul Condron" wrote in message ... Bob, Many thanks but I can't get this to work. I have played around with the " ' and ( ) but I can't seem to get the logic right. I also tried to insert a , before A1 but that didn't seem to help either. I'm probably doing something stupid! Regards Paul "Bob Phillips" wrote: =INDIRECT("'"&A2&"'!A1) where A2 holds the sheet name -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Paul Condron" wrote in message ... I have a 'Master' worksheet containing a list of the other worksheets in this workbook. The list will be added to regularly. I want to create a macro that when run will take the worksheet name that is highlighted in the 'master' list and GoTo cell A1 on the related 'Detail' sheet. Any help would be much appreciated. |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
link a cell in the 'Master' worksheet list to a 'Detail' works
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WS_RANGE As String = "B:B" If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then If Target.Value < "" Then Worksheets(Target.Value).Activate ActiveSheet.Range("A1").Select End If End If End Sub -- Regards, Tom Ogilvy "Mike Punko" wrote in message ... Hey guys I like this code but I need it to look at just Column B for teh Sheet names. "Bob Phillips" wrote: Typo Private Sub Worksheet_SelectionChange(ByVal Target As Range) Const WS_RANGE As String = "C:F" If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then If Target.Value < "" Then Worksheets(Target.Value).Activate ActiveSheet.Range("A1").Select End If End If End Sub -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Bob Phillips" wrote in message ... Paul, Maybe you need an event macro that works upon selecting a cell with a sheet name in it. I have used columns C:F for those names, you can change to suit. 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. Private Sub Worksheet_SelectionChange(ByVal Target As Range) Const WS_RANGE As String = "C:F" If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then If Target.Value < "" Then Worksheets(Tragte.Value).Activate ActiveSheet.Range("A1").Select End If End If End Sub -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Paul Condron" wrote in message ... Bob, thanks again but I obviously haven't explained my problem correctly and what you sent me won't work. I'll try again because this is really getting to me (and hope you still have the patience to reply!!)!! On Worksheet 1 - my 'Master' or 'Front-end' WS, I have a listing of the companies whose details will be shown on other Worksheets within this Workbook. This list has 4 columns and I want one specific one of these to be a link to its associated Worksheet so that when I reference this cell in a GoTo macro, it will go to cell A1 of that particular Worksheet. I'm assuming that if the macro is created as 'relative' that it will take me to the sheet that relates to the list entry being highlighted on the 'Master' WS. The list on the Master WS will be expanded as time goes on and so this automatic link to the associated detail sheet is a key part to making it work. Once again, my thanks for any help I get. Paul "Bob Phillips" wrote: I omitted the trailing quote. You don't need to substitute the cell value, Excel will do that, so use =INDIRECT("'"&A2&"'!A1") -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Paul Condron" wrote in message ... Chip, Thanks. I followed this exactly - I think but it still tells me I have an error. The particular worksheet I tried it on is labelled - Accel_Lifts - and so I got: =INDIRECT("'"&Accel_Lifts&"'"!A1) But it comes up as a syntax error! Paul "Chip Pearson" wrote: Paul, The syntax is =INDIRECT(double-quote single-quote double-quote & A2 & double-quote single-quote double-quote ! A1 ) "Paul Condron" wrote in message ... Bob, Many thanks but I can't get this to work. I have played around with the " ' and ( ) but I can't seem to get the logic right. I also tried to insert a , before A1 but that didn't seem to help either. I'm probably doing something stupid! Regards Paul "Bob Phillips" wrote: =INDIRECT("'"&A2&"'!A1) where A2 holds the sheet name -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Paul Condron" wrote in message ... I have a 'Master' worksheet containing a list of the other worksheets in this workbook. The list will be added to regularly. I want to create a macro that when run will take the worksheet name that is highlighted in the 'master' list and GoTo cell A1 on the related 'Detail' sheet. Any help would be much appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
automatically appending newly added data on worksheet to a master list worksheet | Links and Linking in Excel | |||
How can I pull Cell value from Detail worksheet into Summary sheet? | Excel Worksheet Functions | |||
Summary worksheet reference to detail worksheet | New Users to Excel | |||
How can I link cell colours from worksheet to worksheet/workbook? | Excel Worksheet Functions | |||
how do i link a number of worksheets to one master worksheet? | Excel Worksheet Functions |