# Find Value & Return Contents Of Offset Cell

Sep 3, 2006

On Sheet2 I have a table where I want M2 to do the following:

1.) Look at cell A2 where the name "John" is and B2 where the name "smith" is.

2.) Look at sheet1 and look in B2 and C2 for "John" and "smith"

3.) Once it matches the name, place the contents of sheet1, cell I2 into sheet2, cell M2.

So in short, match the name on sheet2 to the name on sheet1 and return the number in I2 to M2 on sheet2.

Maybe combining the persons name in to one column would make it easier?

## Index Match Offset - Find Contents Of Reference Cell

Jun 21, 2012

How can I modify the following formula to find the contents of reference cell, for example if the cell reference (G\$3) is equal to 9 I want it to use 8 instead.

Code:
IF(ISERROR(INDEX(tra!\$E\$2:\$E\$1100,MATCH(G\$3,tra!\$C\$2:\$C\$1100,0))),"",
INDEX(tra!\$E\$2:\$E\$1100,MATCH(G\$3,tra!\$C\$2:\$C\$1100,0)))

I tried using G\$3-1 but this doesn't appear to work.

## Find Value & Return Offset Cell Content

Nov 13, 2006

I would like to be able to find all cells in row 2 that are "greater than 0". Then I would like to return the coresponding job number in row 4. I need to create a list of each job with the number of hous spent on that job next to it. I may have 30 different jobs but only 5 jobs that have hours for the specific week.

How I attemted to solve this is to create a table that listed all the hours from the largest number in row 2 to the smallest. Then I found the job number corresponding to that number of hours. The problem with this meathod is that if two jobs have the same amount of hours then the first job number is returned for both values that are the same. See attached example.

## Find Value, Offset & Return Offset To TextBox

Nov 1, 2006

- Find a value on a sheet “ORM” in Column G5:G33

-Get the corresponding value of B5:B33 of that cell

-Place that corresponding value in a canned remark: “You have the number 2 in Block, 12, 14, 25 22.

-Place this canned remark in TextBox31

## Using OFFSET Function To Return The Value From Another Cell With Row And Column Offset

May 31, 2014

I am having some difficulties using a combination of IF and the OFFSET function to display a range of cell values from another column based on a simple condition. The values I need to display at the destination cells should be offset by 8 columns to the right and "X" rows down from the reference column. The value "X" is to be determined via the IF function to check for the row index number.

For example, if Index value "X" = 8, then display the value of B2 in cell I9. IF X = "9", display B2 in cell I10 etc.

I have attached a sample worksheet that provides some examples.

## Find Match In Column & Offset For Return Value

Sep 2, 2007

i tried using the lookup but it gave a different result. i want to search a value from sheet1 A to sheet2 A and copy the remarks from sheet2 B and paste it to sheet 1 B and if not found leave it blank.

here's the attach file i just trim it down.

## Find Yesterdays Date In Range And Return Row Number To Be Used In Offset

Jul 13, 2014

I'm writing a macro that will import data from one workbook to another, based on yesterdays date. I have the import functionality working, and I've been playing all afternoon, but I can't find a way to find yesterdays date in a range and use that row number as the row offset value in this line:

Rather than having a set row offset value (in this case, 14) I would like to use the find function to lookup yesterdays date in range "B50:B80" in the worksheet I've designated as "sh1" and return the row number of the cell that has yesterdays date and use this value as the row offset value, replacing the hard coded 14 that is in there currently. The date values in the range are formatted as per Date Format.jpg.

## Excel 2003 :: Offset Reference To Be Contents Of Cell

Mar 21, 2014

I have done a Scenario and they list the cell address (example R2C35) in the Report - the cell has the text "R2C35" in it.

I want to get the contents of a cell 2 columns to the left of Cell R2C35.

So what would I use for the Reference Cell in the Offset formula?

## Return Contents Of 1 Cell Based On Contents Of Another Cell?

Mar 12, 2014

I thought this was a pretty simple formula but I am having difficulty creating it. I am attaching a little test spreadsheet. Sheet 1 is where the data will be entered. The Reimbursed column has a drop down choice of yes or no. The next 2 columns are the cost of registration and the cost of accommodations. On sheet 2 is where I would like the formulas. So in cell A4 I would like a formula that says if B3 on sheet 1 is Yes populate this cell with the contents of Cell C3 only, B4 of sheet 2 would then be B3 if A3 on sheet 3 is Yes and so on with the Not reinbursed if sheet 1 the Reimbursed column is no.

