Lookup Data Within Data Range And 1 Other Criteria?

Jul 29, 2012

i have a treatment centre with 22 rooms some with mutiple beds, i need to view occupancy in a grid form to optimise bed useage. i need to look up a date that is after arrival but before depature that matches a room number, i have tried match, index, and, vlookup etc but all this info must exist on the same row

View 1 Replies


ADVERTISEMENT

Lookup Data Meeting Criteria

Jun 8, 2007

I am trying to include a series of vlookups to find products by a product code from a database, an example is attached.

I want to type a code into the box at the top and find the results containting but not uniquely being the input.

eg typing 15 in automatically finds 15 using "=VLOOKUP(TRIM($C$6),'Main Database'!1:65536,2,FALSE)" but my database also contains 15eurd and 15rf etc and i want these to appear too.

can i do this with vlookups with some clause that excludes a previously chosen entry?

View 9 Replies View Related

Lookup Data Based On Month Criteria?

Apr 5, 2014

show/display data based on month criteria, with selected month in cell H2 (as start date) and I2 (as end date) my expected result start from cell H7, i called "blue area"..

see my attached workbook..

View 9 Replies View Related

Lookup Data With Matching Criteria String?

May 2, 2014

how to figure it out this lookup problem (lookup using partial string of match)...

View 5 Replies View Related

How Do I Use Lookup To Find Data Based On 3 Criteria

Aug 22, 2008

I have a bit of a challenge. You'll have to take a look at the attached files to understand what I am trying to do. I am asking for your advice. I am creating multiple spreadsheets (QPPcompany.xls) that will refer back to a master data(QPPdata.xls) file.

I am trying to figure out how to best set up the data file so that I can do lookups on the data from the referencing files. Here's the challenge. I need to be able to find in the data page the company name in Column A and then how many L's (or C's or P's) they had in a specified month. So for instance I need to be able to find the number 3 in cell E6 and place it in B15 of the referencing file. The required criteria for lookup are Company Name, Month and then L, C or P.

So my question is, is this possible with how my data file is currently set up? I think it may be a problem to have the month/year in row 4 refer to 3 different colums for each month (L, C and P). I don't mind making changes to the data file to make these lookups possible and I think it may be necessary.

View 14 Replies View Related

Return Data Based On Lookup Criteria

Aug 31, 2006

I have an array in excel, 4 columns: Month, Day, Stock, Price. I dont know the most efficient way to handle arrays. I know i can loop through an entire array to find a specific value, but if i have a large array, this doesnt seem efficient.

For example, I would like to find the price in an Array (or Range), when i know the value of two columns. If this were in a database, I would write sql like this: " select Price from TABLE where month = 1 and Day = 3". The result would be 40.21. How can i do this with an array? I have attached a sample spreadsheet with the example mentioned above..

View 3 Replies View Related

Lookup Data In Another Workbook Based On Criteria

Sep 8, 2006

I have a work book with several work sheets, what I'm trying to do or find a a method of coping data from one sheet to another provided the data in any given cell matches a specific criteria. Example below:

Work sheet 1 is the primary sheet in the work book on this sheet I have a cell with a text value (Bob). Ok on the next work sheet 2 I need to search for a cell that contains (Bob). When or if it is found I need information from other cells in that row (where Bob was found) copied to work sheet 1 in a specified row and colum. If it is not found a negative value can be placed in the specified cells.

View 3 Replies View Related

Copy Data With Criteria For Worksheet And Data Range

Oct 17, 2012

I have a workbook with 3 sheets

Sheet1 - TeamList - Column A is a list of all the team leaders which is used to create a new worksheet for every team. Col B is blank. Column C, D, E are the team lists where teams can consist of anywhere from 10-25 employees.

For example
Column A
Column B
Column C
Column D
Column E

Team Leaders

Team Leader
Employee
Pin

TL 1

TL 1
Emp 1
10001

[Code] ......

Sheet2 "RawHours" A raw data sheet that contains employee hours information

For Example
Employee #
PIN
NAME
Reg
OT
TRN
Stat
Sick
Vac
Berv

2000100
10001
Emp 1
24.00
0.00

[Code] .......

Sheet3 - Template

Basically what I want to be able to do is create a sheet for every team leader and copy and paste there team into the appropriate sheet.

