LOOKUP

[GillianX]

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,

 

[Squeaky]

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