sum based on a various conditions

roninn75
3StarLounger
Posts: 236
Joined: 15 Feb 2013, 08:25

sum based on a various conditions

Post by roninn75 »

good day

On a form, when selecting from a dropdown, I wish to look up a related value from a sheet and calculate the sum based on values in related columns.
in the sample workbook, I included a subset of data. I have a form with three dropdown boxes and a text box. The values in the drop down box (1, 2 or 3) is based on a named range on Sheet1 column A.
when selected value "1" in the dropbox, check what was selected in the 2nd dropdown, obtain the monetary value in the sheet based on the fee for that level (1, 2 or 3), check what was selected in the 3rd dropdown, obtain the monetary value for the selected level and and calculate the fee.
So, if I select "1" in the first dropdown, and I selected "0220" in the facility code in dropdown 2 and then select "0222" with the tariff code in dropdown 3, in order to calculate the total fee, based on the selection, I would have to calculate:

BxLevel value = "1" (A2)
BxFacilityCode = "0220" value = 7214 (E4)
BxTariffCode = "0222" value = 3208 (I13)
TxtTariff value = A2 * (E4 + I13)

if the value in BxLevel had to change:
BxLevel value = "3" (A4)
BxFacilityCode = "0220" value = 8395 (G4)
BxTariffCode = "0222" value = 3208 (M13)
TxtTariff value = A4 * (G4 + M13)

change the BxLevel value
BxLevel value = "2" (A3)
BxFacilityCode = "0320" value = 254 (F6)
BxTariffCode = "0321" value = 51 (L17)
TxtTariff value = A3 * (F6 + L17)

kindly assist
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: sum based on a various conditions

Post by HansV »

Check out the attached version.

sample-2.xlsm
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

roninn75
3StarLounger
Posts: 236
Joined: 15 Feb 2013, 08:25

Re: sum based on a various conditions

Post by roninn75 »

this works thanks