I have the sheet create done by simply copying the template and renaming each sheet using the Team Leaders list from column A from the TeamList sheet. And then I copy the page setup information using the template as well but when it comes to pulling over the appropriate team members in to the appropriate sheet I can not seem to wrap my head around it.

Below is the current code I am using:

Code:
Option Explicit
Sub CreateSheet()
Dim LR, i As Long
Dim shCount As Integer
Dim Sh As Worksheet
Dim Cell As Range
Dim CpySht As PageSetup

[Code] ........

View 3 Replies View Related

Lookup Row And Column As Criteria But Data Is Spread Across Columns

May 30, 2007

I need to bring in values into one worksheet from another worksheet using row 1 and column A as criteria. I have previously done this using Sumproduct. However the complicated issue with this new worksheet is the setout. The source worksheet (see attached) has the actual data spread across columns. How can I bring in the values to the 'Summary' sheet from the '0607' sheet using the Employee number and the seg code as 2 criterias to lookup and bring in the resulting data (which is spread out in the purple area in the '0607' worksheet).

View 8 Replies View Related

Lookup Column Of Data Match Criteria From Another Sheet Display Items Vertically

Feb 5, 2014

(File is attached here)

I am trying to work on Sheet 2(Details per person). I want to be able to display all items in a row that matches the 2 criteria (Skype ID and Date) and the items are based from Master Raw file which is in another sheet. I would like to just use index and match.

View 3 Replies View Related

VBA Lookup Function: Paste Data Into A Cell In Another Worksheet, Based On Criteria Specified In Sheet1

Jan 8, 2009

I'm looking to create a macro that will take data from an input sheet, and paste it into a cell in another worksheet, based on criteria specified in sheet1.

Specifically in the attached example, the macro would copy the data in cells C8:C10 of sheet 1, then paste them into sheet 2 based on the data specified in cell B3 i.e. it would paste them into the column headed Mar-09. I intend to make this cell a drop down, so that the user can then select the next reporting month and run the macro again to paste the data into the Apr-09 column.

View 2 Replies View Related

Lookup Two Values In A Data Range

Mar 1, 2014

I have two values I need to find in a chart.

(See Attached)

I can do a Vertical Lookup but don't know how to search for the second value to identify how much to offset to retrieve the value.

View 2 Replies View Related

How To Lookup Range Of Data In One Cell

Jan 6, 2014

how to lookup some of my "range of data" in one cell.. please have a look at my sample workbook..Book.xlsx

View 7 Replies View Related

Advanced Filter For Data Not In Criteria Range

Aug 12, 2006

I m Playing around with AdvancedFilters. Using the code below I can filter for data in the CriteriaRange, but I want to filter for data not in the CriteriaRange. I can't seem to find out to do this. I'm wanting to execute this sub from a button on a userform.

Sub Filter1()
Range("Data").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("CRng"), Unique:=False
End Sub

View 8 Replies View Related

Data Validation List With Criteria Using Named Range

Oct 30, 2012

I have been tasked with creating a data validation list. My data is contained on a separate sheet from my data validation list. I am using a named range to use the data for my data validation list. However, I am looking to add a criteria to my data validation list so that it only is populated with values based on another column on my data sheet.

For example, if I have a data sheet called "Employee" and I have two columns. Column A is a list of employee names and I have named a range in column A as "EmployeeName". Column B is used to define the category of employee. This value is a letter, for example, "A", "B", "C", or "D". This column does not have a named range as I don't know if I need one to get this to work.

I would like the data validation on one sheet to use a list of employees of a particular class from another sheet. Straight forward validation is easy using a validation list with a formula =EmployeeName. But how do I only list employees that have a category "A"? For some reason, I cannot get some of the formulas I have found while searching this message board to work.

View 7 Replies View Related

Macro: Count Data In Range Based On Criteria

Nov 10, 2006

