Show a cell range on selecting a cell value

[pal_slg]

In excel is it possible to show a range of cells on selecting a value in a specific cell.
For Example - I want to create a database for different Hotel tariff. Once I select a Cell value from its drop down list (B2 = "Suite"), the corresponding cells (E2,F2,G2) will show the different rates of Suite at one go

E2 will show Suite double rate (say $500)
F2 will show Suite Single rate (say $450)
G2 will show Suite Extra Person rate (say $50)

Attached sheet (Sheet1 with data, Sheet 2 with Output)

Thank you for your help

[teylyn]

Hello,

you can do this with a lookup formula, for example Vlookup

=VLOOKUP($B2,Sheet1!$A$8:$D$10,COLUMN(B1),FALSE)

copy across.

But you would have to change the formula for the next row of calculations to refer to the table with the other hotels.

If you lay out your data slightly differently, then you can use just one formula copied across AND down.

Please Login or Register  to view this content.

See attached

[Marcol]

Try this

Re-arrange Sheet1 as shown in this workbook.
Make named list "Hotel_Name" dynamic.
i.e.
Refers To:=

Please Login or Register  to view this content.

Then in Sheet2 E2

Please Login or Register  to view this content.

Drag Across to Column G, then Down as required.

Hope this helps

[teylyn]

Marcol, did you mean to attach a file? Or which is "this workbook" you are referring to?

Offset and Indirect are volatile and should be used very sparingly.

[Marcol]

Could have sworn I attached the workbook, corrected now ... thanks.

I agree about the volatility issue, but in this case I can't see this being a problem. Unless of course the OP has to lookup hundreds of tarrifs at a time.

[teylyn]

You'll never know how big a file will end up, so even if the data sample is small, the file may be massive in real life.

Marcol, the suggested data layout is not very efficient. Imagine hundreds of hotels.

[pal_slg]

Marcol and Teylyn

Thank you both for helping. I was having a hard time about how to fix this. This solved my problem.