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.
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.
I am trying to work out how to nest offset in a code using search replace. The goal is to find a value from cell A1 (example) and replace the values in the cells next to the cell containing samuel and the cell containing samuel.
A sort of search and remove data tool if you like So for example:
[QUOTE]A1: "samuel" (the search criteria) Search range is: B1:D400 (for example) Cell B40 = samuel C40 = Driver D40 = year
So, the macro is activated and finds "samuel" in B40 and I would like C40 & D40 replaced with "" The code I'm using is below: (this is just replacing the cell containing the search criteria with "test". I would like to nest offset(0,2) & offset(0,3).Value = ""
Sub Macro1() Range("B1:D400").Select Selection.Replace What:=Sheets("Sheet1").Range("A1").Value, Replacement:="test", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End Sub
i'm running out an option, i must use offset or index function but there are problem found: 1.Offset function cannot use a dynamic height or number of columns and the result are #NAME?, here is the code
VB: Private Sub CommandButton1_Click()Dim k As Variant Dim b As Variant Range(Me.RefEdit1).Name = "item"
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.
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.
The cell name "5260", is also entered as text in cell A1, in the current workbook (not calculos.xlsx). How do I refer excel to get the text from cell A1, instead of having to enter it manually.
I'm working in Excel 2010. I'm trying to create a dynamic range, using the OFFSET function. I've got it working beautifully but as soon as I save it and close the Name Manager the range stops working. When I go back into Name Manager I find all the cell references have changed (from C1:C600 to C1048572:C595 - or some other strange range).
P.S. What it's doing isn't so important as when the cell references are right it works. But just for full info: It's an OFFSET function, starting at A1. It will look at a list of companies and use a Match function to find out how many rows to offset (based on where the first instance of a particular company. And it uses the COUNTIF to make the range the same number of rows as there are entries for that company.
I am looking to create a Macro that says: look at all of the data in column E:E and if you find a cell that has the word "Ohio" in the string of information, then offset that cell by 3 to the right and make that cell equal to "4"
Sub Test()
Columns("E:E").Select Dim rng As Range, cell As Range Dim worksh As Worksheet Set worksh = Worksheets("Axys Layout") Set rng = Range("E:E") For Each cell In rng If InStr(1, cell, "Ohio", 1) Then cell.Offset(, 3).Value = 4 End If Next Ecell End Sub
This is not working and I'm not even sure that I am on the right track!
(1) the user inputs a number into cells B2:B6 (arbitrarily shown as 3,4,3 and 2)
(2) the user then inputs a series of values into the respective columns E, G, I or K (corresponding to examples A-D, respectively) with SPACING between each value corresponding to the value entered in B2:B6.
For example, the value "4" is chosen in B3, so values are entered in column G at time = 0,4,8,12,16,20....etc
As another example, the value "3" is chosen in B4, so values are entered into column I at time = 8,11,14,17,20,23...etc, ie. the starting point is not necessarily t = 0.
(3) once the values have been entered as described in point (2) above, a series of results are automatically calculated in columns F,H,J and L.
NOTE: I have omitted the formulae for the calculation and have just entered arbitrary (color coded for clarity only-i don't need color coding!) numbers alongside each inputted value for the 4 examples A-D.
OK, so what I want to extract from the table for each example is the paired time AND result values, and then show them in a new table.
I have used an OFFSET formula (originally suggested by "daddylonglegs") to do this, and the results i GET are shown in columns O-V.
HOWEVER, what i WANT is the output as shown in columns Y-AF.
So as you will notice, there are 2 problems:
(1) Example C in columns S/T: value in cell B4 = 3, but the first value entered in column I does not start at a multiple of 3, so no values are returned in columns S/T because the offset function only uses row 6 as a reference point. What i want is the result shown in columns AC/AD.
How do i write a formula which says, "look down a column, find the first cell that has a value in it, and then offset from that cell/row" ?
(2) Example D in columns U/V: since the first input in column K starts a t = 12 (ie, cell K18), then i have 5 rows of empty space in columns U/V. What i want is the result shown in columns AE/AF.
Lastly, i should add that i am not necessarily stuck on using OFFSET function, since i keep reading that it is quite volatile.
I have sheet1 with four columns. Team, Issue, Discussion and Recommendation. Discussion and Recommendation headings were placed in column B along with the data in the file I was sent. I need to get it back into table format to eventually load into Access. What I'm trying to do is to loop reading down column B. When I find the word “Discussion” (exact format) I need to drop down one row below the word, cut whatever is in the row, move one column to the right, Column C, and two rows up and paste. For example, cell B3 has “Discussion”. I need to drop to B4, cut whatever is in the cell and move to C2 and paste. I also need the same to happen for Recommendation. Find the word, drop down one row, cut whatever is in the cell, move two columns to the right and up four rows. I would like to delete the rows I moved data from, but will do that by hand if it’s too hard to do in a macro.
An example of my data: TeamText Design4. Reuse existing code to extent possible. DesignDiscussion DesignNo discussion DesignRecommendation DesignEstablish a baseline and work to the plan. Design5. Delete old code DesignDiscussion DesignNo discussion DesignRecommendation DesignNo recommendation Design6. Plan to work without full details. DesignDiscussion DesignIncremental design DesignRecommendation DesignWork on smaller details first
In Mr Excel's Pod Cast on April 12th, he showed how to use the OFFSET function to define a range inside a SUM function. Then he had Conditional Formatting that would highlight the range that was being summed. Can anyone tell me what the formula would be inside the Conditional Formatting dialog box to get the OFFSET range to have a certain format?
I have a large array of cells that go from A1 to A2500. In VB, I need to take a given date that resides in a cell, say 1/6/2006 in C2, and look for a match of it within this large array. When the match is found, I need it to look in the column to the right (IE, from A5 to B5) and assign the value there to a Cell (C1).
Could someone provide an example of the code I was use in VB for this? I'm having a lot of trouble with it.
I am referencing a value in column A of Sheet 1. I am looking for that value in column B of Sheet 2 and would like to return the value of the cell that is 1 row below and 4 columns to the right (column F). I need to do this multiple times.
In row E10:Z10 I have a series of values and in row E6:Z10 I have dates associated with each of the values in row 10.
I would like to create a formula to look in E10:Z10 for the maximum value and then return the date associated with that value. For example, if the maximum value is in P10, I would like the formula to return the date in P6.
I have tried without success to use the OFFSET function as follows:
I am trying to write two different codes to copy a range of data. Basically I am trying to understand how the OFFSET function works in VBA. The first code listed works fine and is as follows:
I would like to use the offset function as follows
Each Month a new column will be Inserted in Column V
I need to sum data starting three columns to the right of column V and then two column to the right of that plus BC15 (this will also change each time a column is inserted in Col V
I have used a sum formula before inserting a new column showing you what i am trying to achieve ...
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)
I have a formula that basically work, except is really complicated and slows the whole sheet down and then, for some reason, returns a circular referencing error or something. There must be a better way!
So the back story is that I have a spreadsheet that I'm using to track ~1200 employees, including salary, job title and, where I'm having trouble, reporting line.
Column [R] is the Line Manager (direct manager) and Column [S] is Department Head. Columns [T] - [Y] is "Manager", "Manager +1" (manager's manager), "Manager +2" (manager's manager's Manager), etc up to Manager +5.
'Line Manager' is input manually. 'Manager' = 'Line Manager'. Manager+1 to +5 just vlooks up off the same sheet. This part works fine and will update the whole reporting line if you change the manager.
Department head is where I'm having a problem! Essentially what I'm looking for is for the formula to look at the 5 cells to the right (the manager +# columns) and look for 'Mr Boss' and return one cell to the left since department heads report the the boss. There is one exception, its that some immediate reports of Lucy Lawful also are counted as Department Heads. For this purpose I vlooked up this list of exceptions on another sheet. My formula is this (entered as an array):
Below is an example file. In my real file the formula works for everyone, not sure why its not working for half the employee now, but probably cos it's a useless formula.
I have a data range set up in a step chart formation. This means there is a "Name" to the left and a time line of "values" to the right in quarterly increments (3 months at a time). Row 1 has the first month of each quarter and should be used to find the start date. Row 2 has the last month of each quarter and should be used to find the end date.
I need to find the first value from left to right and return the date directly above it in row 1.
Then....
I need to find the last value in the step chart and return the date directly above it in row 2
Example Start Date..A...B...C....D...E...F End Date....U...V...W...X...Y...Z Value-1...........5...2....7........
Value-1 Start Date: B End Date: X Duration: = B - X
The reason i need this information is to create a Gantt Chart.
I'm trying to create a formula that would return the value of the column next to it. Looking at the below example, in a new cell I would want to be able to return the value 7 based on the fact that it is in category b.
I would think an if/offset/match function would do this but I can't seem to figure it out as it may require nesting.
I have a total line that gets moved down with each additional newly inserted row from a vba macro. I need the total line to sum all rows in column "B" beginning with the row immediately above the total line and continuing to 30 lines above that.
I was wondering if there was a way to use sumif and offset to guide the summing column in the example below. Perhaps I need to add an additional column, then formulate a solution. I want to sum the daily returns for the TSX when the slope is positive (green), and sum the S&P when it is negative. Currently, on signal day, I am summing that day's return, which I don't want to do. I want to sum starting on the next day (blue). The B column switches back and forth from positive to negative, so I will summing different data periods going back 1000 days. I am summing the TSX returns and S&P returns seperately to make it easier. Currently I have in C1=(sumif(b2:b1000,">0",c2:c1000)) Same for D2.
find attached the excel sheet. In sheet1 I have named the range I have inserted a Name(Test) for range $A$1:$A$2(range contains only texts) and have a list validation on cell $B$1 using the name. This works perfectly. In sheet2 I have named the range I have inserted a Name(Test1) for range $A$1:$A$2(range contains only numerics) and have a list validation on cell $B$1 using the name. This doesn't work.
I would like a consistent OFFSET function formula across a row but my columns move from monthly to semi-annual periods at a point in time. How do I handle the move from monthly to semi-annual periods in my OFFSET formula?