I have a list of several ranges defined in vba, and i am trying to find which range a cell containing word is in excel. Im am looking for help for a good way of doing this.
For an example: Say I have 2 ranges defined, the first range corresponds to column A, the second to column B (just an example). The word "Dog" is written in cell B15. I want to find out in vba what range "Dog" is in. I would like some vba code to check and find either if it in a given range (boolean) or a way of checking a list of ranges and returning which it is in. The word will only appear once on the spreadsheet. I have played around with the find command (A=range.find(Pl), then check if A exists) but i havent had any luck (I get an 'Object variable or With block variable not set) I tried searching for a while, but was having trouble finding something related to what i am trying to do.

View 7 Replies View Related

Lookup 2 Different Criteria Then Average Between A Date Range

Jan 6, 2009

For those of you that love difficult ones, here's one..

I have a sheet that has a column of names.. Across the top row I have dates.. let's say from the 1st through the 31st.... in the body of the report i have different sales numbers and what I am trying to is the following...

1/1 1/2 1/3 1/4 1/5
Johnny 3 4 2 2 1
Becca 3 4 1 1 1

So let's say in this instance, mind you my real report is about 342 rows of names and stats.. Is look for Johnny between 1/3 and 1/4 and average those numbers between those 2 dates...

I've tried everything from sumproduct to vlookups and can't seem to get it right...

View 9 Replies View Related

Distinct Count Of Range Using A Lookup Criteria

Feb 9, 2009

I have a problem getting an adequate formula for the following.

"Sheet 1"
Column A.............Column B
Employee Code.....Distinct Count
ZZADW...............Formula Result
ZZALM................Formula Result
ZZALS................Formula Result
ZZANG................Formula Result

"Sheet 2"
Column A............Column B
Employee Code.....Account Number
ZZADW...............1SSS
ZZADW...............1STT
ZZALM................5GHL''''''''

View 9 Replies View Related

How To Arrange Data Range Into New Table Based On Multiple Criteria

Aug 27, 2012

I have a problem whereby I have a list of data that has a date, a transaction and a balance. I want to be able to rearrange this data into multiple tables, one for each month.

The pictures below explain the situation better, on the left is the list of data I need to sort, and on the right is how I'd like the data displayed. So for the May columns I would like to display all transactions that happened in May and, depending on whether it is an income or an expense, the amount in the corresponding column.

i.e. so the finish result looks something like this:

View 9 Replies View Related

Multiple Criteria Lookup With Date Range Match?

Dec 30, 2008

I am trying to create a formula that will lookup multiple criteria within a table, one of those criteria being a date that falls within a certain date range. The purpose of the data is for billing tenants and owners in a building. I have successfully been able to use Index Match to lookup 2 criteria, one of them being a specific date match (see my attachment). However, I need to add a third criteria looks up the date that falls between a specific date range. The concept is to run a query where I enter a Unit (apartment) number into one cell, a Billing Date into another cell and the Type (Owner or Tenant) into a third cell to determine the Name of the person who fits these criteria... Criteria A Lives in Apt XX, Criteria B Who's lease start date and end date is inclusive of the Billing Date that I entered and Criteria C who matches the Type of customer, either Owner or Tenant.

View 9 Replies View Related

Multiple Criteria Lookup With Date Range Match

Oct 12, 2012

I'm trying to create a formula that will lookup multiple criteria within a table, however the problem I am having is that one of the criteria needs to fall within a certain a date range. I've used index & match to look up the dates when they match exactly, but how to return the same value for when they fall within a date.

I have attached the spreadsheet - Date Range.xlsx! The requirement is to get the correct "pay" from the "Contingent Workers" sheet to the Timesheet sheet. So it will match the Employee ID, but also the date on the Timesheet tab will fall between/or be the exact date indicated on the contingent workers tab.

View 3 Replies View Related

Multiple Criteria Lookup With Date Range Match

Dec 30, 2008

I am trying to create a formula that will lookup multiple criteria within a table, one of those criteria being a date that falls within a certain date range. The purpose of the data is for billing tenants and owners in a building. I have successfully been able to use Index Match to lookup 2 criteria, one of them being a specific date match (see my attachment). However, I need to add a third criteria looks up the date that falls between a specific date range. The concept is to run a query where I enter a Unit (apartment) number into one cell, a Billing Date into another cell and the Type (Owner or Tenant) into a third cell to determine the Name of the person who fits these criteria... Criteria A Lives in Apt XX, Criteria B Who's lease start date and end date is inclusive of the Billing Date that I entered and Criteria C who matches the Type of customer, either Owner or Tenant. I have attached my sample spreadsheet.

View 7 Replies View Related

Automatically Copy/Paste Data From Input Range Based On Criteria

Sep 4, 2006

I have a workbook with 2 - worksheets "DATA" and "LETTER".

The "DATA" sheet has columns "First Name" "Last Name" "Address"...etc.

The user can enter in either a "X" or a "x" in cells B6:B100 on the "DATA" worksheet.

After the user enters a X or x and hits the macro button I need it to copy certain cells from "DATA" worksheet to "LETTER" worksheet. I have listed a example below.

In this example if a "X" or "x" is entered into B6 on "DATA" worksheet and the macro button is hit, then: ....

View 9 Replies View Related

Lookup Or Data Change Based Upon The Data Reported In Column

Mar 7, 2008

I need to pull info to additional tabs. The problem I am having is that the data is sorted daily based upon performance numbers, so the REP names in column A can change based upon the data reported in column C.

What I want to accomplish:

Pull daily data for each unique name in column A to a team tab/worksheet that will be used to report that team's daily data.

Below is a representation of the data I need to break down by Rep Name and Team.


Rep DATA DATA DATA DATA DATA DATA DATA
1 2 3 4 5 6 7 REP 2 27.5% 14


51 86 REP 1 33.3% 14


42 74 REP 3 19.3% 11


57 86 REP 5 19.6% 9


46 66 REP 4 33.3% 9 ...........................

View 9 Replies View Related

Multiple Data In Single Cell Lookup In Another One Data

Nov 9, 2013

see sample file, i need a formula to do like B column,

I WANT MAKE LIKE B COLUMN, COMPARE A COLUMN DATA IN C,D COLUMN AND ALL DATA SHOULD BE IN B COLUMN..

View 7 Replies View Related

Data Lookup And Email Based On Data Extracted.

Mar 27, 2007

I have a excel spreadsheet that has employee names, email address and DOB. The DOB format is 23/03. I need a program that will lookup the DOB row and when it matches the value I enter it extracts the employee name and email address. Once it has this information I need it to copy into an already created email template and send its off. On some days there are more than 1 person with a birthday so it will need to send multiple emails.

View 7 Replies View Related

Lookup Range Of Cells And Populate Specific Cells Based On Matching Data?

May 23, 2014

I am trying to build a staff roster. The staff rotate over a 4 week cycle. the name of the staff member, and their shift needs to be looked up from the key then matched with the particular week. the name and shift then need to populate specific cells.

I have attached the worksheet so you can see what i am trying to achieve.

View 2 Replies View Related

Pull Data From Sheet Based On Criteria - Populate UserForm And Ask For Missing Data

Feb 8, 2014

I have a spreadsheet that is updated weekly -- but every week new info is added that needs a user to input corresponding info. I use a vlookup function to link to another spreadsheet that populates the info from previous weeks and the info that is missing shows up as #N/A...

First I was using a msgbox function to get the info:

HTML Code: 

For Each b In myrange
If Application.IsNA(b.Value) Then
Employee = b.Offset(0, -2).Value
SSID = InputBox("Please enter ID# for " & Employee & " :", "New Employee Found")
b.Value = SSID
End If
Next b

But it can be up to 30 different new employees... and that is time consuming.

I would like to make it more user friendly by creating ONE userform that displays all of the employees as labels -- has a text box in which to put the ID # -- and then has a drop down box to choose the type of employee (2 options). I want all of that info to go back to the reference spreadsheet so it will be saved for following weeks, and then redo the vlookup to get the info into the new weekly spreadsheet (I can do that part)....

HTML Code: 

Private Sub CloseButton_Click()
Unload UserForm1
End Sub

Private Sub ComboBox1_Change()

[Code] ......

View 2 Replies View Related

Lookup Function To Lookup For Data In Another Table

Jul 29, 2008

I am trying to use lookup function to lookup for data in another table (we call it table A). Unfortunately, whenever the code is not in the table A, Excel will return the data from the previous row.... is there any possible way to prevent this... in another word, if the code does not exist in the table A, I want Excel to return 0 or some other figures.

View 9 Replies View Related

Lookup Data Left Of Found Lookup Value

Nov 28, 2006

here is an example....

(this is on a sheet called Summary)

----A--------B --------C------- D
1Names----At Bats----Hits----Batting Average
2Tom-------38--------31------.816
3Derek------19--------14------.737
4Joey-------40--------28------.700
5Chris-------40--------27------.675
6Chuck------37--------24------.649

Using the LARGE function, Excel has created a list based on batting averages(on a separate sheet called Line-up). It looks like this.....

---A----------B
1Names----Averages
2 -----------.737
3 -----------.700
4 -----------.675
5 -----------.816
6 -----------.649

The problem I am having is figuring a way for Excel to also bring the corresponding names (after using the LARGE function to create the line up list).

View 3 Replies View Related







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