how best to creat an inventory list, with one master sheet and a sheet for each item

[Roxanne]

I have been asked to compile an inventory of every item in the
warehouse. In addition, this workbook will be used to track the
invoice of recieved goods and the tracking number of goods issued.
Ideally there would be one sheet that would list every item in 'stores'
with a total and then a connection to the sheet for each item that has
columns to track incoming and out going stock complete with columns for
tracking numbers and invoice numbers. Any ideas?? help, please I am
desperate!

 

[Bernie Deitrick]

Roxanne,

One bit of advice: use a database, whether with Excel or with Access. Use
just _one_ sheet with enough fields to enter all the data that you would
ever need: then you can use pivottables and filtering to see your data,
without having to worry about sheet links and such.

HTH,
Bernie
MS Excel MVP

 

[Dave Peterson]

I like Bernie's suggestion a lot.

I think I'd use one row for headers.
A column for part number
a column for received
a column for shipped
(or a single column with +/- quantities)
a column for dates
a column for ... well, a column for every piece of info that I'll ever need to
track.

Then to get totals, you can use Data|Pivottable to get nice summaries.

I'd use a dynamic range so that when I add more rows to the "database", I don't
have to adjust the pivottable--just refresh it.

Debra Dalgleish has some instructions at:
http://www.contextures.com/xlNames01.html#Dynamic

And if you've never used pivottables before, you'll be an expert after about an
hour of experimenting.

Here are some links for pivottable info:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx

I have been asked to compile an inventory of every item in the
warehouse. In addition, this workbook will be used to track the
invoice of recieved goods and the tracking number of goods issued.
Ideally there would be one sheet that would list every item in 'stores'
with a total and then a connection to the sheet for each item that has
columns to track incoming and out going stock complete with columns for
tracking numbers and invoice numbers. Any ideas?? help, please I am
desperate!