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
sum based on a various conditions
-
- 3StarLounger
- Posts: 236
- Joined: 15 Feb 2013, 08:25
sum based on a various conditions
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78488
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: sum based on a various conditions
Check out the attached version.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 236
- Joined: 15 Feb 2013, 08:25
Re: sum based on a various conditions
this works thanks