Populating ranges following user input

shawnmandel · May 23rd, 2002, 02:55 PM

I have 3 Excel worksheets: Customer (Contains Customer # and Customer Discount %), Product (contains Product # and Product Cost), and CostAnalysis (contains both the Product Cost and Customer Discount column).

I need to ask a user for a specific customer and product combination taken from the Customer & Product worksheet and for the costing and discount info to be populated automatically in the CostAnalysis worksheet.

How would I proceed to execute this type of action in the most efficient possible manner?

Any help would be greatly appreciated.

P.S.: Please refer to my 3rd post for all the details.

Thanks very much,
Shawn

[ This Message was edited by: shawnmandel on 2002-05-24 12:10 ]

Jay Petrulis · May 23rd, 2002, 08:51 PM

How are you asking the user for this information? Are you using a UserForm, or cells on a worksheet?

If on a UserForm, and with Combo Boxes, the "OK" (or such) button click can load the values to the cost worksheet. Event macros also might work. So, too, might worksheet formulas.

How is your data set up on each sheet?

Please provide as many details as you can to get a good answer.

Jay

shawnmandel · May 24th, 2002, 09:22 AM

My next post contains all the sample spreadsheets that are involved in my issue.

Thank you very much in advance for your input.

Shawn

[ This Message was edited by: shawnmandel on 2002-05-24 11:25 ]

shawnmandel · May 24th, 2002, 09:49 AM

I am new to VBA for Excel and have the following 3 spreadsheets (Customer Program Profile.xls; Brand Costing.xls; Customer Costing Analysis.xls).

Note that the Costing Analysis spreadsheet pulls data from both the Customer Program Profile AND the Brand Costing workbooks:

Code:

CUSTOMER PROGRAM PROFILE WORKBOOK1:

Customer #: 011 - WalMart
                             Products
Discount Program       
                       TAR   COB   UNI  APX
TR (Trade Discount)     1%
CA (Coop Allowance)                 3%
VR (Volume Rebate)             5%
PP (Promo Program)                       2%


CUSTOMER PROGRAM PROFILE WORKBOOK2:

Customer #: 028 - Zellers
                             Products
Discount Program       
                       TAR   COB   UNI  APX
TR (Trade Discount)           8%
CA (Coop Allowance)     4%
VR (Volume Rebate)                       1%
PP (Promo Program)                  3%


CUSTOMER PROGRAM PROFILE WORKBOOK3...etc...

---------------------------------------------

BRAND COSTING WORKBOOK1:

Product #:  UNI - UNIDEN

Model #         Retail             Cost

UNI177          129.99             65.00
UNI141          109.99             42.00
UNI209          199.99             78.00
UNI113           99.99             22.00


BRAND COSTING WORKBOOK2:

Product #:  APX - APEX

Model #         Retail             Cost

APX199          229.99             85.00
APX201          119.99             45.00
APX091          199.99             70.00
APX228           79.99             12.00


BRAND COSTING WORKBOOK3...etc...

---------------------------------------------

CUSTOMER COSTING ANALYSIS spreadsheet (Example 1):

User input into cells (no UserForms) - 

Customer #  |  Product #
                        
                        Discount Program
Model #  Retail  Cost   TR   CA   VR   PP
  -        -      -     -    -    -    -
  -        -      -     -    -    -    -
  -        -      -     -    -    -    -
  -        -      -     -    -    -    -

Requirements: The Customer Costing Analysis spreadsheet is initially empty. When the user enters the Customer#/Product# combination, the Brand Costing worksheet should be populated with all the model costs and retail prices for the model numbers for that product and the discount program percentages for that customer--such as the following:

Code:

Customer # 011 | Product # UNI

                          Discount Program
Model #  Retail    Cost   TR  CA   VR   PP

UNI177   129.99   65.00   -   3%   -    - 
UNI141   109.99   42.00   -   3%   -    -
UNI209   199.99   78.00   -   3%   -    -
UNI113    99.99   22.00   -   3%   -    -

The Customer Costing Analysis spreadsheet is now populated based on the user input.

Other user input combination examples:

Code:

CUSTOMER COSTING ANALYSIS worksheet Example 2, for Customer # 011  |  Product # APX

                        Discount Program
Model #  Retail  Cost   TR  CA   VR   PP

APX199   229.99  85.00   -   -    -   2%
APX201   119.99  45.00   -   -    -   2%
APX091   199.99  70.00   -   -    -   2%
APX228    79.99  12.00   -   -    -   2%


CUSTOMER COSTING ANALYSIS worksheet Example 3, for Customer # 028  |  Product # UNI

                         Discount Program
Model #  Retail   Cost   TR  CA   VR   PP

UNI177   129.99   65.00   -   -    -    3% 
UNI141   109.99   42.00   -   -    -    3%
UNI209   199.99   78.00   -   -    -    3%
UNI113    99.99   22.00   -   -    -    3%


CUSTOMER COSTING ANALYSIS worksheet Example 4, for Customer # 028 | Product # APX

                        Discount Program
Model #  Retail  Cost   TR  CA   VR   PP

APX199   229.99  85.00   -   -    1%   -
APX201   119.99  45.00   -   -    1%   -
APX091   199.99  70.00   -   -    1%   -
APX228    79.99  12.00   -   -    1%   -

Additionally, any irrelevant Discount Program columns (i.e., missing/zero data should be automatically hidden (e.g., in the above Example 4, TR, CA, and PP should not show up in the Customer Costing Analysis spreadsheet).

An obvious initial question would be - do I prepare 3 separate workbooks, or 3 worksheets within one workbook?

Any help would be greatly appreciated.

Thanks very much,
Shawn

[ This Message was edited by: shawnmandel on 2002-05-24 13:16 ]

shawnmandel · May 28th, 2002, 08:57 AM

I realize this looks like a big project, but I just require some hints as to how I would approach my Excel problem.

Thanks very much,
Shawn

[ This Message was edited by: shawnmandel on 2002-05-28 07:58 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 23rd, 2002, 02:55 PM	#1
shawnmandel New Member Join Date: May 2002 Posts: 5	I have 3 Excel worksheets: Customer (Contains Customer # and Customer Discount %), Product (contains Product # and Product Cost), and CostAnalysis (contains both the Product Cost and Customer Discount column). I need to ask a user for a specific customer and product combination taken from the Customer & Product worksheet and for the costing and discount info to be populated automatically in the CostAnalysis worksheet. How would I proceed to execute this type of action in the most efficient possible manner? Any help would be greatly appreciated. P.S.: Please refer to my 3rd post for all the details. Thanks very much, Shawn [ This Message was edited by: shawnmandel on 2002-05-24 12:10 ]

May 23rd, 2002, 08:51 PM	#2
Jay Petrulis MrExcel MVP Join Date: Mar 2002 Location: Chicago, IL USA Posts: 2,042	How are you asking the user for this information? Are you using a UserForm, or cells on a worksheet? If on a UserForm, and with Combo Boxes, the "OK" (or such) button click can load the values to the cost worksheet. Event macros also might work. So, too, might worksheet formulas. How is your data set up on each sheet? Please provide as many details as you can to get a good answer. Jay

May 24th, 2002, 09:22 AM	#3
shawnmandel New Member Join Date: May 2002 Posts: 5	My next post contains all the sample spreadsheets that are involved in my issue. Thank you very much in advance for your input. Shawn [ This Message was edited by: shawnmandel on 2002-05-24 11:25 ]

May 28th, 2002, 08:57 AM	#5
shawnmandel New Member Join Date: May 2002 Posts: 5	I realize this looks like a big project, but I just require some hints as to how I would approach my Excel problem. Thanks very much, Shawn [ This Message was edited by: shawnmandel on 2002-05-28 07:58 ]