Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combine Multiple Worksheets into One with a Drop Down Box
Hello,
I was hoping someone could help me. I have a "master" template set up and each of my sales reps goes in and fills out the required information. I was wondering if there was a way to "combine" all of them into the master, so all I would have to do to look through each persons sheet is click on a drop down box on the master sheet, click on the person's name, and all of their information would come up. Any help will be appreciated. Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combine Multiple Worksheets into One with a Drop Down Box
JK
I'm not sure what you have. Do you have numerous workbooks, one for each sales rep, and you want to combine them all into one, or do you have only one workbook and each sales rep inputs information to only his sheet? What you say at the end can certainly be done, that is, click on a name in a drop-down and that sheet displays. HTH Otto "JK" wrote in message ... Hello, I was hoping someone could help me. I have a "master" template set up and each of my sales reps goes in and fills out the required information. I was wondering if there was a way to "combine" all of them into the master, so all I would have to do to look through each persons sheet is click on a drop down box on the master sheet, click on the person's name, and all of their information would come up. Any help will be appreciated. Thanks. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combine Multiple Worksheets into One with a Drop Down Box
Otto,
Thanks for responding. I have one workbook and each of my sales reps enter information on his sheet. Can you tell me how to do this? That would be great! Also, if I did have numerous workbooks, could I combine them into one with a convenient drop-down box? "Otto Moehrbach" wrote: JK I'm not sure what you have. Do you have numerous workbooks, one for each sales rep, and you want to combine them all into one, or do you have only one workbook and each sales rep inputs information to only his sheet? What you say at the end can certainly be done, that is, click on a name in a drop-down and that sheet displays. HTH Otto "JK" wrote in message ... Hello, I was hoping someone could help me. I have a "master" template set up and each of my sales reps goes in and fills out the required information. I was wondering if there was a way to "combine" all of them into the master, so all I would have to do to look through each persons sheet is click on a drop down box on the master sheet, click on the person's name, and all of their information would come up. Any help will be appreciated. Thanks. . |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combine Multiple Worksheets into One with a Drop Down Box
JK
This little macro will do it. Note that this includes nothing to prevent anyone from selecting any sheet they wish to view. I simply placed a drop-down in G5 that displays the names of all the rep sheets. You select one of the sheet names and that sheet is selected and becomes the active sheet. There is a lot more that you can have in this kind of a setup. For instance, you can have the code to always display the sheet with the drop-down when the file is first opened. You can also add additional code to have the sheets hidden until the code is fired. You can even have the sheets VeryHidden (requires VBA to unhide). You can also add a password requirement to access a sheet. About combining workbooks, yes, VBA can combine data from them into one workbook. I don't know what you would use the drop-down for doing that except to maybe select an individual workbook to combine to the master. What you can do is have all the desired workbooks and the master in one folder and fire the code and combine them all. Is that what you mean? Otto Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 Then Exit Sub If Not Intersect(Target, Range("G5")) Is Nothing Then Sheets(Range("G5").Value).Select End If End Sub "JK" wrote in message ... Otto, Thanks for responding. I have one workbook and each of my sales reps enter information on his sheet. Can you tell me how to do this? That would be great! Also, if I did have numerous workbooks, could I combine them into one with a convenient drop-down box? "Otto Moehrbach" wrote: JK I'm not sure what you have. Do you have numerous workbooks, one for each sales rep, and you want to combine them all into one, or do you have only one workbook and each sales rep inputs information to only his sheet? What you say at the end can certainly be done, that is, click on a name in a drop-down and that sheet displays. HTH Otto "JK" wrote in message ... Hello, I was hoping someone could help me. I have a "master" template set up and each of my sales reps goes in and fills out the required information. I was wondering if there was a way to "combine" all of them into the master, so all I would have to do to look through each persons sheet is click on a drop down box on the master sheet, click on the person's name, and all of their information would come up. Any help will be appreciated. Thanks. . |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combine Multiple Worksheets into One with a Drop Down Box
Otto,
Sorry if I sound stupid, but I do not even know where to start with doing a macro. I clicked on Macro and it asked me for the macro name, which I don't know. If you have time. Could you give me a little step-by-step? "Otto Moehrbach" wrote: JK This little macro will do it. Note that this includes nothing to prevent anyone from selecting any sheet they wish to view. I simply placed a drop-down in G5 that displays the names of all the rep sheets. You select one of the sheet names and that sheet is selected and becomes the active sheet. There is a lot more that you can have in this kind of a setup. For instance, you can have the code to always display the sheet with the drop-down when the file is first opened. You can also add additional code to have the sheets hidden until the code is fired. You can even have the sheets VeryHidden (requires VBA to unhide). You can also add a password requirement to access a sheet. About combining workbooks, yes, VBA can combine data from them into one workbook. I don't know what you would use the drop-down for doing that except to maybe select an individual workbook to combine to the master. What you can do is have all the desired workbooks and the master in one folder and fire the code and combine them all. Is that what you mean? Otto Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 Then Exit Sub If Not Intersect(Target, Range("G5")) Is Nothing Then Sheets(Range("G5").Value).Select End If End Sub "JK" wrote in message ... Otto, Thanks for responding. I have one workbook and each of my sales reps enter information on his sheet. Can you tell me how to do this? That would be great! Also, if I did have numerous workbooks, could I combine them into one with a convenient drop-down box? "Otto Moehrbach" wrote: JK I'm not sure what you have. Do you have numerous workbooks, one for each sales rep, and you want to combine them all into one, or do you have only one workbook and each sales rep inputs information to only his sheet? What you say at the end can certainly be done, that is, click on a name in a drop-down and that sheet displays. HTH Otto "JK" wrote in message ... Hello, I was hoping someone could help me. I have a "master" template set up and each of my sales reps goes in and fills out the required information. I was wondering if there was a way to "combine" all of them into the master, so all I would have to do to look through each persons sheet is click on a drop down box on the master sheet, click on the person's name, and all of their information would come up. Any help will be appreciated. Thanks. . . |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combine Multiple Worksheets into One with a Drop Down Box
JK
Sorry about that. I should have told you more. Right-click the sheet tab of the sheet that holds the drop-down. A little menu pops up. Click on View Code. A blank code module appears. Paste this macro into that module. "X" out of the module to return to your sheet. Now when you make a selection from the drop-down (it must be in G5), the sheet you selected will become the active sheet (it will appear on the screen). Read the other options I discussed in my previous post and come back if you want to pursue any of them Otto "JK" wrote in message ... Otto, Sorry if I sound stupid, but I do not even know where to start with doing a macro. I clicked on Macro and it asked me for the macro name, which I don't know. If you have time. Could you give me a little step-by-step? "Otto Moehrbach" wrote: JK This little macro will do it. Note that this includes nothing to prevent anyone from selecting any sheet they wish to view. I simply placed a drop-down in G5 that displays the names of all the rep sheets. You select one of the sheet names and that sheet is selected and becomes the active sheet. There is a lot more that you can have in this kind of a setup. For instance, you can have the code to always display the sheet with the drop-down when the file is first opened. You can also add additional code to have the sheets hidden until the code is fired. You can even have the sheets VeryHidden (requires VBA to unhide). You can also add a password requirement to access a sheet. About combining workbooks, yes, VBA can combine data from them into one workbook. I don't know what you would use the drop-down for doing that except to maybe select an individual workbook to combine to the master. What you can do is have all the desired workbooks and the master in one folder and fire the code and combine them all. Is that what you mean? Otto Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 Then Exit Sub If Not Intersect(Target, Range("G5")) Is Nothing Then Sheets(Range("G5").Value).Select End If End Sub "JK" wrote in message ... Otto, Thanks for responding. I have one workbook and each of my sales reps enter information on his sheet. Can you tell me how to do this? That would be great! Also, if I did have numerous workbooks, could I combine them into one with a convenient drop-down box? "Otto Moehrbach" wrote: JK I'm not sure what you have. Do you have numerous workbooks, one for each sales rep, and you want to combine them all into one, or do you have only one workbook and each sales rep inputs information to only his sheet? What you say at the end can certainly be done, that is, click on a name in a drop-down and that sheet displays. HTH Otto "JK" wrote in message ... Hello, I was hoping someone could help me. I have a "master" template set up and each of my sales reps goes in and fills out the required information. I was wondering if there was a way to "combine" all of them into the master, so all I would have to do to look through each persons sheet is click on a drop down box on the master sheet, click on the person's name, and all of their information would come up. Any help will be appreciated. Thanks. . . |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combine Multiple Worksheets into One with a Drop Down Box
Otto,
I am almost there (I think). I have to back up a second though. Instead of cell "G5", could I make the drop down in "B1". That is where I would like it. I made a drop down too but I do not think I did it right because when I clicked on the persons name, nothing changed. I think I need a really dumbed down explanation! Also, for the other sheets do I need to put that macro in too? "Otto Moehrbach" wrote: JK Sorry about that. I should have told you more. Right-click the sheet tab of the sheet that holds the drop-down. A little menu pops up. Click on View Code. A blank code module appears. Paste this macro into that module. "X" out of the module to return to your sheet. Now when you make a selection from the drop-down (it must be in G5), the sheet you selected will become the active sheet (it will appear on the screen). Read the other options I discussed in my previous post and come back if you want to pursue any of them Otto "JK" wrote in message ... Otto, Sorry if I sound stupid, but I do not even know where to start with doing a macro. I clicked on Macro and it asked me for the macro name, which I don't know. If you have time. Could you give me a little step-by-step? "Otto Moehrbach" wrote: JK This little macro will do it. Note that this includes nothing to prevent anyone from selecting any sheet they wish to view. I simply placed a drop-down in G5 that displays the names of all the rep sheets. You select one of the sheet names and that sheet is selected and becomes the active sheet. There is a lot more that you can have in this kind of a setup. For instance, you can have the code to always display the sheet with the drop-down when the file is first opened. You can also add additional code to have the sheets hidden until the code is fired. You can even have the sheets VeryHidden (requires VBA to unhide). You can also add a password requirement to access a sheet. About combining workbooks, yes, VBA can combine data from them into one workbook. I don't know what you would use the drop-down for doing that except to maybe select an individual workbook to combine to the master. What you can do is have all the desired workbooks and the master in one folder and fire the code and combine them all. Is that what you mean? Otto Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 Then Exit Sub If Not Intersect(Target, Range("G5")) Is Nothing Then Sheets(Range("G5").Value).Select End If End Sub "JK" wrote in message ... Otto, Thanks for responding. I have one workbook and each of my sales reps enter information on his sheet. Can you tell me how to do this? That would be great! Also, if I did have numerous workbooks, could I combine them into one with a convenient drop-down box? "Otto Moehrbach" wrote: JK I'm not sure what you have. Do you have numerous workbooks, one for each sales rep, and you want to combine them all into one, or do you have only one workbook and each sales rep inputs information to only his sheet? What you say at the end can certainly be done, that is, click on a name in a drop-down and that sheet displays. HTH Otto "JK" wrote in message ... Hello, I was hoping someone could help me. I have a "master" template set up and each of my sales reps goes in and fills out the required information. I was wondering if there was a way to "combine" all of them into the master, so all I would have to do to look through each persons sheet is click on a drop down box on the master sheet, click on the person's name, and all of their information would come up. Any help will be appreciated. Thanks. . . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combine Multiple Worksheets | Excel Discussion (Misc queries) | |||
Direction combine several excel worksheets, drop down lists and da | Excel Worksheet Functions | |||
Combine multiple workbooks into 1 workbook w/ multiple worksheets | Excel Discussion (Misc queries) | |||
Combine multiple workbooks into 1 workbook w/ multiple worksheets | Excel Discussion (Misc queries) | |||
how can I combine multiple worksheets into one? | Excel Worksheet Functions |