Need advice/resources to organize Inventory

[Mike_in_Nebraska]

Using Excel 2007.
=======
Just started a new job with an electrical contractor. They have a
paper-based inventory & pricing system. I want to put this in Excel
and have a requirement to sort all items by type and location in the
warehouse. I have a vague idea how to do it, but need some guidance
on the general framework and ideas on where to go for detailed
advice. An additional requirement is to only have one place - whether
a form or a row in a worksheet - to update an item.

Mike in Nebraska

 

[Yanick]

Using Excel 2007.
=======
Just started a new job with an electrical contractor.  They have a
paper-based inventory & pricing system.  I want to put this in Excel
and have a requirement to sort all items by type and location in the
warehouse.  I have a vague idea how to do it, but need some guidance
on the general framework and ideas on where to go for detailed
advice.  An additional requirement is to only have one place - whether
a form or a row in a worksheet - to update an item.

Mike in Nebraska

Hey Mike, I think your are trading a manual way for a other. I suggest
you to use Access. It may take a bit more effort to create but will be
much more liable mid-long term.

 

[Mike_in_Nebraska]

I agree, Yanick, but MS Access is not available here, so I'm stuck.
Excel's not bad, but this requirement is kinda involved.
I'd thought of a single worksheet of all items, with Category as one
of the column headers. Then have a menu worksheet with a form to
filter on whatever we define. But I don't know if Excel can use a
form to create a filtered worksheet, or if there might be another way
to do this.

 

[Yanick]

I agree, Yanick, but MS Access is not available here, so I'm stuck.
Excel's not bad, but this requirement is kinda involved.
I'd thought of a single worksheet of all items, with Category as one
of the column headers.  Then have a menu worksheet with a form to
filter on whatever we define.  But I don't know if Excel can use a
form to create a filtered worksheet, or if there might be another way
to do this.

Well, I am not familiar with controls in Excel but I guess creating a
table like sheet (Each column representing a table field) is the best
way to do it in Excel.

Would like to help you more. : - )

 

[Mike_in_Nebraska]

Thanks anyway. Maybe some other reader of this newsgroup has some
ideas ...... ?

 

[Gord Dibben]

Several Inventory Templates at the Office Template Gallery.

http://officeupdate.microsoft.com/templategallery/default.asp

Prolly find one you can modify.


Gord Dibben MS Excel MVP

 

[Mike_in_Nebraska]

I DID take a look there, but they are pretty much a flat file - none
of the "extra" we need.

 

[John]

Hi Mike
Check this site and download "DatabaseForm".
http://www.excel-it.com/vba_examples.htm
This is only a example, but to give you a idea each column is filtered, you
could sort by any column.
Popup Userform to add, search, amend, delete. Play with it.
That could be expanded for your needs.
HTH
John

 

[Mike_in_Nebraska]

Excellent, thanks!

 

[John]

You're welcome, glad to help.
Regards
John