How would you set this up?

  • Thread starter Thread starter Ed
  • Start date Start date
E

Ed

I need to track the usage of several hundred items. They can be
lumped into three or four basic categories. Each one has a unique
identifier. I need to track when an item came into the system, when
it was replaced, and the item "life" - this is done by date and by
certain item parameters. Each replacement item then becomes a new
item to track.

This seems to scream for an Access database, but I'm restricted to
Excel because that's what we have that people are familiar with. If
someone wants to see this, I can email them a workbook with greater
confidence in their understanding that if I try to give them an entire
database. (I'm also more familiar with Excel, and have greater
confidence in sending out understandable data than if I used Access!)

I'm thinking maybe one workbook with a worksheet for each category of
item, and a UserForm for input - a drop-down to select the item type
with text boxes for data input, or maybe a multipage control to allow
for needing different data inputs for different items. Code would
find the right worksheet, insert a blank line under the item replaced
and fill everything in, then set formulas to calculate item life.
(Previous attempts have resulted in separate workbooks for each item
type, and manual insertion of new lines and formulas. I'd rather
not!)

Does this sound like the best way with Excel? Or does anyone have a
better solution?
Ed
 
I need to track the usage of several hundred items. They can be
lumped into three or four basic categories. Each one has a unique
identifier. I need to track when an item came into the system, when
it was replaced, and the item "life" - this is done by date and by
certain item parameters. Each replacement item then becomes a new
item to track.

This seems to scream for an Access database, but I'm restricted to
Excel because that's what we have that people are familiar with. If
someone wants to see this, I can email them a workbook with greater
confidence in their understanding that if I try to give them an entire
database. (I'm also more familiar with Excel, and have greater
confidence in sending out understandable data than if I used Access!)

I'm thinking maybe one workbook with a worksheet for each category of
item, and a UserForm for input - a drop-down to select the item type
with text boxes for data input, or maybe a multipage control to allow
for needing different data inputs for different items. Code would
find the right worksheet, insert a blank line under the item replaced
and fill everything in, then set formulas to calculate item life.
(Previous attempts have resulted in separate workbooks for each item
type, and manual insertion of new lines and formulas. I'd rather
not!)

Does this sound like the best way with Excel? Or does anyone have a
better solution?
Ed
Click to expand...

Are you intending this to be multi user - if so, DEFINITELY access.
Under NO circumstances share your workbook!
 
Are you familiar with VBA? If you NEED to do this on excel, even though like
you said Access would make your life a million times easiar. I would work
with Worksheet Events that can figure out what you are trying to do.

I need to know more info like how is the information is gathered, how it's
put in to excel, is it an import or does someone manually type it in?

But everything you're talking about can be done with the right events. It's
just a lot slower and less efficient then doing it with access.

lmk
 
It sounds fine.
Thanks, Tom. Coming from you, that is very confidence-inspiring.
Well, at least for the concept - we'll see if I can make it work!
This will be for my use only. although I'd like someone else to be
able to work on it if I'm gone. This will not be a multiple-user file
inthe sense of many people opening and inputting all day long.
I am familiar with VBA, although not greatly expert by any means. I
can usually hack something together to accompish the mission, quite
often with much help from the NG. For example, I don't see how
Worksheet Events come into play here, because I've rarely used them.
Yes, it must be done in Excel, and everything will be typed in
manually.

Ed
 
Well the worksheet and workbook events are inportant because you don't need a
button or something the user has to do to start the macro. The events are
generated based on what the user does. Like whenever the user clicks out of a
worksheet you can tell the worksheet to do whatever it is you want, or if a
user starts a new row it can automatically be colored a certain color, etc.
 
Back
Top