Data Validation and Formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Hasnain - 01 Mar 2008 09:09 GMT

Hello,

I'm making a hotel worksheet.. I have a dropdown menu at B2 with these
options (Single, Double and Suit), and on C2 I have an Adavance column (If a
guest pays any money in advance).

The prices are as follows Single = 39000, Double = 45000 and Suit = 85000

I want the BALANCE at cell E2 should calculate the BALANCE

e.g If I select "Single" from B2 Drop Menu and the guest has paid ADVANCE
10000, the BALANCE should automatically calculate 39000 - 10000 = 29000. The
same way if I select suit or Double it should consider the room rate and
minus the ADVANCE (if paid) and show the value.

Please help.

Hasnain
hasnain98@msn.com

Reply to this Message

FSt1 - 01 Mar 2008 11:14 GMT

hi
since you are using a validation list, i assumed that you have your list off
to the side somewhere. i put my list at J2:J4 for testing purposes only. next
to the list of Single, double, suite, in K2:K4, i put the values 39000,
45000, 85000.
in E2 i put this formula....
=IF(B2="single",K2-C2,IF(B2="double",K3-C2,IF(B2="Suite",K4-C2,"")))
or if you don't want the values listed on the sheet....
=IF(B2="single",K2-39000,IF(B2="double",K3-45000,IF(B2="Suite",K4-85000,"")))
either way works. adjust to fit your data.

Regards
FSt1

> Hello,
>
[quoted text clipped - 15 lines]
> Hasnain
> hasnain98@msn.com

Reply to this Message