Lookup Or If Function: Find The Room Nights And Net Room Revenue Values For The Specific Rate Code In The "MATRIX" Workbook

Apr 7, 2009

I am having a bit of a problem creating a formula for this report that I have to update every week. I need to be able to find the room nights and net room revenue values for the specific rate code in the "MATRIX" workbook.

On "CURRENT REPORT," I need D4 to pull up the value on "MATRIX" that equals "Room Nights" in column H and "CONABC" in column J. The same thing needs to happen for F4 but with "Net Room Revenue" in column H. The full report has about 500 of these codes in column J, and I need a formula that I can copy easily and will not be affected if codes are added or removed. This is super last minute - I need to finish this report by tomorrow morning, so take a look at the attachments.

View 2 Replies


Room Reservation Workbook File

Jul 3, 2009

Following on from this thread: Create Room Reservation System

There are 3 problems that i've spent all morning trying to work out but can't:

1: I need to change back the layout of the Gantt chart (as it matches the paper system we have been using here, so it will be easy for the other volunteers to use it) - I did change it back and played with the conditional formatting and macro's but i can't get it too work like the version that AAE changed...

2: If I make a reservation Booking is placed on the gantt chart, though only on the first date (I.E 22/09/09 25/09/09 The first date is blocked but i can still double book the room via 23/09/09) so I need the entire date range blocked out and Im helpless at writing or even attempting to change VB Code

3: Booking rooms and checking availability is great for single rooms, but I have realized that for dorms (upto 10 Beds) then once I book one person in, then I wont be able to book the other 9 beds, which is a pain! Is there a way to have the room list but also a bed list for each room.
So for instance Dorm 1 has 4 beds I book 1 person in it and it still appears as available until I book another 4 people in it, then it blocks me from booking someone else in the room?

View 4 Replies View Related

Room Booking File

Jul 27, 2009

I am attempting without much luck to create a Room Booking system.

I am facing 2 issues:

So far when you enter the guest iformation onthe booking sheet it is copied to a worksheet which stores the booking information.

Then i need it to be copied to a worksheet called Required Gantt - I have attempted conditional formatting and Lookup formulas but i am having difficulty getting the booking displayed on the gantt sheet (blocking out the entire period that the guest will stay)

Also if a guest is leaving on 28/07/2009 and i have another guest arriving on the same date, i can't make a new booking as the old guest is still blocking the room, even though they have left. Is it possible to make it so at 12 noon, checkout time, i can book another person into that room?

View 9 Replies View Related

How To Make Room For Axis Title

Jul 19, 2014

I have a basic line chart. It was generated without a horizontal axis title, and very little room to add one. When I add one, the chart won't resize for it, and it bumps into the numbers on the axis. How do I get the chart to resize so this axis title fits in nicely. This has proven to be surprisingly difficult. Note also that a lot of options are greyed out that I try, and so far un-degreyable.

View 7 Replies View Related

Create Room Reservation System

Jul 1, 2009

Please find it attached.

Copy all of the info from the main page to the guest lit (a new row each time).

From the guest list to the Gannt chart - i did a few tutorials on dynamic gantt charts using conditional formatting but cannot get them to work when based on data on a different worksheet.

Finaly is there a way to check for availabilty on any given date?

View 3 Replies View Related

Booking Room Status Form

Aug 3, 2008

I'm a student who doing a excel spreadsheet. I've stucked in this situation for long time, could any body point for me what i have to do.

1. I have to do a nest lookup, mean that when i look in a date (RoomBookingStatusList) and do a nest lookup in side to find the status of the room (RoomBookingStatusList). In short this help me to check that a room has not already been booked for the arrival date the customer wants (Clientbooking).

2. Next, if possible could anybody tell me how to alert the result. For example, it will turn red if the room is busy.

View 3 Replies View Related

Show Room Occupancy Based On Dates

Aug 17, 2008

I want to a create a formula that reviews a series of start dates and end date, then sums the number of days (if the end and start date are withing a yearly period). i.e.

Column A = Room No
Column B = Start dates
Column C = End dates

Column A references a list of rooms (Room List)
If Column A = Room123
If start date is equal to or greater than 31/12/2007
If end date is less than or equal to 31/12/2008
Calculate the number of days used.

View 4 Replies View Related

Toolbar When Work Papers Are Opened From Within The Engagement File Room

Mar 23, 2009

I am an IT Consultant and my client is doing a workstation refresh. They are a CPA firm. They use software called ProSystem Fx Engagement as a filing closet sort of thing. This has a toolbar Excel when work papers are opened from within the Engagement file room. In the Engagement toolbar there is an icon which allows the user to check all boxes. I have contacted the vendor and they said they know nothing about this and it is unsupported. I am trying to migrate to the user's new machine (helps save alot of time) but kinda stuck in a rut. I am assuming this maybe is a custom macro? I am not an Excel guy, I do server stuff etc... but trying to please my client, I included a screen shot of the toolbar and highlighted the button he would click to make this function work. This is Excel 2003 w/ SP4, and Windows XP Pro w/ SP3

