Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP
Hello,
I'm really new to this so bear with me. I have got a worksheet with a drop down list for fees (drop down list options below) and check boxes for each of the limits of indemnity, £250k, 500k etc. The user can select only one option from the fees, but can select one, a few or all of the limits of indemnity. I have other cells to show the premium (the 125, 135 figures etc). What I need to do is populate the premium cells (bear in mind it could be four if four options selected), dependant on the fee option selected and the limits selected. Raw data below:- limit of indemnity Fees £250k £500k £1m £2m 0 to 50000 125 135 150 250 50001 to 100000 165 180 200 300 100001 to 150000 200 225 250 350 150001 to 200000 225 250 275 375 200001 to 250000 250 275 300 400 250001 to 300000 275 300 325 425 300001 to 350000 300 325 350 450 350001 to 400000 325 350 375 475 400001 to 450000 350 375 400 500 450001 to 500000 375 400 425 535 I hope someone can help with this. I've looked at the lookup functions but can't get my head around it. Thanks, |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP
Hi GillianX,
If I am understanding this correctly, you want to be able to select a fee using your drop down box, and based on what is selected, the prices on the rows will add up and be placed in a "Total Cost" cell. I have set it up this way. Some cell rows and columns I have selected arbitrarily. We will set up the first row. The other rows will be set up the same way. The fee ranges are in column Q5 through Q14. (out of the way.) The selection drop down box is a FORMS drop down box linked to the range Q5:Q14. The cell link for the drop down box is A5. Place the drop down box somewhere over cell A5 for now. Using the FORMS drop down box will place a number (1 through 10) in the linked cell when you make a selection. We will use this for our lookup formula. An ACTIVE X box will put the value such as 0-50000. If you must use this kind write back and I can explain how to use it here. Column B contains the numbers 1 through 10 in cells B5 through B14. This row will be hidden later and will be what the lookup formula will look for. Cell C5 will contain the first checkbox. It will be linked to cell C5. The rest will go down the column below it, each linked to its own cell. Linking will take the most time. D5 Will contain 125, E5 will have the second checkbox, F5 will have 135, and so on accross and down the worksheet. The last number of the first row, 250, will be in cell J5. In cell K5 put the formula =IF(C5=TRUE,D5,0) In cell L5 put the formula =IF(E5=TRUE,F5,0) In cell M5 put the formula =IF(G5=TRUE,H5,0) In cell N5 put the formula =IF(I5=TRUE,J5,0) In cell O5 put the formula =SUM(K5:N5) Basically the cell in Column O will total what is checked in that row. You can drag the formulas down to copy them to row 14. Now for the vLookup. This is your total. In Cell P5 put this formula: =VLOOKUP(A5,(B5:O15),14,FALSE) Now for the cleanup. Hide columns B, K, L, M, N, O, Q In columns C, E, G, I change the letter color to match the background color of the cell. If the background is white, select white, not auto. Place your drop down box over cell A5 to hide the number below it, and adjust the rest to meet your needs. Let me know how it works. Squeaky "GillianX" wrote: Hello, I'm really new to this so bear with me. I have got a worksheet with a drop down list for fees (drop down list options below) and check boxes for each of the limits of indemnity, £250k, 500k etc. The user can select only one option from the fees, but can select one, a few or all of the limits of indemnity. I have other cells to show the premium (the 125, 135 figures etc). What I need to do is populate the premium cells (bear in mind it could be four if four options selected), dependant on the fee option selected and the limits selected. Raw data below:- limit of indemnity Fees £250k £500k £1m £2m 0 to 50000 125 135 150 250 50001 to 100000 165 180 200 300 100001 to 150000 200 225 250 350 150001 to 200000 225 250 275 375 200001 to 250000 250 275 300 400 250001 to 300000 275 300 325 425 300001 to 350000 300 325 350 450 350001 to 400000 325 350 375 475 400001 to 450000 350 375 400 500 450001 to 500000 375 400 425 535 I hope someone can help with this. I've looked at the lookup functions but can't get my head around it. Thanks, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Matrix lookup/mulitple criteria lookup | Excel Discussion (Misc queries) | |||
Get Cell Address From Lookup (Alternative to Lookup) | Excel Worksheet Functions | |||
Join 2 Lists - Lookup value in 1 list & use result in 2nd lookup | Excel Worksheet Functions | |||
Sumproduct - Condition based on lookup of a Lookup | Excel Discussion (Misc queries) | |||
Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) |