multiple workbooks in one file

[Irene]

I am trying to create a database of textbooks in our inventory. I was hoping
someone who is more savvy than I could tell me how to do it. I want to use
Excel 2007 (but I could use Access 2007 if it would only work there) and I
need one all-encompassing inventory. I would like to be able to open one
file and get 7 workbooks each with multiple worksheets for separate data.
i.e.
Textbooks (all-encompassing file)
-Science (workbook 1)
-Biology, Chemistry, etc (10 worksheets)
-Math (workbook 2)
-Trigonometry, Calculus, etc (3 worksheets)
-English (workbook 3)
-1010 (1 worksheet)
-Practical Sciences (workbook 4)
-Electrical, Aviation, etc (9 worksheets)
-Social Sciences (workbook 5)
-History, Psychology, etc (9 worksheets)
-Arts/Humanities (workbook 6)
-Art, Humanities, etc (4 worksheets)
-Clickers (workbook 7)
-TPT (1 worksheet)

Does this make sense? Is it possible to get multiple workbooks saved as one
thing all together like this? I would really appreciate any help at all.

 

[Legare]

I think that what you are asking use to be possible, but that capability
proved to be not very useful and it created a number of problems. I think it
was dropped in XL2K or earlier.

If it were me, I would keep all of the data in one workbook, and possibly on
one worksheet. You could have a column for the major category like Science,
Math, etc, and a second column for the subcategories like Biology, Chemistry,
etc. Then you could use data filtering or Advanced Filter to display only
the categories and sub categories that you are interested in at the moment.
You could use Data/Validation to enter the categories and sub categories from
drop down lists to make data entry easier and consistent. Did you have some
reason for wanting separate workbooks?

 

[Irene]

The reason I want to have separate workbooks is because I need to have all of
my textbook information available in one view. For example: I need my
Science workbook to have Biology, Chemistry, etc. worksheets to keep all of
the information for those respective textbooks. So I would open my
inventory, select my Science workbook, select my Biology worksheet to view
the Title, Author, Date Purchased, Book Number, Cost, Replacement Fee,
Edition, and ISBN Number for hundreds of books used in that subject.

I was hoping this would work in one cohesive file because I have over three
hundred kids coming to me a day to get all of the information about their
textbooks. I need to be able to move from subject to subject quickly so I
can find all of the books they need for their different subject classes.

Is there some magical macro that could make this possible? Or is Access
capable of doing this?

 

[Gord Dibben]

Don't forget that each workbook is one file so you can't have more than one
workbook in a file.

Excel 2003 and earlier had what was known as a workspace file *.XLW that
could contain more than one workbook and when the *.XLW file was opened, all
the workbooks would open.

Why not have one workbook with several worksheets full of books?

Take advantage of Excel's Autofiltering on each sheet.

One sheet for Science with categories of Biology, Chemistry etc.

One sheet for Literature and so on.

Named ranges for each category to isolate that category.

I don't think 2007 version has this feature but check it out in help.

You could also use hyperlinks to open a particular workbook when needed if
you go with multiple workbooks.


Gord Dibben MS Excel MVP