Formula Help with Rate Sheet
I have a table with rates in cells C3-J31 on a tab called "Rate Sheet". The
information in this tab is similar to below but with more rates. Rows Datasheet - 2 pg Datasheet - 4 pg Brochure - 4 pg Brochure - 8 pg Whitepaper - 12 pg Whitepaper - 16 pg Columns Agency 1 Agency 2 Agency 3 Agency 4 On the tab called "Fall Plan" I have columns that users will fill in. Col A = Type of Content (user can choose from a list of Datasheet, Brochure, Whitepaper) Col B = Agency (users can choose from Agency 1, Agency 2, Agency 3, Agency 4) Col C = # of Pages (user can choose 1,2,4,6,8,12,14,16) Col D = Quantity (users can fill in any # they want) On the tab called "Fall Plan" the user will choose one of the options above in each column (a-d) and based on the criteria the user chooses I'd like the formula to look up the rate on the "Rate Sheet" tab and place the answer in Column E of the "Fall Plan" tab. ie. If the user chooses Agency 1, Whitepaper with 8 pages and a quantity, the answer should be different than if the user chooses Agency 2, Datasheet with 6 pages and a quantity. What formula would I use? |
Formula Help with Rate Sheet
Here's an illustrative sample using index/match:
http://www.freefilehosting.net/download/NDUwOTE= Reading from a rates sheet.xls In Fall Plan, Put in E2: =IF(COUNTA(A2:C2)<3,"",IF(ISNA(MATCH(A2&" - "&C2&" pg",'Rate Sheet'!$C$4:$C$9,0)),"",INDEX('Rate Sheet'!$D$4:$G$9,MATCH(A2&" - "&C2&" pg",'Rate Sheet'!$C$4:$C$9,0),MATCH(B2,'Rate Sheet'!$D$3:$G$3,0)))) Copy down as far as required Col E returns the rates from Rate Sheet for the DV inputs in cols A to C -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Karen Smith" wrote: I have a table with rates in cells C3-J31 on a tab called "Rate Sheet". The information in this tab is similar to below but with more rates. Rows Datasheet - 2 pg Datasheet - 4 pg Brochure - 4 pg Brochure - 8 pg Whitepaper - 12 pg Whitepaper - 16 pg Columns Agency 1 Agency 2 Agency 3 Agency 4 On the tab called "Fall Plan" I have columns that users will fill in. Col A = Type of Content (user can choose from a list of Datasheet, Brochure, Whitepaper) Col B = Agency (users can choose from Agency 1, Agency 2, Agency 3, Agency 4) Col C = # of Pages (user can choose 1,2,4,6,8,12,14,16) Col D = Quantity (users can fill in any # they want) On the tab called "Fall Plan" the user will choose one of the options above in each column (a-d) and based on the criteria the user chooses I'd like the formula to look up the rate on the "Rate Sheet" tab and place the answer in Column E of the "Fall Plan" tab. ie. If the user chooses Agency 1, Whitepaper with 8 pages and a quantity, the answer should be different than if the user chooses Agency 2, Datasheet with 6 pages and a quantity. What formula would I use? |
All times are GMT +1. The time now is 01:36 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com