Data Validation and total Sum????

[KG121953]

A B C D E F G
1 Amount Item Model Size Supplier
Cost

2


In (A2) I enter # of Items needed
(B2) is a (Validation) Drop down box with a list of Items ranging from 1 to 50
(C2) is a (Validation) Drop down box with a list of Models ranging from 1 to
25
{Each Item comes in at 25 deferent Models}
(D2) is a (Validation) Drop down box with a list of Sizes ranging from 1 to 7
(E2) is a (Validation) Drop down box with a list of Suppliers ranging from 1
to 20
(G2) is the Total Cost for the Item determined by values entered in (B2:E2)
[[Amount (A2)* Cost/per]]
((Cost/per is determined by Model, Size, and Supplier))
To solve for Cost (G2) is fairly easy by the use of formula;
=IF(AND(=" ",=" "), , )
I have a cost sheet for every Item, Model, and Size. By Supplier on a
separate worksheet.
Every thing works great. I get the right amount in (G1) for every scenario.
This would be Great if I need to purchase only (example)
10 of ‘Item 1’ ‘Model 16’ ‘Size H’ from ‘Supplier XYZâ€
However I would also need (example);
2 of ‘Item 38’ ‘Model 4’ ‘Size B’ from ‘Supplier ABC’

So am I right in assuming that I have to have a separate line of every;
Item, Model, Size, and Supplier, like the one above A2:G2 In order to keep
a total Cost of items to be purchased? {[Hmm I don’t even want to think about
the number of lines I would need]}
The Value of (G2) will change in relation to ANY change entered (A2:E2)
Or is there a way to Reset the values in (A2:E2) and SAVE the value in (G2)
in order to get a SUM “Cost†of all Items needed?

 

[Ken Johnson]

A B C D E F G
1 Amount Item Model Size Supplier
Cost

2

In (A2) I enter # of Items needed
(B2) is a (Validation) Drop down box with a list of Items ranging from 1 to 50
(C2) is a (Validation) Drop down box with a list of Models ranging from 1 to
25
{Each Item comes in at 25 deferent Models}
(D2) is a (Validation) Drop down box with a list of Sizes ranging from 1 to 7
(E2) is a (Validation) Drop down box with a list of Suppliers ranging from 1
to 20
(G2) is the Total Cost for the Item determined by values entered in (B2:E2)
[[Amount (A2)* Cost/per]]
((Cost/per is determined by Model, Size, and Supplier))
To solve for Cost (G2) is fairly easy by the use of formula;
=IF(AND(=" ",=" "), , )
I have a cost sheet for every Item, Model, and Size. By Supplier on a
separate worksheet.
Every thing works great. I get the right amount in (G1) for every scenario.
This would be Great if I need to purchase only (example)
10 of 'Item 1' 'Model 16' 'Size H' from 'Supplier XYZ"
However I would also need (example);
2 of 'Item 38' 'Model 4' 'Size B' from 'Supplier ABC'

So am I right in assuming that I have to have a separate line of every;
Item, Model, Size, and Supplier, like the one above A2:G2 In order to keep
a total Cost of items to be purchased? {[Hmm I don't even want to think about
the number of lines I would need]}
The Value of (G2) will change in relation to ANY change entered (A2:E2)
Or is there a way to Reset the values in (A2:E2) and SAVE the value in (G2)
in order to get a SUM "Cost" of all Items needed?

You will have to use macro code to do that.

Once you have finished making changes to A2:E2 (or A2:F2. Did you
deliberately not mention F2?) you could run this single line macro
that will add the value in G2 to H2 (H1 could have the heading "Total
Cost" or similar). G2 already has a formula in it so you can't keep
the total cost there.

Public Sub Sum_Total()
Range("H2").Value = Range("H2").Value + Range("G2").Value
End Sub

If you need to keep a record of all the details of the items
contributing to the total cost you could use this macro that keeps
track of the total cost in H2 and adds the details to the cells below
A2:G2, starting in row 4, leaving row 3 blank as a separator. As each
new row of details is added, the previous rows are shifted down the
sheet so that the most recent addition is on the top (row 4).

Public Sub Save_Details_and_Sum()
Range("A4").EntireRow.Insert
Range("A4:G4").Value = Range("A2:G2").Value
Range("H2").Value = Range("H2").Value + Range("G2").Value
End Sub

Ken Johnson