Excel Input once cells

[setishock]

I'm putting together a spread sheet for the other property's we own so the accounting office gets the same printout from each one. Having that would make it easier all the way around.
Since the other property's have different room rates the one I made for me would have to have the room rates adjusted to suite each location. I'd like to get around having to set them in stone. Instead I'd like to set it up so the first time you use it you input your room rates and it continues to use them until you change them. But doing it that way would require the room rate input to be injected in to the formula in the right places.
I like the idea but how to program it is over my head. Google hasn't been much help as hotel software is a nitch business and normally played close to the vest.
So any ideas as to how to make this work or any good websites to learn how on my own?

 

[ssc456]

Hi Seti,

I'm not 100% sure on what you are trying to achieve but it sounds like what you want to do could be done by injecting the formulas using VBA that is triggered upon a cell value change?

Something along the lines of this. . .

Dim roomRate1 As Double
roomRate1 = Range("A1").Value

Dim roomRate2 As Double
roomRate2 = Range("A2").Value

    Range("E5").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-2]=""Rate1"",R[-1]C[3]*" + CStr(roomRate1) + ",R[-1]C[3]*" + CStr(roomRate2) + ")"

 

[jmacavali]

Can't you just put the rate in cell A1 (or where ever) and then just use A1 in the formula where you need the room rate?

 

[setishock]

You know I was entertaining the same idea after I posted here. I'm going to make a copy of the template and give it a go. Here's a screen shot of what I'm using now.

 

[setishock]

It's ugly but it works >

Note in the before version, the price of the rooms was a part of the formula itself. In the updated version the price is now a cell coordinate.
Now when handed out to the other properties, the manager puts in the single and double room rates and using "save as" saves the template under the property name. That leaves the distribution copy as is in case of a screw up. Now I've got to make one for the bars we have. Should be interesting.

 

[strollin]

I'm curious as to why you say "It's ugly but it works"? As far as a spreadsheet goes, it's pretty commonplace.

How many different properties are there? If you know that and what their rates are, you could create a table listing the properties and their rates, then in your formula, use something like the VLOOKUP function to lookup the rates for the property. You could then create a cell with a dropdown list of the various properties and each manager would only need to select the property from the dropdown and the rates for that property would automatically be used in the formula.

Here's a link to info about how to create the dropdown list: http://office.microsoft.com/en-us/excel-help/create-or-remove-a-drop-down-list-HP005202215.aspx

You could put the table on sheet 2 so it wouldn't be seen and then your spreadsheet wouldn't be "ugly".

In the example below, when the property is selected from the dropdown list, the values for C1 & D1 would be looked up in the table with the formula "=VLOOKUP(B1, G20:I23, 2)" in cell C1, and "=VLOOKUP(B1, G20:I23, 3)" in cell D1.

 

[setishock]

OK then is there a way that after the property is selected to make the drop down menu no longer available?
I'm trying to keep the document as clean as possible. The template gets filled out and then printed out. The print out goes to our accounting office to be dealt with. With new hires in the property management and the accounting office the goal is to use the KISS method. The crew at the accounting office knows I'm working on this so they put up with all the BS on the report. In the final version I'd like to have all the setup selections and input vanish. It would be in the background just not visible. Possible?

 

[strollin]

The other selections shown in the dropdown only display when the arrow on the right-hand side of the cell is clicked. Once the selection is made, then only the selected value is displayed. As far as the lookup table itself, it can be put on another sheet so would never need to be seen. The sheet it put on could even be hidden so couldn't be seen.