Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sheet tab name to reference cell data
I have a Workbook with several (16) sheets in it. I want the name of sheet 2
to reflect the info I have typed into cell B3 in sheet 1; sheet 3 to reflect cell C3 in sheet 1, sheet 4 to reflect D3, etc. If I change the data in B3, I want Sheet 2 to rename itself. (Also, I use Excel 2000 - if that makes a difference) I saw a question earlier that I thought answered this but can't figure out how to do it. I don't know anything about macros or VBA so maybe I'm just doing it wrong and not changing the right data in the formula. Please explain in detail what formula I need, where to put it, and what data in the formula I need to change to suit my individual project. Thank you very much for any assistance I can get. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sheet tab name to reference cell data
Change to
Sheets(Target.Column-1).Name = Target -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... Right click the sheet tabview codeinsert this. Now when you change a cell in row 3 the appropriate sheet will be re-named accordingly. Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Target.Row < 3 Then Exit Sub 'MsgBox Target.Column Sheets(Target.Column).Name = Target End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Regina J." wrote in message ... I have a Workbook with several (16) sheets in it. I want the name of sheet 2 to reflect the info I have typed into cell B3 in sheet 1; sheet 3 to reflect cell C3 in sheet 1, sheet 4 to reflect D3, etc. If I change the data in B3, I want Sheet 2 to rename itself. (Also, I use Excel 2000 - if that makes a difference) I saw a question earlier that I thought answered this but can't figure out how to do it. I don't know anything about macros or VBA so maybe I'm just doing it wrong and not changing the right data in the formula. Please explain in detail what formula I need, where to put it, and what data in the formula I need to change to suit my individual project. Thank you very much for any assistance I can get. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sheet tab name to reference cell data
Nothing happened. I went to sheet 2, did the view code and that's where I
put it. Then I went back to sheet one and changed the data in B3 to check it and nothing happened. Also, I want to note that not all my data will be in row 3. Sheet 7 - 11 data will be in row 25, sheet 12 - 16 data wil be in row 47. "Don Guillett" wrote: Change to Sheets(Target.Column-1).Name = Target -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... Right click the sheet tabview codeinsert this. Now when you change a cell in row 3 the appropriate sheet will be re-named accordingly. Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Target.Row < 3 Then Exit Sub 'MsgBox Target.Column Sheets(Target.Column).Name = Target End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Regina J." wrote in message ... I have a Workbook with several (16) sheets in it. I want the name of sheet 2 to reflect the info I have typed into cell B3 in sheet 1; sheet 3 to reflect cell C3 in sheet 1, sheet 4 to reflect D3, etc. If I change the data in B3, I want Sheet 2 to rename itself. (Also, I use Excel 2000 - if that makes a difference) I saw a question earlier that I thought answered this but can't figure out how to do it. I don't know anything about macros or VBA so maybe I'm just doing it wrong and not changing the right data in the formula. Please explain in detail what formula I need, where to put it, and what data in the formula I need to change to suit my individual project. Thank you very much for any assistance I can get. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sheet tab name to reference cell data
I don't have xl2000 so don't know if that is the problem. I fully tested the
solution. Did you use the entire sub, as written? Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Target.Row < 3 Then Exit Sub 'MsgBox Target.Column Sheets(Target.Column-1).Name = Target End Sub BTW, You should ALWAYS state your problem (not just PART of it) in the OP. I suspect you STILL have not fully stated your problem. modify to suit if target.row<3 AND target.row< Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Target.Row < 3 And Target.Row < 7 Then Exit Sub 'MsgBox Target.Column Sheets(Target.Column - 1).Name = Target End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Regina J." wrote in message ... Nothing happened. I went to sheet 2, did the view code and that's where I put it. Then I went back to sheet one and changed the data in B3 to check it and nothing happened. Also, I want to note that not all my data will be in row 3. Sheet 7 - 11 data will be in row 25, sheet 12 - 16 data wil be in row 47. "Don Guillett" wrote: Change to Sheets(Target.Column-1).Name = Target -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... Right click the sheet tabview codeinsert this. Now when you change a cell in row 3 the appropriate sheet will be re-named accordingly. Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Target.Row < 3 Then Exit Sub 'MsgBox Target.Column Sheets(Target.Column).Name = Target End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Regina J." wrote in message ... I have a Workbook with several (16) sheets in it. I want the name of sheet 2 to reflect the info I have typed into cell B3 in sheet 1; sheet 3 to reflect cell C3 in sheet 1, sheet 4 to reflect D3, etc. If I change the data in B3, I want Sheet 2 to rename itself. (Also, I use Excel 2000 - if that makes a difference) I saw a question earlier that I thought answered this but can't figure out how to do it. I don't know anything about macros or VBA so maybe I'm just doing it wrong and not changing the right data in the formula. Please explain in detail what formula I need, where to put it, and what data in the formula I need to change to suit my individual project. Thank you very much for any assistance I can get. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sheet tab name to reference cell data
Yes I did use the entire sub as you had it typed even corrected it as you
stated in your follow-up. BTW: You suspect WRONG! I DID state my FULL problem - wanting to name the other sheet tabs from cells in sheet one - it should not matter what row they are on! And as far as the lecture goes - why are you offering advice if you don't have the program I am using anyway? I stated using Excel 2000 and you replied with a solution. Don't offer help on things you don't know. We ask for help because we need it from someone that can actually help not just hope their idea will work. "Don Guillett" wrote: I don't have xl2000 so don't know if that is the problem. I fully tested the solution. Did you use the entire sub, as written? Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Target.Row < 3 Then Exit Sub 'MsgBox Target.Column Sheets(Target.Column-1).Name = Target End Sub BTW, You should ALWAYS state your problem (not just PART of it) in the OP. I suspect you STILL have not fully stated your problem. modify to suit if target.row<3 AND target.row< Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Target.Row < 3 And Target.Row < 7 Then Exit Sub 'MsgBox Target.Column Sheets(Target.Column - 1).Name = Target End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Regina J." wrote in message ... Nothing happened. I went to sheet 2, did the view code and that's where I put it. Then I went back to sheet one and changed the data in B3 to check it and nothing happened. Also, I want to note that not all my data will be in row 3. Sheet 7 - 11 data will be in row 25, sheet 12 - 16 data wil be in row 47. "Don Guillett" wrote: Change to Sheets(Target.Column-1).Name = Target -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... Right click the sheet tabview codeinsert this. Now when you change a cell in row 3 the appropriate sheet will be re-named accordingly. Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Target.Row < 3 Then Exit Sub 'MsgBox Target.Column Sheets(Target.Column).Name = Target End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Regina J." wrote in message ... I have a Workbook with several (16) sheets in it. I want the name of sheet 2 to reflect the info I have typed into cell B3 in sheet 1; sheet 3 to reflect cell C3 in sheet 1, sheet 4 to reflect D3, etc. If I change the data in B3, I want Sheet 2 to rename itself. (Also, I use Excel 2000 - if that makes a difference) I saw a question earlier that I thought answered this but can't figure out how to do it. I don't know anything about macros or VBA so maybe I'm just doing it wrong and not changing the right data in the formula. Please explain in detail what formula I need, where to put it, and what data in the formula I need to change to suit my individual project. Thank you very much for any assistance I can get. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sheet tab name to reference cell data
"Nothing happened. I went to sheet 2, did the view code and that's where I
put it. Then I went back to sheet one and changed" It would have been helpful if you had put the code into the sheet module of the sheet you are changing. Putting the code into sheet TWO has NO effect on changes to sheet ONE....... You didn't go to school at Texas A & M, did you. I went back and re-read your post several times. I see no mention of anything but row THREE. I did fully test the sub I sent and it worked properly for row THREE and then also for row SEVEN. Perhaps you could change the 7 to whatever row is desired. I suggest you look at my record of "ACTUALLY HELPING". BTW, the pay we get for helping here goes to pay for my Infiniti M45. So, I'm really glad to help a lot. "Happy Days" -- Don Guillett Microsoft MVP Excel SalesAid Software "Regina J." wrote in message ... Yes I did use the entire sub as you had it typed even corrected it as you stated in your follow-up. BTW: You suspect WRONG! I DID state my FULL problem - wanting to name the other sheet tabs from cells in sheet one - it should not matter what row they are on! And as far as the lecture goes - why are you offering advice if you don't have the program I am using anyway? I stated using Excel 2000 and you replied with a solution. Don't offer help on things you don't know. We ask for help because we need it from someone that can actually help not just hope their idea will work. "Don Guillett" wrote: I don't have xl2000 so don't know if that is the problem. I fully tested the solution. Did you use the entire sub, as written? Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Target.Row < 3 Then Exit Sub 'MsgBox Target.Column Sheets(Target.Column-1).Name = Target End Sub BTW, You should ALWAYS state your problem (not just PART of it) in the OP. I suspect you STILL have not fully stated your problem. modify to suit if target.row<3 AND target.row< Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Target.Row < 3 And Target.Row < 7 Then Exit Sub 'MsgBox Target.Column Sheets(Target.Column - 1).Name = Target End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Regina J." wrote in message ... Nothing happened. I went to sheet 2, did the view code and that's where I put it. Then I went back to sheet one and changed the data in B3 to check it and nothing happened. Also, I want to note that not all my data will be in row 3. Sheet 7 - 11 data will be in row 25, sheet 12 - 16 data wil be in row 47. "Don Guillett" wrote: Change to Sheets(Target.Column-1).Name = Target -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... Right click the sheet tabview codeinsert this. Now when you change a cell in row 3 the appropriate sheet will be re-named accordingly. Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Target.Row < 3 Then Exit Sub 'MsgBox Target.Column Sheets(Target.Column).Name = Target End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Regina J." wrote in message ... I have a Workbook with several (16) sheets in it. I want the name of sheet 2 to reflect the info I have typed into cell B3 in sheet 1; sheet 3 to reflect cell C3 in sheet 1, sheet 4 to reflect D3, etc. If I change the data in B3, I want Sheet 2 to rename itself. (Also, I use Excel 2000 - if that makes a difference) I saw a question earlier that I thought answered this but can't figure out how to do it. I don't know anything about macros or VBA so maybe I'm just doing it wrong and not changing the right data in the formula. Please explain in detail what formula I need, where to put it, and what data in the formula I need to change to suit my individual project. Thank you very much for any assistance I can get. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sheet tab name to reference cell data
LOL!
Gord On Thu, 10 Apr 2008 16:33:05 -0500, "Don Guillett" wrote: BTW, the pay we get for helping here goes to pay for my Infiniti M45. So, I'm really glad to help a lot. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nesting a sheet name reference within a cell reference??? | Excel Discussion (Misc queries) | |||
Changing sheet reference to cell reference | Excel Worksheet Functions | |||
Reference data from another sheet | Excel Discussion (Misc queries) | |||
Can a cell reference bring the color of the data to the new sheet | Excel Discussion (Misc queries) | |||
How do I reference data from a sheet specified by text in a cell | Excel Worksheet Functions |