Variables- Spreadsheet That Calculates The Floor Area Of A New Build City Centre Hotel
Apr 3, 2009
heres the data: [url]
Im meant to produce a simple spreadsheet that calculates the floor area of a new build city centre hotel. The developer is looking at various plots of land that allow differing sizes of floor plates and storey heights. The key variables are the number and type of bedrooms, number of floors and whether the hotel is classed as a premium or budget hotel.
I need to produce a spreadsheet that shows the key variables and the total calculated floor area at the top of the sheet.
I have a spreadsheet that has columns hidden from column Z to the end and rows hidden from row 50 to the end thus when viewing the spreadsheet the user sees the working area and hidden area in blue. What I would like to do is have the spreadsheet window minimize to the size of the spread sheet and also allow the user to drag the window around the screen. Is this possible if so do I need VBA Code.
I have a spreadsheet of over 8000 addresses. I have the street, state and zip but not the cities.
Is there a way I can do a search and mass import of cities from a website, like USPS? I'd like to know if there is something I can do quickly rather than manually look each zip code up and enter it in one by one.
how to match a series of rates for a destination city depending upon the origin city. I have figured out how to match rates from origin city to multiple destination cities but have not figured out how to change the series of rates when the origin city changes. These rates will be calculated on the "calculator" in the excel document depending on the cities chosen.
I have attached a document that shows a simplified version of what I am trying to do.
I have a large database from a central appraisal district. In the database the city is stored as a numerical value. The problem is I need to convert the numerical value for the city to a text string with the actual city name. For example the values to the attached example database are as follow:
The issue is, I need to convert this column of numbers into the string of the actual city name. Is there some type of command, or macro that I could use to automate this process? Attached is a small example copy of the database.
I am trying to set up a spreadsheet which automatically calculates the start and end dates of project tasks, by looking at the order in which tasks need to be completed. I have attached a spreadsheet to show what I am trying to do.
I am trying to build a spreadsheet containing data validation (drop-downs). So far, so good.
My goal:
* I want to have various cells show dropdowns based on the value of a cell (per row) which itself can be changed by the user using a drop down.
* I want to have the same various cells show dropdowns that are also based on the value of a cell which has a fixed value per column (in other words, a header).
I have tried a lot of different approaches, including using the INDIRECT function, named ranges, dynamic ranges using a table, INDEX and MATCH. All have failed.
The main issues I see have to do with the fact that my data has several possible values for each given header and classification. Also all values are not unique.
The best result I have ever got returned a dropdown list based on the header bot returned the entire column rather than limited to the classification values. So for example I had all instances of 'Main Colour' to choose from, not just those of the classification value.
In Cells B4:B13 I have a guests name In Cells C4:C13 I have the guests arrival date In Cells D4:D13 I have the number of days the guest will be in the hotel In Cells E4:E 13 I have the guests departure date
Is there I can use a contif or sumeif or some other formula to give me a sum of guests in the hotel between two dates and on a given date?
I can generate from my hotel software a list that includes the check in and checkout date for a room- and the number of nights for the stay, and the total dollar amount of that stay. if anyone is willing to look what I have so far, I am attaching the smallest sample set of data I can generate and have it useful and I have succeded in using vlookup succesfully to transfer the net room amount onto the first night of each stay on the grid.
worksheet "export tape chart" is the data generated by my hotel software- (except for Col a-- I use this for my two way vlookup match) I can reorder the output as needed for vlookups & offsets
worksheet "first night rate total NA" is where I have pulled the total room rate into the first night of the stay in a grid
worksheet "first night rate total clean" is just a simple cleanup of the first pulling for iserror.........
1. If column D422 is greater then 9,750 then multiply D422 by 10% and then floor it to the nearest 100. But what I am trying to do is;
If D422 is between 9,750 and 9,999 then multiply it by 10% then ceiling it to the nearest 100 which would be 1,000. But if it is equal to or greater the 10,000 then multiply it by 10% then floor it to the nearest 100. So the minimum 10% returned should be 1,000.
I am trying to ask if a truncated number is divisable by 9. e.g. 1234 truncated would be 123. To truncate the number i've tried to devide it by ten and then round it down using the floor or rounddown function. However i get the "Complie Error Sub Or Function Not Defined" error in my User Defined Function
If Floor((i / 10), 1) Mod 9 = 0 Then Do my groove thang End If
The compiler is IDing Floor (or rounddown) as the problem.
Say i got a figure 9.9218. If written "=floor(9.9218,0.0625)", the result i would get is 9.875. However, if the formula was written with the CEILING function, I would get 9.9375.
Now here's the fun part. Is it possible to combine the FLOOR and CEILING function codes into one complete function, where it could determine whether 9.9218 is closer to 9.9375 than 9.875?
The following was taken from my decimal equivalent chart, at spaces of .0156:
I have the following calculation that I use to determine if a price is outside of a floor or ceiling, if it is outside of the range it uses either the floor or ceiling price
=IF($G$76E76,-(($G$76-E76)*F13),0))
the formula is in cell G71 F13 is the total quantity E76 is the ceiling price of $15.00 F76 is the floor price of $7.50 G76 is the calculated price of ($6.21)
In this case the floor of $7.50
I would like to modify the formula to where if you input N/A (or something else) that it will give a result of $0. I do not want to put a zero in the cells for the floor and ceiling price because it will give me a result of $0.
I have a value in a cell that is to one decimal place. I need to round this value to the nearest 0.5 multiple up or down which ever is closer. The value in cell A1 reads 6.6, therefore rounding I want the cell to read 6.5 If the value in A1 is closer to 6 say 6.2 I want the cell to read 6.0
I may be being a bit of a tool here, but I can't find a way to centre colums without selecting the columns (ie using with selection, centre, end with). I've written some variations below, none of which work.
if it was possible to create a shape, such as a rectangle, with a hollow centre so you can see the cell positioned behind it? E.G. the shape could cover cells B3:D5 so you could see cell C4 through the hollow centre/gap?
I attached a workbook and it shows data in row 2 - 960. and in cells BA1574 -BM1604 are the summary by using 'sum if' function. As you can see, row 160 countercheck the results and it shows differences.
So my questions are
1) any formula that i can use to ensure all cost centres are taken up in Col AZ1574 - AZ1604? I am sure that i have missed something out.
2) My goal is to summarise the total of each cost centre. Is there other options to reach this goal apart from using 'sum if' function? I would like to learn something new.
I got this code from someone who is not currently available, I modified it a little so that when I pick a picture it always lands on j96, but I need it to line up exactly on top of the picture underneath it I have also attached a sample. The pictures will always be exactly the same size
How can I print 2 selected area (highlighted area). I know there is a trick to do that. Right now I can only select (highlight) one area to print, but would like to know how to print 2 or more areas at same time
I have data like Below Mentioned in column A and Column B.I Want to Merge and Center the Data in Column based on column A.
For example; I want to Combine the Cell i.e b2 and b3,B4 to B7,No Need to merge B8 Because A8 have a Same name But Differnet City. and so no.
I need a Macro.I have 6000 data in one sheet.I have 18 Sheets like this
DEALER NAME A M MOTORS MALLAPURAM8 A M MOTORS MALLAPURAM AALIANZ AUTOMOBILES NEW DELHI7.35 AALIANZ AUTOMOBILES NEW DELHI AALIANZ AUTOMOBILES NEW DELHI AALIANZ AUTOMOBILES NEW DELHI AALIANZ AUTOMOBILES Noida15 ABHARAN MOTORS UDUPI7.88 ABT MARUTI CHENNAI7.89 ABT MARUTI CHENNAI ABT MARUTI CHENNAI ABT MARUTI CHENNAI ABT MARUTI CHENNAI..................
textbox1 containing the province textbox2 containing the city
If didn't click any value of province the city textbox2 don't have any value and if i click the one of the province the city will now have a value . Is that possible
Sample stock system LOADTESTING.xlsm has ten products. "LoadTesting" shows the opening stock for each. CSV file "Van Sales" has the Stock Number in Col M and the sales figure in Col N. I want the code to Open a CSV file "Van Sales" (which for some reason the site would not upload) Establish the sum total sold for each product (VBA equivalent of SUMIF) Deduct that sum for each item from the opening numbers on "Van Sales" (e.g. Product 001 started with 156 on the van, 92 were sold, so the "revised" van figure should be 64).
Can't get the code to link to the right "ranges" from the Van Sales workbook.
(You will have to change the file path calling the Van Sales file, but if you do get it to work,
VB: Option Explicit Sub GETSALE() Dim Dest As Range, Dest1 As Range, Dest2 As Range, Dest3 As Range, oCell As Range, varResult As Variant
I have an entire row of if statements that basically check other sheets if a number occurs. If it does then it will display "Pending Approval" ,"Open", or "Closed" depending on which sheet the # appears on .. Just wandering if their was a better formula for this..
I have an excel of around 20.000 entries and I need it completed tomorrow for work There is the address in one column, and I need to copy and paste only the city to the next column. Any formulas I tried return an error. The city can have one or more words and it is usually after the zip code. For Example:
PLACE DE LA FONDUE 04360 MOUSTIERS SAINTE MARIE ----> MOUSTIERS SAINTE MARIE 19 AVENUE DE MESSINE 75008 PARIS ----> PARIS 160 BIS RUE DE PARIS 92100 BOULOGNE BILLANCOURT ----> BOULOGNE BILLANCOURT 25 SQUARE DE MONT-LOUIS 95380 LOUVRES ----> LOUVRES
