Sum of several Vlookup values ...

[dollar]

hi all,

kindly have a look on the attached sheet.

I need a macro to calculate the order value i.e when i fill in a qty against any code a macro would execute and get the rate of that code from (rate file worksheet) and multiply that value with the fill in qty and display it and also, when i fill in a qty against another code the macro should perform the same procedure but in this case it would add the value to the last value and show the combined total value for the order,


Thanks.

[DonkeyOte]

I would suggest posting a revised sample with some sample values and desired output - I don't understand the significance of the size column and/or how this affects in which column value for given code is to be placed.

[dollar]

Kindly Note :-= for category E :- The rates are same for all sizes

[DonkeyOte]

The simplest thing would to be

a) ensure your table has some sort of consistency - ie introduce Qty column for Cat E which sums Size columns

b) use RateFile sheet to store values via SUMIF (efficient) and simply sum resulting column

see attached

[dollar]

we have a little problem (bug)

we i enter the qty equal to any mentioned code i.e
if i fill in 152 (152 is also a code of some item) in B5 the formula got corrupted and shows wrong value.

[DonkeyOte]

In that case, given your layout, either you would need to split the present SUMIF into 5 SUMIFs and sum results or you would need to revise the approach and revert to SUMPRODUCT such that you could differentiate between code & quantity based on column, eg:

ratefile!C2:
=$B2*SUMPRODUCT((MOD(COLUMN(Dollar!$A$5:$I$23),2)=1)*(Dollar!$A$5:$I$23=$A2)*Dollar!$B$5:$J$23)
copied down as required