set up a stock ordering system in excel working backwards

[Devil's Isle]

I am trying to set up a stock ordering system for my new business. I sell
gift hampers. The hamper is made up of several products. So, for example, I
have sold 1 'sweet' basket [column B] I need the applicable columns that
represent the individual items in the hamper [C - G] to automatically show
quantities. Am I asking for the impossible. In my head it works. I am used
to formulas going the other way. I would appreciate your feedback.

 

[Gord Dibben]

Couple of methods depending upon how you're set up.

1. If entering basket types down column B you could use a cell with a
COUNTIF formula for each type of basket.

In X1 enter =COUNTIF(B:B,"sweet")

Then in C2:G2(assuming titles in C1:G2) enter =$X$1

Same pattern for "fruit" or "canned goods"

2. Have Data Validation dropdown list in B2 with fruit, sweet and canned
goods.

Worksheet event code to add 1 to X1 when sweet is chosen from B2 dropdown.


Gord Dibben MS Excel MVP


On Fri, 16 Oct 2009 22:52:01 -0700, Devil's Isle <Devil's