## Lookup & Return Offset Data By Cell Value

Jan 30, 2008

The main worksheet in my workbook contains lists of suppliers with unique Vendor Numbers, of which there are approximately 4,500 rows. There is another worksheet which holds approximately 6,500 entries I need to bring data from that sheet to the main sheet using the Vendor Number and the information that is held on 4 columns unique to that number.

The information is a classification system that comprises of 4 columns holding a mixture of numbers and text.

Worksheet 1 has the Vendor Number in Column A and the Classification destination starting in Columns Q, R, S & T.

Worksheet 2 once again has the Vender Number in Column A, and the Classification information in Columns C, D, E & F.

What I need guidance on is how to build the formula to copy the information across, or direction into what other method I should be looking at instead.

## Find 1st Cell With Numeral, Then Offset.

Apr 21, 2008

How would I find the first cell in column A containing a number, like HY42128PP, and then offset 1 row up from there to start my autofilter?

Also, to copy the worksheet headings, what code would copy all rows from row 1, down to 2 rows above the first cell in column A containing numbers, and insert those rows in another sheet?

## How To Return Cell Contents If It Contains Partial Text

Jan 2, 2014

Below is a small sample of Column A from a spreadsheet I'm working with. All cells in the column begin with a 6 digit number:

115383_BOSTON
115384_NEW_YORK
115385_PORTLAND
115402_LOS_ANGELES
115403_WACO
115010_SAINT_PAUL
115011_SAN_DIEGO
115130_WEST_NYACK
115021_CHAPEL_HILL

What formula would allow me to enter only a 6 digit number and return the entire cell contents, i.e, search for 115403 and get a return of 115403_WACO ? I have tried INDEX and MATCH, but I'm not getting the results I need.

## Return Pictures Based On Cell Contents?

Jul 25, 2014

I have inlcuded my workbook that I use for tracking the NFL season and I'd like to add in some team logos just to make it look a little cooler.

The attached workbook includes three sheets, the first of which uses a drop-down for selecting picks. The second sheet has the team logos and the third sheet feeds off the first.

I'd like for team logos to populate into the third sheet based on what is selected in the first sheet.

I've already tried a few methods, all unsuccessfully. I tried this [URL] which was a great idea but it didn't allow for the same image to be repeated potentially 100 times. I also tried a method involving named ranges and indirect lookups to the linked picture image but that would require a different named range for every person and every pick which isn't feasible.

if it's possible to just do a massive nested if statement with a vlookup to return an image but I haven't been successful in that either.

## Return Value Based In Contents Of Another Cell With A Twist?

Jan 7, 2012

I would like cell CM2 to look at cell G2 and get the value there. Here is where it gets interesting. I would like it to reference sheet "LookupTables" in column A.

Cell G2 will be a 4 digit number.

Cells in column A of sheet "LookupTables" will be formatted like this: 3 digit number-4 digit number-4 digit number, like this "001-0001-0001".

If cell G2 exists in column A of the LookupTables sheet, (but it needs to ignore the first 4 digits and the last 5 digits of that number) then it should return that value in cell CM2. I would like it to look through the entire column A of the LookupTables sheet and return all numbers that match the above criteria, and separate them with a comma.

So, if G2 is 0001 and column A of the LookupTables sheet contains several values including:

