Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Worksheets
My budget workbook is broken up into 16 worksheets, the "Supplies" worksheet
being one of them. The "Supplies" budget is broken up into 24 parts (so I've set up one teacher/department budget per worksheet). I want to enter information only once (on one of the individual worksheets), but would like it to copy over to the main "Supplies" worksheet. Do I do it by running a macro, using a function, or copy/paste? I did the Edit, Fill, Across Worksheets thing, which put the 8 rows of data from the first individual worksheet onto the first 8 rows of the main "Supplies" worksheet. Then when I did the next one (which had only two rows of data), it transferred them onto the first two rows of the main "Supplies" worksheet, replacing what was there from the first worksheet. Is there a way it would put them on lines 9 & 10 instead? That way, I can have my whole "Supplies" budget in one place for the principal, but if a teacher asks for a copy of their individual budget, I can provide that too them (without them seeing what everyone else has in theirs). I hope this makes sense to someone out there. This would be such a great thing to know and save time too! -- DebbieS |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Worksheets
I'll describe the manual process for setting up a single link on the Supplies
sheet to a single cell on one of the teacher's sheets. I think that'll get you going. We'll just start at the upper left corner, A1 on the Supplies sheet, but it doesn't have to be there, but we do need a point of reference. In A1 on the Supplies sheet, type a single = symbol then choose the particular teacher's sheet and the first cell you need to show up in the Supplies sheet. Hit the enter key. Poof! Now A1 on Supplies should have a formula something like this: ='Teacher 1'!A1 You can now fill that formula to get all of the information you want from that sheet. Then drop down on the Supplies sheet to where you want the information from the second teacher's sheet to begin and repeat the process, but choosing the second teacher's sheet instead of the first. Changes on the various teacher's sheets will be echoed on the Supplies sheet when they are made. "DebbieS" wrote: My budget workbook is broken up into 16 worksheets, the "Supplies" worksheet being one of them. The "Supplies" budget is broken up into 24 parts (so I've set up one teacher/department budget per worksheet). I want to enter information only once (on one of the individual worksheets), but would like it to copy over to the main "Supplies" worksheet. Do I do it by running a macro, using a function, or copy/paste? I did the Edit, Fill, Across Worksheets thing, which put the 8 rows of data from the first individual worksheet onto the first 8 rows of the main "Supplies" worksheet. Then when I did the next one (which had only two rows of data), it transferred them onto the first two rows of the main "Supplies" worksheet, replacing what was there from the first worksheet. Is there a way it would put them on lines 9 & 10 instead? That way, I can have my whole "Supplies" budget in one place for the principal, but if a teacher asks for a copy of their individual budget, I can provide that too them (without them seeing what everyone else has in theirs). I hope this makes sense to someone out there. This would be such a great thing to know and save time too! -- DebbieS |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Worksheets
You could store all the information on the Supplies sheet, with a column
to indicate the teacher name. Then, you could filter the data (DataFilterAutoFilter) for a specific teacher name, and print out their data when requested. Keeping it on one sheet will be easier to maintain, and you can use filters to view specific data, and pivot tables to summarize. There are instructions for filters and pivot tables in Excel's Help, and he http://www.contextures.com/xlautofilter01.html http://www.contextures.com/xlPivot01.html DebbieS wrote: My budget workbook is broken up into 16 worksheets, the "Supplies" worksheet being one of them. The "Supplies" budget is broken up into 24 parts (so I've set up one teacher/department budget per worksheet). I want to enter information only once (on one of the individual worksheets), but would like it to copy over to the main "Supplies" worksheet. Do I do it by running a macro, using a function, or copy/paste? I did the Edit, Fill, Across Worksheets thing, which put the 8 rows of data from the first individual worksheet onto the first 8 rows of the main "Supplies" worksheet. Then when I did the next one (which had only two rows of data), it transferred them onto the first two rows of the main "Supplies" worksheet, replacing what was there from the first worksheet. Is there a way it would put them on lines 9 & 10 instead? That way, I can have my whole "Supplies" budget in one place for the principal, but if a teacher asks for a copy of their individual budget, I can provide that too them (without them seeing what everyone else has in theirs). I hope this makes sense to someone out there. This would be such a great thing to know and save time too! -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Worksheets
Debbie,
Debra's suggestion has additional benefits - maintenance of the Supplies sheet is simplified, especially if your list of supplies grows. With the way you have it now, and that I gave some guidance on getting together on one sheet, you'd have to go back to the Supplies sheet and add rows/extend formulas if your list of information on the individual teacher's sheets gets longer. With it all on one sheet, if you come up with another supply item, or items, you don't have to insert rows at each break in the list of teachers, you can just add it all at the bottom of the current list and the filtering will still show any given teacher or supply item as a group. You may want to set up lists to use with data validation on the sheet if you go that route, and there's not much of a better place to get advice on doing that than at Debra's Contextures site either - so it's a one-stop-shopping trip for you <g "Debra Dalgleish" wrote: You could store all the information on the Supplies sheet, with a column to indicate the teacher name. Then, you could filter the data (DataFilterAutoFilter) for a specific teacher name, and print out their data when requested. Keeping it on one sheet will be easier to maintain, and you can use filters to view specific data, and pivot tables to summarize. There are instructions for filters and pivot tables in Excel's Help, and he http://www.contextures.com/xlautofilter01.html http://www.contextures.com/xlPivot01.html DebbieS wrote: My budget workbook is broken up into 16 worksheets, the "Supplies" worksheet being one of them. The "Supplies" budget is broken up into 24 parts (so I've set up one teacher/department budget per worksheet). I want to enter information only once (on one of the individual worksheets), but would like it to copy over to the main "Supplies" worksheet. Do I do it by running a macro, using a function, or copy/paste? I did the Edit, Fill, Across Worksheets thing, which put the 8 rows of data from the first individual worksheet onto the first 8 rows of the main "Supplies" worksheet. Then when I did the next one (which had only two rows of data), it transferred them onto the first two rows of the main "Supplies" worksheet, replacing what was there from the first worksheet. Is there a way it would put them on lines 9 & 10 instead? That way, I can have my whole "Supplies" budget in one place for the principal, but if a teacher asks for a copy of their individual budget, I can provide that too them (without them seeing what everyone else has in theirs). I hope this makes sense to someone out there. This would be such a great thing to know and save time too! -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Worksheets
I haven't had a chance to try both you and Debra's answers, but didn't want
to forget to thank you very much. DebbieS -- DebbieS "JLatham" wrote: Debbie, Debra's suggestion has additional benefits - maintenance of the Supplies sheet is simplified, especially if your list of supplies grows. With the way you have it now, and that I gave some guidance on getting together on one sheet, you'd have to go back to the Supplies sheet and add rows/extend formulas if your list of information on the individual teacher's sheets gets longer. With it all on one sheet, if you come up with another supply item, or items, you don't have to insert rows at each break in the list of teachers, you can just add it all at the bottom of the current list and the filtering will still show any given teacher or supply item as a group. You may want to set up lists to use with data validation on the sheet if you go that route, and there's not much of a better place to get advice on doing that than at Debra's Contextures site either - so it's a one-stop-shopping trip for you <g "Debra Dalgleish" wrote: You could store all the information on the Supplies sheet, with a column to indicate the teacher name. Then, you could filter the data (DataFilterAutoFilter) for a specific teacher name, and print out their data when requested. Keeping it on one sheet will be easier to maintain, and you can use filters to view specific data, and pivot tables to summarize. There are instructions for filters and pivot tables in Excel's Help, and he http://www.contextures.com/xlautofilter01.html http://www.contextures.com/xlPivot01.html DebbieS wrote: My budget workbook is broken up into 16 worksheets, the "Supplies" worksheet being one of them. The "Supplies" budget is broken up into 24 parts (so I've set up one teacher/department budget per worksheet). I want to enter information only once (on one of the individual worksheets), but would like it to copy over to the main "Supplies" worksheet. Do I do it by running a macro, using a function, or copy/paste? I did the Edit, Fill, Across Worksheets thing, which put the 8 rows of data from the first individual worksheet onto the first 8 rows of the main "Supplies" worksheet. Then when I did the next one (which had only two rows of data), it transferred them onto the first two rows of the main "Supplies" worksheet, replacing what was there from the first worksheet. Is there a way it would put them on lines 9 & 10 instead? That way, I can have my whole "Supplies" budget in one place for the principal, but if a teacher asks for a copy of their individual budget, I can provide that too them (without them seeing what everyone else has in theirs). I hope this makes sense to someone out there. This would be such a great thing to know and save time too! -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Worksheets
Well, thanks for my half of your thank you, I'm sure that Debra appreciates
it also. I'd give her solution a look, I think it would simplify your life once implemented. But you can give mine a try just as a learning experience or as a comparison to see which one you would prefer to deal with in the long run. "DebbieS" wrote: I haven't had a chance to try both you and Debra's answers, but didn't want to forget to thank you very much. DebbieS -- DebbieS "JLatham" wrote: Debbie, Debra's suggestion has additional benefits - maintenance of the Supplies sheet is simplified, especially if your list of supplies grows. With the way you have it now, and that I gave some guidance on getting together on one sheet, you'd have to go back to the Supplies sheet and add rows/extend formulas if your list of information on the individual teacher's sheets gets longer. With it all on one sheet, if you come up with another supply item, or items, you don't have to insert rows at each break in the list of teachers, you can just add it all at the bottom of the current list and the filtering will still show any given teacher or supply item as a group. You may want to set up lists to use with data validation on the sheet if you go that route, and there's not much of a better place to get advice on doing that than at Debra's Contextures site either - so it's a one-stop-shopping trip for you <g "Debra Dalgleish" wrote: You could store all the information on the Supplies sheet, with a column to indicate the teacher name. Then, you could filter the data (DataFilterAutoFilter) for a specific teacher name, and print out their data when requested. Keeping it on one sheet will be easier to maintain, and you can use filters to view specific data, and pivot tables to summarize. There are instructions for filters and pivot tables in Excel's Help, and he http://www.contextures.com/xlautofilter01.html http://www.contextures.com/xlPivot01.html DebbieS wrote: My budget workbook is broken up into 16 worksheets, the "Supplies" worksheet being one of them. The "Supplies" budget is broken up into 24 parts (so I've set up one teacher/department budget per worksheet). I want to enter information only once (on one of the individual worksheets), but would like it to copy over to the main "Supplies" worksheet. Do I do it by running a macro, using a function, or copy/paste? I did the Edit, Fill, Across Worksheets thing, which put the 8 rows of data from the first individual worksheet onto the first 8 rows of the main "Supplies" worksheet. Then when I did the next one (which had only two rows of data), it transferred them onto the first two rows of the main "Supplies" worksheet, replacing what was there from the first worksheet. Is there a way it would put them on lines 9 & 10 instead? That way, I can have my whole "Supplies" budget in one place for the principal, but if a teacher asks for a copy of their individual budget, I can provide that too them (without them seeing what everyone else has in theirs). I hope this makes sense to someone out there. This would be such a great thing to know and save time too! -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do i assign the ActiveWorkbook.Worksheets to a worksheets object? | Excel Worksheet Functions | |||
worksheets | Excel Discussion (Misc queries) | |||
Worksheets | Excel Discussion (Misc queries) | |||
worksheets | Excel Discussion (Misc queries) | |||
Assigning Cells in worksheets to other data in other worksheets. | Excel Discussion (Misc queries) |