Extract shipping cost associated with shipping type selected from drop-down list...

GypsyPrince

New Member
Joined
Jan 16, 2020
Messages
1
Office Version
  1. 2019
Platform
  1. 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.
 

Attachments

  • Annotation 2020-01-14 094631.png
    Annotation 2020-01-14 094631.png
    4 KB · Views: 15

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

CSmith

Well-known Member
Joined
Jan 13, 2020
Messages
686
Office Version
  1. 365
  2. 2010
  3. 2007
Platform
  1. Windows
  2. Mobile
  3. Web
A simple vlookup against the shipping rate cell should work
 

Watch MrExcel Video

Forum statistics

Threads
1,127,529
Messages
5,625,351
Members
416,096
Latest member
forevans

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top