View 5 Replies View Related

Calculate The Solved/unsolved Issues Assigned To A Room Or To An Employee

Sep 25, 2008

location solved? issue employee room 1 ok issue 1 employee 1 room 2 issue 2 employee 2 room 3 issue 3 employee 2 room 4 ok issue 4 employee 1 room 5 ok issue 5 employee 1 room 6 issue 6 employee 1 room 7 ok issue 7 employee 2 unsolved issues room 1= solved issues room 1= unsolved issues employee 1= solved issues employee 1=

what formula could I use to calculate the solved/unsolved issues assigned to a room or to an employee? I use an autofilter on this date, so that I can only show unsolved issues.

View 2 Replies View Related

Index IF Match Formula / Returning Room Number That Matches The Hours Used

Mar 21, 2014

I am trying to find a formula that populates a room number based on the number of hours used. I have a chart that is by building but multiple rooms within each.

Where I am having trouble is when there are more than one room with the same number of hours used then sometimes the formula populates the wrong room number.

Attached is my example spreadsheet of my chart. Book1.xlsx

I have shown what I need, what I have tried and a blank space to show where I need it to go.

View 3 Replies View Related

Two Columns - Lookup Rate Corresponding To Specific Date

Jan 30, 2012

I have 2 cols of dates; col1 effective from, col2 effective to. In col c i have a list of corresponding rates. How can i look up the rate corresponding to a specific date. I've tried lookups and matching formulas but not working.

View 1 Replies View Related

Find The Commission Rate Per Worker Using Lookup Functions

Dec 24, 2005

I have a sheet listing comission rate eg. sales less than $200, the rate is
..5%, less than 300, the rate is 1%..etc.

Then i have another table showing different sales value of different workers. How do i find the commission rate per worker using lookup functions??

View 12 Replies View Related

VBA Code For NPV Function With Variable Interest Rate?

Feb 2, 2014

I would like a function which works like the NPV function except that the interest rate should be variable and a range rather than a single number.

Is it possible to see the code for the excel NPV function so that I can modify it?

Or alternatively has seen such a function?

View 8 Replies View Related

Lookup The Values Contained Within The Matrix To Get Distances Between Them

Oct 8, 2009

I'm updating my database of distances between different cities in our records... but the source that i have to lookup is actually a distance matrix....something like this...


Now i need to lookup the values contained within the matrix to get distances between them. I'm hoping if i can do that i dont have to actually manually sit and list down all the possible combinations of cities and distances between them but directly lookup the distances from this matrix.

View 2 Replies View Related

Find How Much Revenue I Earned

Nov 21, 2007

I own a local restaurant and want to streamline my excel spreadsheets. Currently, I have yearly workbooks (2005, 2006, 2007, etc.) that have all of my revenues included in them, split up monthly by worksheets. I then have lines for each day within the month for the revenues earned at that day.

So, if I want to find how much revenue I earned on March 1st, 2006, I go to my 2006 book, March worksheet, and look at the 1st. Here's where my problem lies.

I want to create a new spreadsheet where all I have to do is type in a certain date (say March 1st, 2006) and all of the information for the date will be automatically displayed on the new sheet.

View 14 Replies View Related

X Condition Lookup: Find The Specific Value Within The Table Or Array

Aug 21, 2008

Given three criteria (category, name and date) I want to find the specific value within the table or array. refer to attachment.

View 4 Replies View Related

Set Up A Lookup Function With Two Lookup Values?

Apr 18, 2008

Is it possible to set up a lookup function with two lookup values? For example, say I have a list of items such as:

1 A 14
1 B 22
2 C 84
4 D 25

I'd like to have the lookup go to the above table and find the number 1 and the letter B and return 22. I can't seem to visualize how to make this work.

View 9 Replies View Related

Find Words Through Lookup Function

Jul 31, 2008

I have an issue with VLOOKUP. By runnig VLOOup we can get the data that there is present in a specified range. But how about to get the data DISPLAYED which is not there in the specified range. I tried combining IF and VLOOKUP functions. I am on a simple project now and I would be happy if I could get the answer immeidately

View 2 Replies View Related

Lookup Specific Cells In Multiple Worksheets To Bring Into One Workbook

Aug 13, 2013

I have a workbook that's setup like a form, with 30+ worksheets in it (lets say this file is called "source.xlsx"). I have information in specific cells I want pulled out in each worksheet and brought in to a new workbook called "Destination.xlsx" and listed in a row.

View 2 Replies View Related

Figure Out How To Interpolate Revenue Values

Nov 7, 2008

I have a question on data interpolation with Excel 2007. Normally, this wouldn't be too much of an issue for me, but for some reason I cannot figure it out.

I'm working on an Income Statement, which is designed for 5 years. I have values 1 and 5, which are given to me (B7: 3,500,000 and F7: 5,200,000). I'm trying to figure out the other 3 years of revenue assuming a growth trend. The values should fill the series B7:F7.

View 9 Replies View Related

Lookup Function To Find Price Between Dates

Dec 23, 2013

