Hello all,
I am working on a rate card for a client but stuck with how to formulate the summary, auto calculator page.
I have basic level knowledge of Excel formula (tried using index match but seems like I am not good at that one !!)
Data I have:
1. Drop down list of suppliers (out of 10) e.g A, B, C, D, E, F, G, H, I, J
2. Drop down list of service level (out of 3) e.g X, Y, Z
3. Drop down list of product category (out of 2) e, g i, ii
2. Master data (rate card) with rates per supplier / service / Product category/origin/destination and charges Headlines (col A: BF)
(columns)
Supplier Origin Destination Service Product Rate a Rate b Rate c
A Ham Syd Y ii 2.5 3.0 4.0
A Ham Syd Z ii 4.0 5.0 6.0
A Ham Syd Z i 7.0 9.0 10.0
Outcome required after formula application:
1. Once a supplier is selected, show origin & destination associated with that supplier in selected cells marked Origin and destination
2. then, once service and product categories selected from drop down list, return rates from rate card into the summary page.
example:
(selected)
Supplier : A
Service : Z
Product: ii
Rate a :
Rate b :
Rate c :
Thanks a lot for you kind assistance in advance.
Ta
I am working on a rate card for a client but stuck with how to formulate the summary, auto calculator page.
I have basic level knowledge of Excel formula (tried using index match but seems like I am not good at that one !!)
Data I have:
1. Drop down list of suppliers (out of 10) e.g A, B, C, D, E, F, G, H, I, J
2. Drop down list of service level (out of 3) e.g X, Y, Z
3. Drop down list of product category (out of 2) e, g i, ii
2. Master data (rate card) with rates per supplier / service / Product category/origin/destination and charges Headlines (col A: BF)
(columns)
Supplier Origin Destination Service Product Rate a Rate b Rate c
A Ham Syd Y ii 2.5 3.0 4.0
A Ham Syd Z ii 4.0 5.0 6.0
A Ham Syd Z i 7.0 9.0 10.0
Outcome required after formula application:
1. Once a supplier is selected, show origin & destination associated with that supplier in selected cells marked Origin and destination
2. then, once service and product categories selected from drop down list, return rates from rate card into the summary page.
example:
(selected)
Supplier : A
Service : Z
Product: ii
Rate a :
Rate b :
Rate c :
Thanks a lot for you kind assistance in advance.
Ta
