Worksheets

[DebbieS]

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!

 

[JLatham]

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.

 

[Debra Dalgleish]

You could store all the information on the Supplies sheet, with a column
to indicate the teacher name.
Then, you could filter the data (Data>Filter>AutoFilter) 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
here:

http://www.contextures.com/xlautofilter01.html
http://www.contextures.com/xlPivot01.html

 

[JLatham]

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

 

[DebbieS]

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

 

[JLatham]

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.