Total Beginner Ques: "Can Excel Do This?"

[rjwalker]

I've never had to use Excel, but might see a need and before I dig in, I would like to know if Excel can do the following

1: Maintain an inventory of say 20 products (for a mobile food pantry)
2. Generate a form for selecting items
3. Generate a packing list based on the number of expected customers

E.g, Say we have on hand

• 20 cases of canned corn (24 cans to a case)
• 20 cases of sliced carrots (24 cans to a case)
• 20 Cases sliced beans (24 cans to a case)
• 20 cases potatoes (24 cans to a case)
• --Customer gets to choose one or the other
• 30 cases of milk (12 boxes to a case
• 30 cases of juice (8 bottles to a case)
• --Customer gets to choose one or the other.

Note, from the 4 different canned vegetables in the inventory, for a given pantry, I'd only take 2 - so I'd like a checkbox form for selecting which 2 of the 4 to further process


For a given trip, i expect 48 customers, so I might want to take (a bit over-simplified example)

• 1 case corn
• 1 case of carrots
• 2 cases of milk
• 3 cases of juice

Can I:

1. have a form asking "How many customers are you packing for" - for this example I'd fill in "48"
2. And which asks "Which products do you want to take?"- for this example I'd check off "corn" and ""carrots"
3. Have Excel go through the inventory and generate the packing list
4. And, of course, adjust the inventory count?

Bonus question Can Excel generate an html version for me to publish to our web site for a distant warehouse location

I'm guessing it can do all this -- but would it need VBA? Is this really advanced or relatively simple for someone with some script development experience?

Thanks in advance

Bob Newbie

 

[NeonRedSharpie]

Since you are updating cells both ways, this would need to be visual basic. And it will be a little easier to code/maintain/make idiot proof if it is a userform rather than an excel sheet. The reasoning being that if it's all in an excel sheet, manual changes to the inventory (either purposefully or accidentally) can fudge your numbers. So yes, this is a relatively simple userform. Sending userforms is another issue since I can't just upload some code.

As for your an html version, I don't know. Never tried that.

 

[rjwalker]

Thanks for the quick response - I'm not sure what you mean by "updating cells both ways"

I was thinking the "packing list" would be generated by

1. looking at the existing numbers
2. running a simple(?) math formula
3. and generating a new list (in another spread sheet instance) showing the number of cases to pack
4. And then update the original spread sheet by the number of items picked.,

Is that an example of "updating cells both ways?"

>>easier if it is a userform rather than an excel sheet.

Wouldn't a user form just be used to access (and manipulate) a spreadsheet or database?

As I said, I'm as raw a beginner here as you can image! (If I was any dumber on excel, you'd probably have to water me regularly )

 

[NeonRedSharpie]

So what I mean by updating cells both ways is your 4th bullet point.

4.  [COLOR=#333333]And then update the original spread sheet by the number of items picked.,[/COLOR]

Excel formulas can't update sheets they're pulling from. That's why an outside function (VBA in this case) is required. You can use formulas and fancy formatting for steps 1-3. But when it comes to updating a list of static values, that's not possible in formulas. Unless you have an entire worksheet devoted to backend formulas, but that's not even fool proof and if any one formula gets overwritten it all goes downhill.

 

[rjwalker]

NeonRedSharpie

Thanks so much for that helpful info!

 

[NeonRedSharpie]

If you get stuck or need help with the coding, come on back. It sounds like a fun project, and pretty straight forward.

 

[wesimmo]

Just to add....

Excel can do all sorts of things if you look hard enough for a way to do it, it's really only limited by your own imagination, to the point I often have to think "Ok, I CAN do this in Excel, but just because I like playing with Excel and can do it, should I? Or is there a better tool for the job?"

That said, this seems like a great project to learn about Excel and VBA, I find I learn more trying to do something practical I want to do rather than boring text books, in fact I've never even opened an Excel textbook or training material, MrExcel and Google FTW!

 

[NeonRedSharpie]

That's why I'm working on a full scale RPG game in excel right now. I wanted to learn userforms so I'm making a whole thing with userforms. I'll need pre-alpha testers pretty soon...maybe I can post to the other excel forum.