Go Back   Computer Forums > General Computing > Software and Operating Systems
Click Here to Login
Join Computer forums Today


Reply
 
Thread Tools Search this Thread Display Modes
 
Old 06-19-2013, 07:17 AM   #1
Wizard of Wires
 
setishock's Avatar
 
Join Date: Feb 2005
Location: Not sure
Posts: 10,030
Default Excel Input once cells

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?
__________________

setishock is offline   Reply With Quote
Old 06-19-2013, 09:25 AM   #2
Fully Optimized
 
ssc456's Avatar
 
Join Date: Jan 2007
Posts: 4,279
Send a message via MSN to ssc456
Default Re: Excel Input once cells

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. . .

Code:
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) + ")"
__________________

__________________
He who has never failed has never attempted anything worth succeeding at.

Dont Eat Animals, Its Not Good For Them And They Dont Like It!
ssc456 is offline   Reply With Quote
Old 06-19-2013, 10:53 AM   #3
Fully Optimized
 
jmacavali's Avatar
 
Join Date: Jun 2009
Posts: 4,867
Default

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?
__________________
****************************************
Don't take life too seriously -- no one gets out alive. Plus, who wants to arrive to the hereafter in pristine condition wearing a suit and tie?
I want to slide in sideways, worn out, used up, hair a mess, clothes tattered, & screaming, "Whooo! What a ride!"
****************************************
jmacavali is offline   Reply With Quote
Old 06-19-2013, 10:58 AM   #4
Wizard of Wires
 
setishock's Avatar
 
Join Date: Feb 2005
Location: Not sure
Posts: 10,030
Default Re: Excel Input once cells

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 is offline   Reply With Quote
Old 06-19-2013, 10:49 PM   #5
Wizard of Wires
 
setishock's Avatar
 
Join Date: Feb 2005
Location: Not sure
Posts: 10,030
Default Re: Excel Input once cells

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.
setishock is offline   Reply With Quote
Old 06-20-2013, 08:33 AM   #6
Fully Optimized
 
strollin's Avatar
 
Join Date: Mar 2011
Location: USA
Posts: 1,943
Default Re: Excel Input once cells

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/ex...005202215.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.

strollin is offline   Reply With Quote
Old 06-20-2013, 12:35 PM   #7
Wizard of Wires
 
setishock's Avatar
 
Join Date: Feb 2005
Location: Not sure
Posts: 10,030
Default Re: Excel Input once cells

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?
setishock is offline   Reply With Quote
Old 06-20-2013, 07:31 PM   #8
Fully Optimized
 
strollin's Avatar
 
Join Date: Mar 2011
Location: USA
Posts: 1,943
Default Re: Excel Input once cells

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.
__________________

strollin is offline   Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off



All times are GMT -5. The time now is 08:45 PM.


Powered by vBulletin® Version 3.8.8 Beta 4
Copyright ©2000 - 2017, vBulletin Solutions, Inc.
Search Engine Friendly URLs by vBSEO 3.6.0