I need to design a worksheet where I need to put in 7 criteria. My patients
can get admitted in "Superdeluxe room" "Deluxe Room " "ICU" "General ward" ,
"ICU And then Deluxe room " Etc. If I have the case function in Excel then, I
can tell excel to put "a " amount for the "Deluxe room, "Y" amount for the
superdeluxe room etc. The IF function is too cumbersome.

Why not set up a simple table with the room types and the costs and then do a
look-up?

HTH.

"Dr Alok Modi MD" wrote:

I need to design a worksheet where I need to put in 7 criteria. My patients
can get admitted in "Superdeluxe room" "Deluxe Room " "ICU" "General ward" ,
"ICU And then Deluxe room " Etc. If I have the case function in Excel then, I
can tell excel to put "a " amount for the "Deluxe room, "Y" amount for the
superdeluxe room etc. The IF function is too cumbersome.

Hi Dave
Thanks
But How do i put up a table and do a look up in Excel. This is possible in
access?. Would it be possible to give me small sample demo?
Dr Alok Modi MD

"DaveO" wrote:

Why not set up a simple table with the room types and the costs and then do a
look-up?

HTH.

"Dr Alok Modi MD" wrote:

I need to design a worksheet where I need to put in 7 criteria. My patients
can get admitted in "Superdeluxe room" "Deluxe Room " "ICU" "General ward" ,
"ICU And then Deluxe room " Etc. If I have the case function in Excel then, I
can tell excel to put "a " amount for the "Deluxe room, "Y" amount for the
superdeluxe room etc. The IF function is too cumbersome.

OK, here's a quick example. On Sheet1 have something like this in cells A1 to
B5

Superdeluxe Room £200
Deluxe Room £175
ICU £150
General Room £100
ICU then deluxe room £185

Then in Sheet2, where you have your patient records you'd have something
like this ...

Mr E Xample Superdeluxe Room
Mrs Bloggs ICU
Mrs Jones General Room

In the next column (Column C in my example) you'd have this formula...

=vlookup(B1, Sheet1!A1:B5, 2, False)

You'll need to alter the B1 for each row, but a simple drag and drop would
work for this.

HTH.

"Dr Alok Modi MD" wrote:

Hi Dave
Thanks
But How do i put up a table and do a look up in Excel. This is possible in
access?. Would it be possible to give me small sample demo?
Dr Alok Modi MD

"DaveO" wrote:

Why not set up a simple table with the room types and the costs and then do a
look-up?

HTH.

"Dr Alok Modi MD" wrote:

I need to design a worksheet where I need to put in 7 criteria. My patients
can get admitted in "Superdeluxe room" "Deluxe Room " "ICU" "General ward" ,
"ICU And then Deluxe room " Etc. If I have the case function in Excel then, I
can tell excel to put "a " amount for the "Deluxe room, "Y" amount for the
superdeluxe room etc. The IF function is too cumbersome.

Since VLookup is picky about spelling when you want an exact match (and to
avoid some typing), you might also consider using Data Validation on the
cells where you will be entering the room description. Select
Data/Validation, select List and enter the range reference to the first
column of the lookup table that has the room descriptions.

If your data and lookup table are on separate sheets, you will need to name
the first column of the lookup table to get data validation to work (select
the cells, then enter a name in the name box in the upper left corner of the
spreadsheet). Then in the data validation dialogue you will use =RoomDesc
for the source, where RoomDesc is the named range.


"Dr Alok Modi MD" wrote:

Hi Dave
Thanks
But How do i put up a table and do a look up in Excel. This is possible in
access?. Would it be possible to give me small sample demo?
Dr Alok Modi MD

"DaveO" wrote:

Why not set up a simple table with the room types and the costs and then do a
look-up?

HTH.

"Dr Alok Modi MD" wrote:

I need to design a worksheet where I need to put in 7 criteria. My patients
can get admitted in "Superdeluxe room" "Deluxe Room " "ICU" "General ward" ,
"ICU And then Deluxe room " Etc. If I have the case function in Excel then, I
can tell excel to put "a " amount for the "Deluxe room, "Y" amount for the
superdeluxe room etc. The IF function is too cumbersome.

Thanks
I got my work done with the IF function. It took some time , but it has been
done.
Thanks all of you.
I however have a suggestion. It would be nice if microsoft would publish a
book with real life examples of all the functions, because the help they
provide just gives the syntax which is very confusing for non IT persons like
us.

"JMB" wrote:

Since VLookup is picky about spelling when you want an exact match (and to
avoid some typing), you might also consider using Data Validation on the
cells where you will be entering the room description. Select
Data/Validation, select List and enter the range reference to the first
column of the lookup table that has the room descriptions.

If your data and lookup table are on separate sheets, you will need to name
the first column of the lookup table to get data validation to work (select
the cells, then enter a name in the name box in the upper left corner of the
spreadsheet). Then in the data validation dialogue you will use =RoomDesc
for the source, where RoomDesc is the named range.


"Dr Alok Modi MD" wrote:

Hi Dave
Thanks
But How do i put up a table and do a look up in Excel. This is possible in
access?. Would it be possible to give me small sample demo?
Dr Alok Modi MD

"DaveO" wrote:

Why not set up a simple table with the room types and the costs and then do a
look-up?

HTH.

"Dr Alok Modi MD" wrote:

I need to design a worksheet where I need to put in 7 criteria. My patients
can get admitted in "Superdeluxe room" "Deluxe Room " "ICU" "General ward" ,
"ICU And then Deluxe room " Etc. If I have the case function in Excel then, I
can tell excel to put "a " amount for the "Deluxe room, "Y" amount for the
superdeluxe room etc. The IF function is too cumbersome.