how to use code to reference a variety of other cells? stumped excel newbie

[arlojwhite]

Hi

Glad i found this forum - already learned a lot.

I am building a spreadsheet that references 'LHA rent rates' listed in other cells.

Example table
Bed rate Code: LHA per week:
0 - £55.00
1 - £91.85
2 - £109.61

(Ie two columns with six cells. The first number before the dash are in a separate column. Each of these £ amounts will be in a separate cell.)

In another part of the sheet, i want to be able to type '0' in Cell 1, and this will trigger a formula in Cell 2 to retrieve the correct £ amount i.e., £55.00 in the 'example table' above. Likewise if i change Cell 1 to '2', then Cell 2 automatically updates to £109.61.

I think this would be a long IF formula in Cell 2, but i have no idea how to do it.

Can someone help me create a formula?

Many thanks in advance

[:) Sixthsense :)]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[arlojwhite]

Hi

Have attached a new spreadsheet to illustrate what i am trying to achieve as you suggested.

Two tabs - before and after.

In the before tab, the greyed out area self-completes from another worksheet (the £ amounts vary depending on area).

I've removed everything else from the worksheet. I want to be able to complete the next bit - white boxes:
A10 - A14
B10 - B14
C10 - C14

The salmon-coloured boxes (D10-D14) should have the formula and self-complete.

I want to be able to change the number reference in the C10-C14 column, which will automatically cause the D10-D14 column to update.

in the AFTER tab, i have typed '1' in C10. This made D10 extract the data from cell C3, so the £91.15 shows. If i were to later change C10 to '4', then D10 updates itself to £155.77.

Hope this helps. I guess the formula in D10 will have 5 options, which will only populate a result if 0-4 is typed in C10. I would like that if anything else is typed in C10 OTHER THAN 0-4, then D10 shows £0.

Hope this helps explain.

Thanks for quick response!

[:) Sixthsense :)]

In D10 Cell

=IFERROR(VLOOKUP(C10,$B$2:$C$6,2,FALSE),"")

Drag it down...

In D16 cell

=SUM(D10:D14)