If Cell1 Reads Text1 Return The Value Of Adjacent Cell
Feb 25, 2014
A workbook containing 2 sheets. Sheet1 is a data entry sheet containing 8 columns and many 10s of rows of client data. The columns are (in this order) Client, Date, Code, Duration, CaseNo, Description, Staff. I also have a second sheet. Sheet2 takes the data from Sheet1 and displays the results of certain calculations, such as amt of cases a particular Staff person has and of what Code type.
Example:
- Total amt of P02 cases = 25
- # of cases Homer Simpson has = 10
So here's what I'm trying to accomplish:
Is it possible to have Excel return the total amt of time spent on a particular Code type? It would have to search through the Code column for a particular Code type (let's say "P03") and return the SUM of the Duration which resides in a different column.
Calculate by Billing Code.xlsx
View 7 Replies
ADVERTISEMENT
May 18, 2009
I have one column of about 10 names (each of which is unique). The cell adjacent to these names is empty and I want to insert a value into this cell.
The value I'd like to insert into this cell is located in another sheet attached to the same file. This sheet has the same layout, except the unique name has the value next to it and the order of the names is different.
I'd like the formula to determine where the name from the first sheet matches the name from the second sheet. I'd like it to then display the data associated with that unique name in the first sheet, adjacent to the name in the blank cell.
View 6 Replies
View Related
Sep 18, 2008
I am trying to write a function that will return the value in cell B based on its position adjacent to cell A (above). Cell A contains its own formula and may the values may change.
View 9 Replies
View Related
May 2, 2014
column 1 is vouchers
column 2 is date
vouchers column is populated with a voucher code for every date date column is populated with dates beginning with today, ending with the last day of the year.
I would like to create a function that evaluates the date file, If date = today's date, return value in the adjacent vouchers column.
View 1 Replies
View Related
Jun 10, 2008
I have a drop down list in a merged cell B12-F12 and B13-F13 and B14-F14 . . . B30-F30.
I need the adjacent merged cell to populate a reason (text) based on the text answer in the drop down list or the entered text in the first merged cell. For example in the cell B12-F12 the user picks from the list or types in "Amiodarone." I want the adjacent merged cell G12-J12to automatically fill with "Heart Rhythm." I also want to be able to set up multiple if - them statements like if Amiodarone is entered then fill adjacent cell with Heart Rhythm and if Toprol XL then fill adjacent cell with Heart / Blood pressure and if simvastatin then fill adjacent cell with Cholesterol, etc. I have about 30 different options for cell 1 that I want to have auto fill in cell 2 based on the contents of cell 1. I've attached my file.
I want the user to be able to choose from the list or type the drug name in.
View 8 Replies
View Related
Mar 22, 2008
I have a spreadsheet with a column A of dates April 08, May 08, June 08 etc. Adjacent to each of these dates is a value in column B. I want to select the appropriate value relevent to the current date and use it elsewhere. Therefore if it happens to be Oct 08 when I open the spreadsheet I want the value in column B, adjacent to Oct 08 to be represented.
View 3 Replies
View Related
Jun 5, 2008
I have a list of dates in column AF. I would like to retrieve a value from the 2nd occurrence of a date, so I used the function for the Nth Occurrence on this website as follows:
Function Nth_Occurrence(range_look As Range, find_it As String, _
occurrence As Long, offset_row As Long, offset_col As Long)
Dim lCount As Long
Dim rFound As Range
Set rFound = range_look.Cells(1, 1)
For lCount = 1 To occurrence
Set rFound = range_look. Find(find_it, rFound, xlValues, xlWhole)
Next lCount
Nth_Occurrence = rFound.Offset(offset_row, offset_col)
End Function
The excel formula I am using is as follows: =Nth_Occurrence(AF2:AF622,AE2,2,0,1)
Where AE2 is the date I am looking up. My question is this: When I put in a date ("1/12/2007") instead of AE2, i get the correct value. I tried giving AE2 and the AF column the same date formatting (3/14/2001), but this doesn't work. The date I am searching for also changes, so I cannot just type the date into the excel formula.
View 2 Replies
View Related
Aug 12, 2008
I am trying to complete these steps:
Take a value from AK59 from the "EQF" tab in a file called "QuoteMaster.xls"
Use that value to find match in column "A" in a Seperate workbook "ITMSTR.xls" on the "Sheet1" tab
Offset to the right 1 cell of the found value, take back to the "QuoteMaster.xls" and put in cell "AN59"
My code comes up with no errors, it opens the file and closes is as shown, but it doesn't copy the value over for whatever reason...
View 3 Replies
View Related
Apr 18, 2013
I have a phone list and I am trying to add a search box to it that when you type in the Employee name, it will return the value of the cell adjacent to the search result so the extension can show up.
I am a VBA Novice, but I have found plenty of code for the searching aspect of it. I am having trouble figuring out how to have the search occur and then return a different value then what was originally searched for.
Ideally I would like the msg box to show both the employee's full name and extension.
View 4 Replies
View Related
Apr 5, 2008
I have a formula that searches through a couple columns, and based on some criteria on those columns, it returns a value. The criteria looks at the dates in a given week, finds the earliest date, and then the largest value for all entries of the same date. So, I do not know what this vale is going to be in advance. In any case, Let's call this value1. This is all working fine!
Now, what I need to do is grab the value in an adjacent column to the left(same row) and add that to value1. Let's call this adjacent column value, value2. I was thinking that I could use the OFFSET function in this way, OFFSET(cell reference of value1, 0, -1) to return value2.
But this requires that I can get a cell reference for Value1. I cannot find out how to get a cell reference. I saw some VBA code to search and return cell references, but it assumed that you know what value you are looking for, and I do not know that in advance.
View 9 Replies
View Related
Apr 4, 2008
how to make the data look like a table with three columns. Other than the date, it is space delimited. I have a tracking spreadsheet where Column A is populated with dates for the year. Column C contains daily values.
I don't always start entering daily values on the first day of the year, e.g., this year the first value in Column C corresponds to March 9. All values in Column C are contiguous - there are no blank cells until the value in Column A is greater than today's date code. I would like to use a formula (rather than VBA) to look down Column C and find the first non-blank entry where the value in Column A is less than or equal to today(). In this case, the formula should return the value for March 9, 2008.
CREATE TABLES LIKE BELOW?Column A Column B Column C
March 1, 2008Saturday
March 2, 2008Sunday
March 3, 2008Monday
March 4, 2008Tuesday
March 5, 2008Wednesday ...................
View 4 Replies
View Related
Feb 4, 2014
I want to create a formula that reads cell B6 of a different sheet. The sheet name is in cell A1, and I want to get the results in B1.
Later I will change A1, and I want to get in B1 the cell B6 from the different sheet. What is the formula in B1.
View 1 Replies
View Related
Sep 17, 2009
i have a formula that reads off a single cell at the moment.
=IF(N38="MB","KL","MB") this works fine. I need the this to read off a range of cells from N38 to N43.
View 9 Replies
View Related
Mar 18, 2009
I have some VBA which I am using to search though a worksheet for data matching an input box. I would like the search to produce a messagebox with the imputbox search result, as well as the adjacent cell. does anyone know an easy way of doing this? Below is the code I have so far:
View 2 Replies
View Related
Jan 28, 2014
I have 2 tabs in a 2013 workbook. Inventory Receipts and lookups. One of the Data verification lookups I have is a drop down list in each cell in Column B (eg: Cat, Dog, Mouse) In the lookups tab I have another cell range containing the sounds (eg: Meow, Bark, Squeak).
What I am trying to acheive is, if B2 contains Cat, then return Meow in B3, if B2 contains Dog then return bark in b3 and if B2 contains Mouse return squeak in B3. Ideally I would like the formula to return the text from my lookup sheet (eg: 'Lookups'!C2,'Lookups'!C3,'Lookups'!C4). So depending on which option they choose from my animal drop down list .. the correct noise would automatically fill in.
View 9 Replies
View Related
Feb 28, 2013
how to Chk the text string in particular cell, compare it with a super set column and get the full from of the text string from another corresponsing column and the output will be corresponsing full form of the chked text string?
View 6 Replies
View Related
Dec 15, 2006
I have a list of people with SSN, about 3000. This list contains business done by each individual, which can be in all 50 states. I have another list of the same individuals with just their name and addresses and what we call "client number". I need to put the client number with the list for the individual states. See example list:
List of individuals with client numbers
123 John Doe
456 Jane Doe
List of individuals with state business
John Doe AR
John Doe CO
John Doe FL
John Doe MS
John Doe TX
Jane Doe MS
Jane Doe TX
Jane Doe AZ
I need for those client numbers to appear in the column before the names on list with states.
Example:
123 John Doe AR
123 John Doe CO
123 John Doe FL
456 Jane Doe MS
456 Jane Doe TX
View 6 Replies
View Related
Mar 14, 2008
I was wondering if you have similar values how you use formulae to return the first or the last value from a list?
I have attached an example and I was wondering if you guys could have a look at it?
View 3 Replies
View Related
Apr 2, 2008
I have two worksheets. The first one contains 500 rows:
Column A - Company Name
Column J - Formula that calculates a 'company score' based on other data in columns B-H
Column K - Number of directors
The second worksheet contains over 3000 rows:
Column A - Name
Column B - Company
Column C - Designation
I want to caculate a score for each person (from worksheet 2) that does the following:
Personal Score = ('Company Score' / No. of directors) * Designation Weighting
Where Designation Weighting (numerical value) is determined by Designation (text). There are 10 different and mutually exclusive types of text in the Designation column.
My problem is compounded by the fact that each person (in worksheet 2) can be directors on several companies. The Personal Score that I want to calculate needs to recognise which different companies they are directors of and sum the scores derived from each company.
View 9 Replies
View Related
Oct 1, 2007
Basically what I am trying to do is use a Lookup formula on a sales invoice where the product name and product price will be automatically entered into the sales form when the product ID is entered. What would the formula be?
Product Information
RegionProduct IDProduct NameProduct Price
Product IDHPPHomeware Pizza Pan19.95
Product NameSPPStone Pizza Pan 29.95
Product PriceHCDHomeware Casserole Dish19.95
Units SoldHCPHomeware Cookie Pan 9.95
Total SaleCWChina Wok 19.95
HEWHomeware Electric Wok29.95
WCMWilson Coffee Machine29.95
HBMHomeware Bread Machine49.95
HBMDHomeware Bread Machine -Deluxe89.95
HRHomeware Rotisserie119.95
OGGOlson Gas Grill 159.95
OEGOlson Electric Grill 159.95
View 2 Replies
View Related
Jul 30, 2008
Is it possible to write some VBA code that will copy IDs from Sheet1 and then find these IDs in Sheet2 and return the values these IDs have?
View 4 Replies
View Related
Feb 10, 2013
Trying to program in excel by adding contents in A to B and store in B
eg a1 = 1: b1 = 2: b1 now = 3: zero out A : column A changes every week
View 2 Replies
View Related
Aug 25, 2009
I have to make a table that shows that a if someone purchases
less than 5 items they receive no discount
5-10 items they receive 2% discount
11-20 items they receive 5% discount
21-50 items they receive 8% discount
over 50 items they receive 10%
and it has to be done in a way that the discount rate can be calculated using Vlookup I am struggling to find the best way to write this table. i tried numbering 1 to 50 and writing the corresponding discount rate in the second column but this looks untidy and can't calculate greater than 50 as i am not sure how to write it in the cell so it reads as >50 and not just 50.
View 4 Replies
View Related
Jun 21, 2007
I can solve my problem with a pivot table, and with VBA easily...however, I'm interested in knowing if this can be done with formulas (array formulas using index/match I'm assuming).
Goal: On sheet1 I have one column with products, then the column next to it will have an "Y" in it if the product is to be selected (blank if not). On sheet2 I want to create a list of the products that were selected (having the "Y"). The only thing stumping me is that I do not want spaces between the product list on sheet2...just a nice continuous list. Example:
Sheet1
cup Y
bowl Y
spoon
fork
knife Y
Sheet2
cup
bowl
knife
..not..
cup
bowl
knife
View 4 Replies
View Related
Feb 21, 2008
On Pipe worksheet, pipe numbers 1-203 are listed in column A. Each pipe number has a flow rate that is listed in column E. I need it to look up the pipe number in column A on the Job worksheet and report the flow rate (from the Pipe worksheet) in column D for each pipe.
View 5 Replies
View Related
Mar 28, 2008
I have an excel sheet with cols of information e.g IDNo, FirstName,LastName etc. I have created a form in VB where you enter the IDNo (& Click on a command button called View) It should populate designated textboxes on the Form. Thus it should check the col where the IDNo is stored, find the IDNo and populate the adjacent cols i.e FirstName, LastName in designated textboxes on the form.
View 3 Replies
View Related
Jun 11, 2008
Essentially, sheet b is a list of 900 people I need to mail to. Sheet a has 3000 rows of people, many of whom don't need the mailing. It has mailing addresses that we need in sheet b.
We have two worksheets, sheet a and sheet b
compare sheet b, column c (email addresses) to sheet a, column x, (email addresses).
If they match, move sheet a, columns d, e, f, g, and h to sheet b.
View 3 Replies
View Related
Jul 1, 2008
I have two excel sheets. The first sheet has a column with codes and another column with dates. There are more than one date for the same code. The second sheet has just one unique code. I would like to match the code on the second sheet with the code on the first sheet and return ALL the dates associated with that code to the second sheet - horizontally.
View 5 Replies
View Related
Apr 11, 2013
How to write a sub that read all cells in the matrix ( a block of cells let's say: A5:F15).in two dimensionals. This is what I've done so far, but i got nothing:
Sub myarray()
Dim myarray() As Variant
myarray = Range("A5:f15").Value
End Sub
View 2 Replies
View Related
Mar 12, 2007
I have a rowfilter on the location (so i can view who took the test by location nad whether or not they passed) - the only problem is that when it selects by location - it chooses the correct name, personellnum, location row but it then picks the test values ABOVE that row - not below it - so It is choosing the complete wrong values to go with the associated person -
View 14 Replies
View Related
