Search Formula - Lookup Person Name In Given Worksheet In Specified Cell?
Jan 15, 2014How to create a search formula that will look up a person's name in a given worksheet in a specified cell?
View 8 Replies
ADVERTISEMENT
Counts The Number Of Hours That This Person Works & Lookup Function
Jan 25, 2008I'm trying to create a work Rota and I'm having a bit of a problem with a certain section. In Worksheet 1 I have the following headings:
Cell A - Name
Cell B - Monday
Cell C - Tuesday
Cell D - Wednesday
etc
Under these headings is each member of staff and the hours they work, IE L (Late), E (Early), SD (Short Day) etc. In the final Column, it counts the number of hours that this person works (Early is 7.5). In Rows 46, there is a section here to work out how many people are working earlys, which is where I have the problem.
The hours are worked out by doing a lookup function on the cell that says E, L etc and goes to a CODES sheet and pulls the value of that letter. What I am wanting to do is lookup that Letter, which and look in the cell next to it and count how many people would be on an early etc.
IF Or LOOKUP: Search For Values From One Worksheet And Identify Whether Or Not Those Values Exist In Another Worksheet
Mar 23, 2009I tried both IF and LOOKUP and failed. I'm trying to search for values from one worksheet and identify whether or not those values exist in another worksheet. I attempted the following lookup in field A2:
=LOOKUP(B2,Sheet3!A$2:A$914,Sheet3!C$2:C$914)
B2 (thru B5000 or so) contains values I want to search for; sheet3!A$2:A914 is where I want to look and column C of that same sheet, entered the text "Yes" in an attempt to have the results list "Yes" for hits and N/A for misses. (All fields are text.) I copied the formula all the way down the sheet in column A. The result it is returning is N/A in A2 and Yes in A2 -to the bottom, which is incorrect.
Lookup With Multiple Criteria: Return The Amount Paid And Full Cost Based On The Person's Name And The Date
Apr 21, 2006I would like a lookup that takes multiple criteria and that is not an array formula! Unfortunately I decided to use array formulae and my spreadsheet went to over 45mb!! Not good. I've searched the forum for an answer to my questions but couldn't find any! I've attached a spreadsheet as an example. The examples I am using have {Sum(IF)} formulae in it (array) and I would like to change those to others that will not increase the file size so much and will not take too long to calculate.
Basically, I would like a lookup that will return me the Amount Paid and Full Cost based on the person's name and the date. the data and the results table are both on separate sheets. It would be nice to bring that file's size back down to less than 4mb!!
Pull Details Of Each Person From Another Worksheet
Sep 21, 2007I have worksheet 1 which calculates a member's pension which is based on his date of joining a scheme, date of leaving, salary and yearly contributions paid into the scheme. Normally I would input these details manually but now I have a large number of members to produce figs for. These members' details will be on worksheet 2 with a separate row for each member's details. I want excel to process each member one by one and enter his details in worksheet 2 on the calculation worksheet 1 and put the results in worksheet 3. Is this possible? And how???
View 8 Replies View RelatedAdd Lookup Value In Array Formula To Each Worksheet With VBA
Feb 19, 2008I am looking to write a macro that can look at a value (i.e. "X") in a cell and compare it to the elements in a 2D array (where one dimension corresponds to the start value and the other to the end value) that is found on another worksheet. If "X" falls between the starting and ending numbers then I would like the macro to return a string (say "Test1") to a cell on the same row and a couple of columns to the left. The string values are listed to the left of the start and end values on the first worksheet and are elements to a 1D array. I am trying to make this " loop" through all the cells that contain run numbers (I have highlighted the columns in yellow on the attached workbook). There are multiple (i.e. +12) worksheets in this project but I have only included two in the sample I am providing because of file size.
View 4 Replies View RelatedCreate A Formula To Pull The Person's Name And The Avg Number Of Cartons
Dec 5, 2007I am trying to create a formula to pull the person's name and the avg number of cartons sorted for the month from this spreadsheet.
I've attached a sample of the spreadsheet I created, you will see at the bottom I would like to see the person that had the highest avg for the month and the person with the lowest avg for the month.
This is just a simple spreadsheet to track productivity of my sortation employees.
Lookup Nth Occurence In Range & Limit Rows To Search Based On Cell Value
Jan 14, 2009I have a UDF for a lookup_occurrence formula (thanks to Dave), and I was wondering if it was possible to tweak the range it looks at with if statements.
I have a formula like this =Lookup_Occurence(B13, INDIRECT($N$5), 1,1,5) where N5 says exit card 1.
I need it to only look at a certain rows within exit card 1 depending on the value entered in I5.
If value I5 says Period_1 it will only look in rows 12:46.
If value I5 says Period_2 it will only look in rows 48:82 etc.
I believe I need to modify the xl look part of the code with if statements.
Below is the code. I am wondering if I can replace the xlLook line with nested if statements. If I5="Period_1 then rows 12:46" etc.
Function Lookup_Occurence(To_find, Table_array As Range, _
Look_in_col As Long, Offset_col, Occurrence As Long, _
Optional Case_sensitive As Boolean, Optional Part_cell_match As Boolean)
Dim lLoop As Long
Dim rFound As Range
Dim xlLook As XlLookAt
Dim lOcCheck As Long
Lookup Type Formula For Column AB In The (worksheet/tab) Titled 2009
Jan 8, 2009I have a lookup tab which contains three columns: Column A is a tax ID #, Column B is a set of numbers and Column C is a list of street names. I need help with creating a lookup type formula for my column AB in the (worksheet/tab) titled 2009, which will look at and compare the data in columns F & G and compare them to columns B & C in my "lookup" tab and if a similar address is found based on both the street name and number then the tax ID # affiliated with that address from the "lookup tab" will be automatically placed in the column AB in the 2009 tab. I hope this makes sense.
View 4 Replies View RelatedSearch Value Of A Cell On Another Worksheet Then Return A Value?
Jul 31, 2013I need a formula that will search for the value of C1, Sheet1 in Sheet2 then if found, return a value from that row in column D? If not found then return a message "search emails".
I can upload a dummy if that's possible?
Search Coloured Cell In Worksheet And List The Cell Location
Jan 10, 2013I have a worksheet size roughly 100 * 400 cell. there is roughly 100 cells filled with RED color and with different value.
When i used FIND ALL format - RED, I can locate the cell location and the cell value. And I wish to copy these 2 info onto a new sheet so that on the new sheet, I can create a hyperlink and easily access to the location of the RED cell.
However, in FIND and REPLACE, i have a list of results on what I want. But i cannot copied the information out. So is there anyway to copy it out or to achieve what i want?
For eg.
RED value
cell
50
A3
87
D71
251
AQ5
and so on.
How Do You Lookup The Correct Cell With 3 Of The Same Name In 1 Worksheet?
Oct 1, 2009I need a formula that will look up a cell to get a figure from, but there is three of the same name (sometimes more, depending on different products sold) i.e. "Dept Total" (shown below & attached for easier reading) ....
View 7 Replies View RelatedUsing Cell Text To Lookup A Certain Worksheet
Oct 13, 2008I want to be able to look up a cell and do an index lookup on the worksheet with that cells text. Eg.
=index('B2'!A2:E500,match(1,('B2'!A2:A500=E2)*('B2'!C2:C500=D2),0),5).
Where 'B2'! is the worksheet I want to lookup. If cell B2 contained BAZ, the index would lookup worksheet BAZ. If B2 contained TAZ, the index would lookup worksheet TAZ.
Search/Find Multiple Cell Values On Another Worksheet
May 20, 2008I have three parameters that users on my spreadsheet will be using. They will be searching using the 'Brand', 'MPAN' and 'Tariff' fields.I would like to have drop down menu's for then to select the correct search option. I then have another sheet within the same workbook with all the data in it with columns headed of Brand', 'MPAN' and 'Tariff'. It has to be an excat match using those three parameters though. I would like the parameters the user selects on the first sheet to be searched and found on the second sheet, with the figures in the cells next to the search result getting fed back into a set cells back on the first page.
View 9 Replies View RelatedAdd Cell Content Into Formula Command In Another Cell For Multiple Files Search
Apr 12, 2014How to determine the correct formula for this requirement?
Assuming I have 2 individual excel files and an index excel file (in reality, there are more than hundreds of file). For index excel, once the user enter part number (eg. 1 or 2), the excel will look for the part number excel file and determine vlookup function.
The only problem I have here is I can not make the index file automatically add the part number shown on most left column into the required formula (replace the part number file section).
I tried use the indirect function but this require each file to be opened, which is not possible for actual use. I am looking for a function that can work in closed worksheet.
Attached files :
index.xlsx
1.xlsx
2.xlsx
Search For & Merge List Of Text Files Named In Worksheet And Paste In A Specifc Cell
Aug 20, 2006I did a search for " import text" and found some promising leads, but not exactly what I was looking for. I have tried running macros and looking at the code but don't know how to pass the file names from the the D47:D147 range to VBA(see below). I saw elsewhere that Excel can be told to create a temporary batch file, and that the batch file with the command "copy text1.txt+text2.txt+text3.txt all.txt" for example could be used to merge the 3 text files into a file called all.txt. I don't however know how specify the path where copy starts, to tell the batch file to look in subdirectories or to pass the file into Excel. I've posted this question yesterday to Yahoo Answers http://tinyurl.com/omers and http://tinyurl.com/rfww9 without much luck.
Below is the macro I would like:I have text files whose names are found in the range D47:D147 although without the appended ".txt" extension. The sheets can only contain one name sometimes, but on average 8 to 10, so in the average case only cells D47:D56 would have entries.The text files are found in the say H:Textfiles directory or subdirectories.I would like Excel to find these files, concatenate them with a row between each file, and paste the results into cell K251.Finally, the text import wizard should be used with a space as a delimiter and the last 3 columns (it's sometimes only 2) of the concatenated file, not imported.
VBA Lookup Function: Paste Data Into A Cell In Another Worksheet, Based On Criteria Specified In Sheet1
Jan 8, 2009I'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.
VLookup Formula That Will Search For Value One Cell To The Right
Sep 29, 2011I get new worksheets on a regular basis which are of the same format (same columns with headers) and I have to perform both vlookups and sumifs on them. Their are about 12 different tabs I perform the vlookup on based a vendor name in A2 and its corresponding tab. I am tired of typing the same thing over and over again.
What I'd like to write is a Control Panel with all the equations so I can just copy/paste them in when I get a new sheet.
How can I write a vlookup which would search for the value which is one cell to the right of where I put the formula in?
Similar to:
=vlookup(current cell over one, 'vendor1sheet'!A:C,3,0).
I would then want to copy it down the worksheet. Currently type in column E everytime I get a new sheet:
=VLOOKUP(F2,'Vendor1'!A:C,3,0)
The catch
Column A has the vendor name, there are about 12 vendors, each with a different tab with related data which I am searching through.
Ultimately, I'd like to have an equation that would do the vlookup (and sumif) as described above and also use the vendor name in column A to look on a particular tab.
Something like:
=vlookup(one cell to the right,'VendorTabFromVendorNameInColumnA'!A:C,3,0)
I was going to use nested if statements on a control panel sheet with a column for the vendor name and a column for the vlookupformula and than paste a single formula all the way down column E:
In column E, every row:
If(A2=vendor1, 'controlpanel'!vendor1equation), if(A2=vendor2, 'controlpanel'!vendor2equation), etc.
Again, the vlookups would all check for one cell to the right of where this nested if statement was (located in column E, looking in F).
Search And Replace Formula For A Cell
Feb 17, 2008I am looking for a formula for a cell that will do the following:
from :
robert johnson hot dog
To this
robert+johnson+hot+dog
I would like it only to change the space between the words to a + sign.
Auto Fill Formula Every Nth Cell And Lookup Value Every Nth Cell In Source?
Dec 19, 2013Source tab contains vital information about some clients.
In the aggregated tab (Cell C10) I created a formula that pulls the Inflows from the source in a very specific array. So for client 1, this works fine. Now, if i copy my formula to the client 2 (Cell C14), it obviously wont go and look in the correct array in my source.
What i need to do is to be able to copy/paste my formula
[Code].....
(from cells C10 to CC10) to cells C14 to CC14, but when copied, the look up array changes to:
Formula: [Code] ....
I will have to fill this formula to at least 100 entries down, so i need to make it work with ease
The good thing is that all look up values in the source increase by a fixed number of rows (12). I tried playing with index/rows formula.. no luck..
Attached File : samplev1.xlsx
Formula: Go To Lookup Cell
Dec 15, 2006I'm creating a customer manager spreadsheet. I have my data set up and hidden above the data area, so I can get autocomplete to work. What I want to do is allow my sales reps to pull up the customer information by typing the customer name, etc, which I can do with Vlookup.
I then want them to be able to type in notes into a string which is attached to the customer's row of information. For instance, Customer ABC is on Row 2, their name is A2, address is B2, and notes are C2. However, Vlookup won't allow me to change the cell it finds, only display its contents. I'm sure I'll need a submit button, and possibly some VBA, but I'm not very good with it, and I can't find the answer anywhere else. Let me know if you need any more information. I'm sure it's simple, I just can't find how to do it.
Formula To Search For One Word In Adjacent Cell?
Jul 8, 2014I've got a long list of company names, i'm trying to create a formula to see if they match but the problem is the names won't match exactly (e.g it would be Joe Bloggs Limited & Joe blogg's ltd). I've thought about it and think the best way to see if they match would be to take the first word from the first cell (in this case Joe) and then search for it in the next cell and return TRUE if it exists anywhere in the cell and FALSE if not.
View 3 Replies View RelatedFormula To Work Where It Will Search A Single Cell
Feb 27, 2009I am trying to get a formula to work where it will search a single cell (on a sheet called "Calls") to see if a particular code is in there, if so return a value using HLOOKUP from a table on a sheet called "Values". The problem I am having is how to use the wild card etc because everything I have tried returns an error...
The code I am trying (Without wild cards, because they all failed) is...
Code: ....
Search Worksheet For Data In Multiple Textboxes On Userform - Display In Temp Worksheet
Dec 23, 2013I have a workbook with 4 worksheet that store different type of data. It also has a userform that load at start of the application which is to search the data in the workbook. The userform has a combobox where the names of the sheets are stored. when the user selects say Sheet2 in the combobox, it enables the relevant textboxes on the userform and activates the worksheet at the change event. The userform has a search button that searches all the worksheets based on the text entered in a textbox.
The problem: how to search based on 1 textbox. What I want is: say for e.g the end-user selects sheet2 from the combobox, this intern enables 4 textboxes (Name, DOB, Nationality, ID #) on the userform. The end-user should have the liberty to enter data in 1 and/or any of the textboxes. The search should be performed, that if data is only in 1 of any of textboxes then give all rows that fit that criteria and display in a temp worksheet. if say the name and dob is filled by the user than what matches both should be displayed in a temp worksheet. if say dob, name and ID# given so the search button should narrow down to fit all 3 criteria and then display result in temp worksheet. As if mentioned data can be entered in either just 1 or any or all textboxes.
E.g. the worksheet is (Columns are Name, Nationality, DOB, ID#)
row 1 = name: Steven Martin, DOB: 27-may-1993, Nationality: Trinidad & Tobago, ID #: 1234567
row 2 = name: Gary Richards, DOB: 2-FEB-1993, Nationality: British, ID #: 456789
row 3 = name: David Cohen, DOB: 27-May 1993, Nationality: American, ID #: 98765
row 4 = name: Roberto McDonalds, DOB 21-Jul-1962, Nationality: British, ID # 654321
row 5= name: Gary Richards, DOB: 01-Dec-1978, Nationality: Australian, ID # 1234567
Now if the user enters only name as "Gary Richards" and search then row 2 and 5 should be displayed in a temp worksheet. if user enters name Roberto McDonald and ID# 1234567 then it should not display anything. if user enters DOB 27-may-1993 and nationality British and ID # 1234567 then as well shouldn't display anything and should a msgbox "no data found".
Search Keyword In Worksheet / Copy And Paste Adjacent Cells To New Worksheet
Sep 24, 2012I've been tackling this data capture/paste issue for a week or so. I found the string below which does provide a good foundation for my challenge. But, my basic level of understanding macros limits my modifications to meet my needs.
[URL] ......
I have 20 worksheets in my master file corresponding to Excel files individual associates will update weekly. After the associates have updated their individual files for the week, I want to capture the data entered and paste values into a master file containing a worksheet for each associate (sharing the same name as the individual associate file). All of these files are housed on team SharePoint sites.
I need a macro to perform several steps after clicking a "Run Update" macro button in the master file:
Open individual associate fileIn master file, search for each Initiative listed in column B (starting cell B3) in the individual associate file (in column B starting at cell B11)If Initiative is found in individual associate file, copy adjacent data in columns D:J for the respective rowIn master file, paste values to the corresponding Initiative row for the corresponding week's worth of dataIf Initiative is not found in the individual associate file, move to the next Initiative listed in the master fileRepeat these steps for each individual associate file
Linking would be the easiest way to accomplish this if I wanted to have a multitude of weekly individual files for the associates. However, I'd rather each associate have one file for them to update (basically overwriting their previous week's entries).
I need to ensure the paste values corresponds to the appropriate day of the week. In simpler terms, if the date in the individual associate file in cell D9 reads Oct 1, 2012, the data captured from that row needs to be pasted to the corresponding row/column in the master file that reads the same date.
Search For Cell That Has A Formula And Color Cell
Feb 26, 2009I have this worksheet, that was created by some one else, that has formulas all over the place. What I want to do is click a button and have all the cells that contain a formula to change the font color to red and fill the cell with a certain color.
I am going to create a userform with one command button. I know the code will go in the command button.
Formula To Search A Cell For Specific Data To Extract
Mar 13, 2009I was wondering if there is a formula that will search a cell for a word or other specific criteria then if it finds it, puts the requested data / word in the formula cell
Example:
If I have a list of vehicle descriptions all in different formats:
CAR1 1.4 SRI 3 door Hatch
CAR2 5 door saloon GSI 2.0 V8
2.2 CDX 5 door CAR3 Estate
CAR3 Estate 5 Door CDX
Say I want to know which ones are CDX varient I need the formula to look in the cell and return "CDX" or "YES"
Formula / Macro To Search Cell Text And Assign Value?
Jan 30, 2014I am trying to get a code which searches each row for a keyword, and then assigns a value to it depending on what group of keywords it is.
For example, if the Name or Comment mentions certain fruit keywords, I want it to assign the value: "Fruit", and if it mentions certain vegetable keywords, I want to assign it "Vegetables", and if it is unrelated to either, I want to keep it blank. If the name/comment mentions both fruit and vegetables then I want it to say "Error".
Since the keywords I am using are mostly two letters, I want the search to only search for the two letters on its own, and not as part of a sentence. e.g. if the key word is ED, I don't want it to return a value for "I rested". Instead, I want it to stay blank.
i.e. I want the code to take information from column A and B, and assign a value based on the keywords in column D and E. As an example:
A B C
Name Comment Value
V1 Eating vegetables are good for you Vegetable
null lock out of ep. Fruit
S-1 It is JUST
Fruit-sp Error
D E
Fruit Vegetable
EP SW
KG SP
ST CM
BV LP
The formula I used so far is this: where C3 is the first value
C3=IF(OR(ISNUMBER(LOOKUP(100,SEARCH(Sheet1!$D$2:$D$6,A3))),ISNUMBER(LOOKUP(100,SEARCH(Sheet1!$D$2:$D$6,B3)))),"Fruit","")&IF(OR(ISNUMBER(LOOKUP(100,SEARCH(Sheet1!$E$2:$E$6,A3))),ISNUMBER(LOOKUP(100,SEARCH($E$2:$E$6,B3)))),"Vegetable","")
In columns D and E I put a leading and trailing space between each word to only search for the keywords themselves, and not as part of a word. (e.g. ST is correct instead of haSTe).
This formula seems to work only for 60% of the information, problems occur when the keyword is at the end of comment sentence: the value is blank. e.g. in row B4 of the table I made above my formula returns a blank.
Modifying A Sumproduct Formula To Search For Words In A Cell
Jul 7, 2009My current formula is this: =SUMPRODUCT((Str311A=1)*(Str311C=15)*(Str311G<>" DM "),Str311L). All the items starting with Str311 are formula's associate with a name. What I'm trying to do is add one more factor into the formula. I need it to look in column I for any cell that contains the words L/S. I tried modifying the formula to this but it does not work:
SUMPRODUCT((Str311A=1)*(Str311C=15)*(Str311I="*L/S*")*(Str311G<>" DM "),Str311L)
The formula contained inside the names are:
Str311=OFFSET(Outlet!$A$1,MATCH("STORE # 163311",Outlet!$A:$A,0)-1,0,MATCH("TOTAL FOR STORE : 163311 SAN MARCOS",Outlet!$A:$A,0)-MATCH("STORE # 163311",Outlet!$A:$A,0)+1,1)
Str311A=OFFSET(Str311,0,0)
Str311C=OFFSET(Str311,0,2)
Str311I=OFFSET(Str311,0,8)
Str311G=OFFSET(Str311,0,6)
What I need is a way to modify my original formula to now also only match cells that contain the phrase L/S. The phrase can be located at the beggining, middle, or end of the cell characters.
=LOOKUP(2,1/SEARCH
Jul 17, 2008=LOOKUP(2,1/SEARCH(0,G13:G1052),B13:B1052)
this will tell me the last 0,how do I find the first 0?