Data Validation and Formula
 

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

Data Validation and Formula
 

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

"Hasnain" wrote:

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