Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
data validation formula | Excel Discussion (Misc queries) | |||
Can I use formula with data validation? | Excel Discussion (Misc queries) | |||
data validation formula | Excel Discussion (Misc queries) | |||
Help with Formula (data validation maybe?) | Excel Programming | |||
Data validation Formula | Excel Discussion (Misc queries) |