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

Sep 27, 2013

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.

Mar 31, 2014

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.

## Calculating Shipment Rates Base On Origin City And Destination City?

Apr 4, 2014

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.

## How To Convert The Numerical Value For City To A Text String With Actual City Name

Jan 30, 2014

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:

excel help forum 2.xlsx

024 = Hurst
026 = Fort Worth
025 = Euless
017 = Mansfield
013 = Keller

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.

## IF/VLOOKUP (set Up A Spreadsheet Which Automatically Calculates The Start And End Dates Of Project Tasks)

Feb 1, 2009

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.

## Build A Spreadsheet Containing Data Validation (drop-downs)?

Aug 12, 2014

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.

## Excel - Add Background Picture To Spreadsheet That Only Shows On Print Area

Aug 29, 2012

I need to add a background picture to a spreadsheet that only shows on the print area.

This spreadsheet is being used as a pro forma invoice that can be emailed to customers for them to view & print as required.

At the moment I can add a background image but this appears throughout the sheet and not in the place I need it to i.e. the print area.

## Sum Of Guests At Hotel On Given Date?

Jul 31, 2013

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?

## Report Of Hotel Bookings Based On Check-In/Out Dates

Apr 9, 2008

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

## Ceiling & Floor Together

Sep 25, 2007

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.

=IF(D422,FLOOR(D422*10%,100),9750)

## FLOOR Function In UDF

Dec 20, 2006

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.

## FLOOR And CEILING Functions

Oct 9, 2009

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:

.875
.8906
.9062
.9218
.9375

*As seen here, .9218 is closer to .9375

## Price Calculation With Floor And Ceiling

Jul 20, 2007

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.

## Ceiling, Round And Floor Functions

May 7, 2007

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

## Trying To Create Macro That Selects A Ceiling And A Floor

Jan 31, 2008

I am trying to write a macro, but am failing at it.

Basically, what I am trying to do is:

----------------------

Select a whole row, for example 5
Define that row as "Ceiling"

Then select a row a few steps down, for example 18
Define that row as "Floor"

Then select the range (ceiling, floor)

Then print that area.
-----------------------

But I have no idea how to write the code.

## Centre Columns In Macro

Nov 9, 2006

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.

Sheets("Journal").Columns(6).HorizontalAlignment = x1HAlignCenter

or

Range("F:F").HorizontalAlignment = x1Center

## Shape With Hollow Centre

May 29, 2008

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?

## Way To Check Whether Any Cost Centre Has Been Missed Out ...

Nov 20, 2009

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.

## Modify Code To Always Centre The Picture

Dec 3, 2008

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

## Conditional Formatting (largest Number In Column B The Hotel In Column A Should Be In Bold)

Oct 18, 2008

when the largest number in column B the hotel in column A should be in bold.

So in excel language IF(Number in B Is Max display corresponding hotel in column A as BOLD. But I can't figure out how to do this.

You can see here on the image:

## Print 2 Selected Area (highlighted Area)?

Apr 11, 2013

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

## Macro For Merge And Centre The Data In Column

Feb 26, 2009

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

## Find Centre Of 6 Figure National Grid References

Feb 6, 2014

Is it possible to find the centre point of these 6 figure National Grid References (sample)

345723842214
346094842080
346087842611
346359842379
346502841967

These are point on a map - these points will vary and the number of point will vary too,

## Province And City Dropdowns

Sep 5, 2013

I have
Userform
Textbox1
Textbox2

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

Here is the picture : [URL] .....

## VBA Calculates Sum If From CSV Workbook

May 2, 2014

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

[Code].....

## Calculates Faster Than IF

Aug 21, 2009

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

## IF Function Only Calculates First Value?

Sep 9, 2013

I have written an IF formula that only clacluates the first IF values in the formula and I dont know why.

Here is my formula: =IF(I2999;"1000";IF(I2>1999;"2000";IF(I2>2999;"3000";IF(I2>3999;"4000")))))

So if in I2 I have the value 800 it correctly returns

## Data Validation Of Country And City

Aug 5, 2014

Data validation using named ranges as countries are listed multiple times and to relate country with cities.

How can i relate country with city in two different columns(named ranges) not in order.

Formula to be used for Data Validation would be useful.

Sample data file is attached.

Its just a sample.data can be for 250 rows.

Book1.xlsx‎

## Move Only City From Address To Next Column?

Dec 3, 2013

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