How To Return Value Adjacent To Cell From Another Sheet?
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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 ...................
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:
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.
I have a sheet that I populate with data, and I would like to create a formula (or some code) that will look down column A, and if any of the cells contain a value >1 copy the cell and the adjacent cell (column B) and paste both values into a new sheet.
I have a have excel problem Im trying to figure out. I have 2 sheets. Sheet1 and Sheet2. I want to copy into sheet1 cell B2 the contents of sheet2 column C row x iff sheet2 column B row x = 1. There will be only one cell in that column equal to 1, so I will be copying that cells(colB; rowx) adjacent cell (colC;rowx) contents. This is what I have so far but it doesnt work. =IF('Sheet2'!B:B=1,DONTKNOW WHAT TO PUT,""))
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?
I have 2 workbooks in Excel 2010, each contain just 1 sheet. (see attached) I need to compare on sheet 1, cell D1 and column A:A (this column will be much longer), with the data in columns C:C & A:A on sheet 2, if a corresponding match is found, the data contained in column D on the same row on sheet 2 is written to the cell with the matching data in sheet 1.
If I have a cell that has as its contents as sheet name, is there anyway I can use the cell's address to reference that sheet?
As an example, say I have in Sheet1, cell A1, the text Sheet2. And let's say I want to return the value of cell B2 on whichever sheet the text of A1 says. So, on Sheet1, I might have this:
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
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.
I have a workbook that uses the following for a list of names (in a specific cell) over multiple sheets:
Burns, Charles; Simpson, Bart; Smithers, Waylon; Wiggum, Ralph
Some names appear on different sheets. I would like to create a TOC sheet that lists what sheet each name shows up on. I can pull the names and have a list of all of them, but how to
a.) determine all of the sheets where each name shows up and, subsequently, b.) how to programmatically create a hyperlink to whatever sheet(s) each name shows up on.
I've attached a dummy workbook. The column in question is Content Owners. There would be a link to each separate sheet in different cells adjacent to the name.
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
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:
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.
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.
This function =INDIRECT(""&A17&"!"&$A$8) works only once or twice. Then I rewrite it as =INDIRECT(""&A17&"!"&A8), without the dollar sign, $, at A8. But then that only works once or twice.
This is how it works: In Grid A17, type name of worksheet in which to perform the data search. Then, the function goes to that worksheet and selects the data in Grid A8.