I try to find the price between two dates . I used lookup function, but couldn't get the correct result.

See detail at attached file and explained what exactly wanted : rates.xlsx‎

View 4 Replies View Related

Lookup Multiple Values And Then Compare To Get Specific Result?

Mar 29, 2014

I have below table and want to get new order quantity if the closing stock of a particular product is less than or equal to the ROL after viewing that a previous order of the same product has not been placed within the lead time of that particular product even the closing stock is less than ROL.

New Order


- 1st it match the product with the relevant one
- Then compare closing stock parameter
- Then finally look up the previous order and compare it with relevant lead time

View 9 Replies View Related

Lookup Function - Find Value In Column For Value Choose In Cell

May 28, 2014

I have this issue where I get an #N/A error when I i run a VLookup in cell H7. I have posted the worbook.

I want to find the value in column D for the value you choose in cell G2


View 4 Replies View Related

MID Function For Different Lookup Values?

Mar 26, 2014

In my spreadsheet, one cell in every row has a string that contains the invoice number. I would like to get the invoice number in a cell of it's own. The problem is that different vendors use different lengths of characters for their inv #, so I can't do a one size fits all MID formula.

What would be the best way of getting the invoice number?

View 9 Replies View Related

Function To Find Days In Specific Month

Jan 30, 2010

Can Anybody help me with a function that will arange all days from January acorrdingly with the year 2010. I am attacing a sample file to make it clear. I need the function to Check which month is in Cell H1 and arrange the days accordingly to the month and the year.

In my example the code must change the Cell values Like this: ...

View 9 Replies View Related

How To Put Values From One Workbook Into A Specific Cell(s) Of Another Workbook

Apr 25, 2013

I have a workbook that is used to calculate values from various data etc. On completion of calculating the values, eg. £20,000, I want to put the values into specific cells of another workbook template. I can of course do it manually by selecting the cell of the template workbook and do an = then reference the the cell in the calculating workbook.

What I want to happen when all the calculations are complete is that:

1. A new workbook, based on the template, is opened and saved with an appropriate title

2 The values from the calculating workbook are automatically entered into the new (saved) workbook at specific cells

View 1 Replies View Related

Creating Formula To Lookup Rate Using Ranges And Other Criteria?

Mar 26, 2013

I need to know if its possible, and if it is, what the formula would be to get the correct "Rate" to pull based on the criteria given:

User would input the following information:

Zip Code: 56559
Pallets: 3
Weight: 1200

The formula needs to use the following table to use the criteria listed above, to fine the correct "Rate". The "Zip Code" and "Weight" both need to fall between the correct ranges and then match the "Pallet" to find the correct "Rate".

From Zip Code
To Zip Code
Weight From
Weight To


View 1 Replies View Related

Find Workbook Name In Specific Folder

Oct 27, 2006

I am trying to have a worksheet linked to another workbook, but this workbook name will change. It will always start with Blockbuster - Valuation but the rest of the file name is the date and the version of the workbook. Do you know how VBA can go in a folder and look for the entire file name of a workbook starting with Blockbuster - Valuation.

View 3 Replies View Related

IF(AND Function Using LOOKUP For Conditional Values

Dec 3, 2013

I'm new to excel and am attempting to use an IF(AND function that requires the use of two LOOKUP tables for the IF conditions, I have been unable so far to generate the answer I'm looking for.

I have a set of data in two columns, column A which has a series of dates from 1/09/2013 - 30/09/2013 (multiple for each date), and column F which has a series of 4 digit numbers (employee numbers). I also have two columns where I can enter in values for LOOKUP tables, one column for dates (O2:O4) and another for employee numbers (Q2:Q17).

What I am trying to achieve is a True/False answer in a final column for IF A2 = value from column (table) O2:O4, AND F2 = value from column (table) Q2:Q17. For the querry to be true the values in column A and column F must meet both conditions, be from a date in column O2:O4 and also be an employee from column Q2:Q17


Currently I get a #NA error and I know its due to my formula not being anywhere near correct, as far as I can tell it's a result of the LOOKUP part of the formula I'm attempting to create, as it generates a #NA area when the data does not meet the criteria in the columns (tables), I have attempted to include a ISNA section to the formula to alter the result if the data does not meet the two conditions but when applied to the whole data set it only responds with a False answer even if the data meets both conditions.

View 8 Replies View Related

Calling Function That Acts On Matrix Range Within Another Function

Jul 23, 2013

I have a function

Function f1(Matrix As Range)
'Does something and returns f1 = a double
End Function

And a second function which defines and constructs a matrix of doubles to use as an argument in f1 to return a double:

Function f2(dD As Double)
Dim MatrixRed() As Double
Redim MatrixRed(1 To dD, 1 To 10)
For i = 1 To dD
For j = 1 To 10
MatrixRed(i, j) = i * j
f2 = f1(MatrixRed)
End Function

I get an output error (#VALUE). I think it has something to do with MatrixRed not being a range anymore?

View 2 Replies View Related

Copyrights 2005-15 www.BigResource.com, All rights reserved