missdeannamarie
New Member
- Joined
- Mar 31, 2014
- Messages
- 25
Hello!
I am really hoping someone can help me out with an excel question I am stumped on!
I will do my best to try and explain what I am trying to accomplish. I have a file with 3 different tabs. Tabs 2&3 are purely informational, which I want to pull data from. I need a formula that will take two selections from Tab 1, and reference those in Tabs 2&3 in order to pull the correct data. Each tab has column headers.
Here is what I am trying to do:
In Tab 1, when a customer is entered (Tab 1: Col B), I'd like a formula that will look up that customer in tab 2 (Tab 2: Col A), and find what freight rate they are assigned (Tab 2: Col B), then based on the type of freight entered in tab 1 (Tab 1: Col C), it would take the rate that is assigned to the customer and enter in the rate that corresponds to the freight type in tab 3 (Tab 3: Col B-F). I'd like that data returned in (Tab 1: Col J)
Basically, I need the rate in Tab 1: Col J to change based on the customer chosen.
I'd appreciate any help!!! Thanks!!!
TAB 1 - DATA
<colgroup><col><col><col><col><col><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>
TAB 2 - CUSTOMERS
<colgroup><col><col><col><col><col span="2"><col></colgroup><tbody>
</tbody>
TAB 3 - RATES
<colgroup><col><col span="5"><col></colgroup><tbody>
</tbody>
I am really hoping someone can help me out with an excel question I am stumped on!
I will do my best to try and explain what I am trying to accomplish. I have a file with 3 different tabs. Tabs 2&3 are purely informational, which I want to pull data from. I need a formula that will take two selections from Tab 1, and reference those in Tabs 2&3 in order to pull the correct data. Each tab has column headers.
Here is what I am trying to do:
In Tab 1, when a customer is entered (Tab 1: Col B), I'd like a formula that will look up that customer in tab 2 (Tab 2: Col A), and find what freight rate they are assigned (Tab 2: Col B), then based on the type of freight entered in tab 1 (Tab 1: Col C), it would take the rate that is assigned to the customer and enter in the rate that corresponds to the freight type in tab 3 (Tab 3: Col B-F). I'd like that data returned in (Tab 1: Col J)
Basically, I need the rate in Tab 1: Col J to change based on the customer chosen.
I'd appreciate any help!!! Thanks!!!
TAB 1 - DATA
A | B | C | D | E | F | G | H | I | J | K | |
1 | DATE SHIPPED | CUSTOMER | TYPE OF FREIGHT | ITEMS DELIVERED: COMMENTS/DETAILS | QTY | UNIT OF MEASURE | LENGTH (FT) | WIDTH (FT) | SQ. FT. CALC. | RATE | COST |
2 | 1/17/2017 | CUSTOMER A | TYPE A | ABC | 1.00 | ea | |||||
3 | 1/18/2017 | CUSTOMER B | TYPE B | DEF | 1.00 | ea | |||||
4 | 1/19/2017 | CUSTOMER C | TYPE C | GHI | 1.00 | ea | |||||
5 | 1/20/2017 | CUSTOMER D | TYPE D | JKL | 1.00 | ea | |||||
6 | 1/21/2017 | CUSTOMER E | TYPE E | MNO | 1.00 | Sq Ft | |||||
7 | 1/22/2017 | CUSTOMER F | TYPE F | PQR | 1.00 | Sq Ft | |||||
8 | 1/23/2017 | CUSTOMER G | TYPE G | STU | 1.00 | Sq Ft |
<colgroup><col><col><col><col><col><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>
TAB 2 - CUSTOMERS
A | B | C | D | E | F | G | |
1 | Customer | RATE TYPE | Address | Phone | Attachments | Notes | |
2 | CUSTOMER A | RATE 1 | 555, FAKE LANE, WA 55555 | 555-555-5555 | ABC@ABC.COM | ||
3 | CUSTOMER B | RATE 2 | 123, FAKE LANE, WA 55555 | 555-555-5556 | ABC@ABC.COM | ||
4 | CUSTOMER C | RATE 3 | 777, FAKE LANE, WA 55555 | 555-555-5557 | ABC@ABC.COM | ||
5 | CUSTOMER D | RATE 4 | 888, FAKE LANE, WA 55555 | 555-555-5558 | ABC@ABC.COM | ||
6 | CUSTOMER E | RATE 5 | 999, FAKE LANE, WA 55555 | 555-555-5559 | ABC@ABC.COM | ||
7 | CUSTOMER F | RATE 1 | 111, FAKE LANE, WA 55555 | 555-555-5510 | ABC@ABC.COM | ||
8 | CUSTOMER G | RATE 1 | 222, FAKE LANE, WA 55555 | 555-555-5511 | ABC@ABC.COM |
<colgroup><col><col><col><col><col span="2"><col></colgroup><tbody>
</tbody>
TAB 3 - RATES
A | B | C | D | E | F | G | |
1 | Freight Type | RATE 1 | RATE 2 | RATE 3 | RATE 4 | RATE 5 | Unit of Measure |
2 | TYPE A | $15.00 | $20.00 | $20.00 | $25.00 | $40.00 | ea |
3 | TYPE B | $35.00 | $40.00 | $40.00 | $45.00 | $70.00 | ea |
4 | TYPE C | $35.00 | $40.00 | $40.00 | $45.00 | $70.00 | ea |
5 | TYPE D | $15.00 | $20.00 | $20.00 | $25.00 | $40.00 | ea |
6 | TYPE E | $1.65 | $1.85 | $1.85 | $2.65 | $8.60 | Sq Ft |
7 | TYPE F | $1.65 | $1.85 | $1.85 | $2.65 | $8.60 | Sq Ft |
8 | TYPE G | $1.85 | $2.00 | $2.00 | $2.65 | $8.60 | Sq Ft |
<colgroup><col><col span="5"><col></colgroup><tbody>
</tbody>