GypsyPrince
New Member
- Joined
- Jan 16, 2020
- Messages
- 1
- Office Version
- 2019
- Platform
- Windows
I have two tables in my Excel workbook - one a selection source, the other for shipping data.
Note: Because I am more used to databases than spreadsheets, I refer to a column as a field. Also, because of other functionality which requires it, each table also has a named range associated with it.
Table 1 Name = tblRate/rngRate
Field 1 Name = RAT_ID (Number - 0 decimal places)
Field 1 Description = The record's auto-generated I.D. number and recordset index key.
Field 2 Name = RAT_TYPE (Text)
Field 2 Description = The type of courier rate according to which the item is to be shipped.
Field 3 Name = RAT_COST (Currency - 2 decimal places)
Field 3 Description = The cost or price for the shipping type.
Table 2 Name = tblItms/rngItms
Field 1 Name = ITM_ID (Number - 0 decimal places)
Field 1 Description = The record's auto-generated I.D. number and recordset index key.
Field 2 Name = ITM_SKUN (Number - 0 decimal places)
Field 2 Description = The item's associated SKU (stock keeping unit) number.
Field 3 Name = ITM_WGHT (Number - 2 decimal places)
Field 3 Description = The total shipping weight of the item.
Field 4 Name = ITM_RATE (Text - selected from drop-down list)
Field 4 Description = The type of courier rate according to which the item is to be shipped.
Field 5 Name = ITM_COST (Currency - 2 decimal places)
Field 5 Description = The auto-calculated cost or price for the shipping the item.
Goal: When I am working in Table 2 (tblShpg) on Worksheet 1, I need to be able to select a shipping rate type for an item. The shipping rate type (First Class Mail, Priority Mail, UPS Ground, etc...) in Field 4 is selected via a drop-down list which is linked to Table 1 ( =tblRate[RAT_TYPE] ) on hidden Worksheet 2. When that selection is made, I need the cost associated with that selected type ( =tblRate[RAT_COST] ) to be multiplied by the item's weight stored in Field 3, with the result being displayed in Field 5.
Question: My goal would be easy enough to achieve if I were, instead, selecting the actual shipping rate from the drop-down list. However, because my boss insists the shipping type be selected by name from the list, how might I go about extracting the cost associated with the selected shipping type in order to multiply it by the item's weight?
Note: There are more complicated formulas involved for this, which I can do. I really just need to know how to extract the shipping cost that is associated with each shipping rate type selected from the drop-down list. Also, I am well versed in VBA.
Note: Because I am more used to databases than spreadsheets, I refer to a column as a field. Also, because of other functionality which requires it, each table also has a named range associated with it.
Table 1 Name = tblRate/rngRate
Field 1 Name = RAT_ID (Number - 0 decimal places)
Field 1 Description = The record's auto-generated I.D. number and recordset index key.
Field 2 Name = RAT_TYPE (Text)
Field 2 Description = The type of courier rate according to which the item is to be shipped.
Field 3 Name = RAT_COST (Currency - 2 decimal places)
Field 3 Description = The cost or price for the shipping type.
Table 2 Name = tblItms/rngItms
Field 1 Name = ITM_ID (Number - 0 decimal places)
Field 1 Description = The record's auto-generated I.D. number and recordset index key.
Field 2 Name = ITM_SKUN (Number - 0 decimal places)
Field 2 Description = The item's associated SKU (stock keeping unit) number.
Field 3 Name = ITM_WGHT (Number - 2 decimal places)
Field 3 Description = The total shipping weight of the item.
Field 4 Name = ITM_RATE (Text - selected from drop-down list)
Field 4 Description = The type of courier rate according to which the item is to be shipped.
Field 5 Name = ITM_COST (Currency - 2 decimal places)
Field 5 Description = The auto-calculated cost or price for the shipping the item.
Goal: When I am working in Table 2 (tblShpg) on Worksheet 1, I need to be able to select a shipping rate type for an item. The shipping rate type (First Class Mail, Priority Mail, UPS Ground, etc...) in Field 4 is selected via a drop-down list which is linked to Table 1 ( =tblRate[RAT_TYPE] ) on hidden Worksheet 2. When that selection is made, I need the cost associated with that selected type ( =tblRate[RAT_COST] ) to be multiplied by the item's weight stored in Field 3, with the result being displayed in Field 5.
Question: My goal would be easy enough to achieve if I were, instead, selecting the actual shipping rate from the drop-down list. However, because my boss insists the shipping type be selected by name from the list, how might I go about extracting the cost associated with the selected shipping type in order to multiply it by the item's weight?
Note: There are more complicated formulas involved for this, which I can do. I really just need to know how to extract the shipping cost that is associated with each shipping rate type selected from the drop-down list. Also, I am well versed in VBA.