001-0001-0006
004-0001-0187
098-0001-0874
098-0002-4356 (it should ignore this since the middle numbers don't match the value of G2)

then CM2 should display: 001-0001-0006,004-0001-0187,089-0001-0874

Also, if there are no matches, then it should just return a blank cell.

## How To Return Contents Of Last Non-blank Cell In Column

May 7, 2014

I am trying to figure out how to return the contents of last non blank cell in column B, based on the name in column A. So, if I choose "Sam", the result I am looking for is "blue". If I choose "Pete", the result I am looking for is "orange".

Sam
red
Sam
blue
Sam
Pete
orange
Sam

## Sumif: Return The Contents Of The Respective Cell

Nov 5, 2009

In Column A, in each cell in rows 1-10, there is various text that may or may not include "ABC" and "DEFG" as the first four letters. In Column B, rows 1-10, there is numerical data. What I would like to do in Column C, rows 1-10, is place a formula that would return the contents of the respective cell in Column B, but only where the first three or four letters in the respective cell in column A is "ABC" or "DEFG".

## Find Last Cell In Range, And Changing Another Cell Based On Offset Text

May 4, 2009

If i have a range say E12:O12 and want to find the last cell before blank, lets say it finds m12 as the cell with the value before blank. then with m12 it needs to determine weather row 11 in the same column has Text either "S" or "F" if "S" then m12 = t if "F" then m12 offset(1,-1) = t. And just to make things more difficult i need the range E12:O12 to step 2 as well until it gets to E208:O208 .Noting that row 11 never changes and will always have either an "S" Or an "F"

and also t = time()

## Offset: Find The Last Value Cell In A Row And Display Its Contense

Jan 21, 2010

I need to find the last value cell in a row and display its contense. Is OFFSET the best way of doing it? Cell H3 will need to contain the formula and the row to seach is also row H. In H3 I want the value that is found in the right most cell on row H that contains anything.

## Find & Use Cell Address To Offset On Another Worksheet

Jul 27, 2007

I'm still getting to grips with the Find Command. Basically, I have the below code that looks at a line in a sheet(ws_Site) based on the Line ID Number that is in column A and then Finds that that LineID_Value in a different sheet(ws_main) and copies a value from column I on that line across. Problem is, if the cell I is blank it copies it across and essentially clears the cell if there was data in it.

How can I adjust the below code to only set ws_main.Range(c.Address).Offset(0, 8).Value = LineID_Value if LineID_Value is not blank?

For Each LineID In ws_Site.Range("A7:A" & SiteRows) ' Loop through all the cells in range

LineID_Value = LineID.Offset(0, 8).Value

'Dim c
With ws_main
Set c = .Range("A7:A3000").Find(LineID, LookIn:=xlValues)
'If Not c Is Nothing Then

'End If
End With

Next LineID

## Find All Instances Of Cell In Column & Check Offset Cell

May 21, 2008

how to explain this except by showing it, so I explained it as best I could in the attachment. Anyone think they have a solution for this? Let me know if you need more information. Thanks!

the macro will look at the value I entered in cell A1, then find all instances of it in Column A
in every row that A1 matches, if the value in column E is "0", I need the macro to change the value in column E of that row from "0" to (that row's column B * \$B\$1)

## Search For Text Within Cell Then Return Contents Of That Cell?

Jun 19, 2014

So right now I have a spreadsheet that looks something like this:

A
B
C
D

[Code]....

As you can see, the stock names are slightly different in columns A & C (CORP. vs CORP, CO vs CO., etc). I need a formula in column D that searches column A for the first word in column C, and then retrieves the contents of that cell. I want to do this because I will then use a vlookup in column E to get the ticker for the stock.

Right now I have: =IF(ISERROR(SEARCH(LEFT(C1,FIND(" ",C1)),A:A,1)),A:A,"")

This formula searches column A for EXXON, but does not return the contents of the cell. Instead, it returns the contents of a different cell in the column.

## Loop To Find A Cell, Then Copy Offset Paste

Feb 1, 2009

I have a data sheet with employee information. Only one column. It prefixes information with codes, but keeps it in the same cell. I want to move data of certain types into their own columns, but the amount of data is variable, so I cannot simply move every Nth cell, etc.

i.e.

Employee#1
200 Firstname Lastname
204 99999999 (Employee ID)
G38 00005000 (i.e. Pension Deduction)
H38 00007580 (i.e. Benefits Deduction)
X96 00012099 (i.e. Staff Club Deduction)
Employee#2
200 Firstname Lastname
204 99999998
G38 00000775
X96 00001000

So you see some employees may have different codes altogether. But I know that I want all the cells that start with 200 to be offset (-1,1), and all the cells that start with 204 to be offset (-1,2), and so on so that basically I end up with columns of info instead of a one column list.

I have been reading and studying other peoples' macros, and am just starting to grasp the basic. When I wrote my own to accomplish this, I put this together, which doesn't work. But I don't know enough to know what I don't know.

## Offset With Automatically Find Active Cell With VLookup

Jul 30, 2012

I have 2 columns of data

column A = weeks (A2:A50) i.e. P1W1, P1W2, P1W3....P12W4 etc
column B = headcount (B2:B50) i.e. 5, 7, 5...10 etc

Essentially my criteria is "looking for last week and give me headcount" i.e. my lookup criteria is P7W5 ....however if P7W5 has no data, i want the lookup to go up or offset to the row above (it may be one to 4 rows above)....

## Find Date & Pass Offset Cell To Variable

Sep 20, 2007

How do I use VBA to send the value of a cell, for example, cell J77 to the variable "mastervalue1"?

I have a spreadsheet that has a column with dates ( in the format "mmm-yy") and next to it a column that has values. I want to pass the value of the cell that is next to the date cell that contains today's month to a variable in VBA. Auto Merged Post;here's an example of my spreadsheet

## VBA - Find Cell Containing Text And Use An Offset Cell Value In Formula

Feb 13, 2014

I'm trying to do a column of individual discount calculations using a discount percentage found in a cell two to the right of a cell containing the text "Total SP:" that is always upwards and to the left of the cell where the formula goes (but could be two rows or could be 20). There are multiple "Total SP:" cells in the sheet - I always want the first one upwards. I have created the following but I get #NAME? where I hope to see the discounted value.

VB:

Dim Discount1 As Double
Discount = Cells.Find(What:="Total SP:", After:=ActiveCell, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=True).Offset(0, 2)
Range("R9:R" & LastRow).Formula = "=IF(J9>0,\$E9*(1-Discount1),0)"

I've just thought, the Cells.Find needs to be redone for each cell where the formula is inserted to ensure it always catches the correct discount and this isn't going to do that - it's going to find it once and always use that single value.

I think this needs a Loop or something and to move the ActiveCell down one after the formula to get it to redo the Cells.Find.

## To Find Part Contents Of A Cell

Apr 16, 2009

I've got a code that does what I want it to do, but I have to select the start point manually becuase it's position varies on different documents. It's usually around row 650 out of 1200, but the cell I want to start the macro at always contains the word ELECTRONIC. What I would like to do is modify my code so that it finds that cell itself and then runs the rest of the code. I'm not very good with VBA, but I've tried a few things like Dim LR-2 and using that but I get error messages about duplicates.

Here's my code

HTML Sub Macro1()
'
' Macro1 Macro
' SALES ORDER FORMAT
'
'

## Macro Will Not Run Find And Replace With The Contents Of A Cell

Apr 3, 2007

I want to have a macro that will look at a few cells say A1 & A2 and then use the contents of the cells say 1Q07 & 2Q07 respectively to do a find and replace find 1Q07 and replace it with 2Q07. I want it to use the contents of the cell and not just the text because the contents of the cell will change each time used. I tride to do it here but I dont have it yet. It doesnt seem to pass the value to the variable and the variable doesnt seem to do the find and replace.

Private Sub Update_Click()

Application. ScreenUpdating = False
Dim quarter As String
quarter = CStr(txtquarter.Text)
txtquarter.Text = CStr(quarter)

Sheets("Income Statement").Select
Dim oneq As Single
Dim twoq As Single
Dim threeq As Single
Dim fourq As Single
Dim fiveq As Single

oneq = ActiveSheet.Range("A2").Value
twoq = oneq - 1
threeq = twoq - 1
fourq = oneq - 4
fiveq = oneq - 5

## Find Row Max And Display Cell Contents Of Cell To Left?

Aug 12, 2014

I have data arranged in cells A1 to Z1 as Name1, Amount1, Name2, Amount2, Name3, Amount3, etc. Is there a formula that can determine the name that corresponds to the maximum amount in the row?

## Find Highest Value In Column And Then Copy Contents Of Another Cell In That Row?

Aug 18, 2014

I have a worksheet where columns C, F, I, L, O record scores within a league. Each row records a persons score in that league and there are two rows per person recording their score and their handicap score. So Person A would be on rows 3, 4, Person B on 5, 6 etc. The persons name is recorded in Column B.

What I would like to do is to have a cell(s) elsewhere in the worksheet which show the highest score in that league and display that score and the name of the person who achieved it. This ideally would need to be done for the highest score and the highest handicap score.

For the life of me I can't even begin to work out how to do that or even know if it is possible in Excel.

So to clarify, lets say the highest score is in cell L7 and the highest handicap score was in M3. The cell(s) containing this formula should then show the name in B7 and the score in L7 and below it the name in B3 and the score in M3.

## Find Text Based On Active Cell Contents

May 11, 2007

I have a workbook with 2 worksheets, A and B. Sheet A contains source data and sheet B a pivot table of this data. I want my user to click on a cell within the pivot table,click a button that runs a macro to find the instance of this value within Sheet A. I did record a macro but it did not work.