Return Offset Data/values With Lookup
Aug 1, 2007
I have all my data on sheet 2 which contains tables that relate to information on regions, manufacturers, etc so it can be a large amount of tables which will change from month to month depending on a monthly list.
For example, if “Region 1” is in cell B134 – the resulting data I need to pull out will be contained in C138:G232 ...or ”Region 2” which is in B235 – table info is in C239:G333, etc, etc.
On sheet 1, I have a cell (L7) that is populated by another formula. This cell is effectively my lookup to pull out information from sheet2 to populate cells in (sheet1) AE119:AH212 - same sheet as the reference (L7).
I need to look up the reference/resulting value that is in L7 on sheet1, find and match against the values in column B on sheet 2 and then pull in the corresponding table information.
If the value in L7 was not changing at all I could do.. in cell AE119… =OFFSET(sheet2!B134,4,1,1,1) etc etc
... but I am at a loss as to the value in L7 changing and incorporating a lookup… or maybe I am looking at it wrong..?
I have looked up match, offset, index and lookups on the forum and have managed to confuse myself even more. I have even tried taking some of the example formulas and amending with my references but to no avail.
View 10 Replies
ADVERTISEMENT
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.
View 4 Replies
View Related
Jan 19, 2014
Trying to grasp the concept of using these 3 functions to search for and return values from a data sheet.
The attached spread sheet has performance data for a group of employees.
What I need to do is find a particular employee then return a value for one of the category's.
For instance, I need to find "10TE03 ANGIE HOLLIS" Parts Usage on color or cell C10 in the attached sample.
Sometimes new category's are added to column A adding to the number of rows so a simple offset is not reliable.
Once I get that working, I then need to use a named range to total and average different data points for groups of employees by teams.
Maybe Offset-Index-Match is not even the way to go here?
View 7 Replies
View Related
Oct 30, 2006
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.
View 7 Replies
View Related
Jan 30, 2008
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.
Can I use a VLOOKUP/OFFSET combo or Index/Match?
I am writing the formula in column H of sheet 1.
View 3 Replies
View Related
Mar 29, 2008
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.
View 9 Replies
View Related
Jul 17, 2009
I have a worksheet containing a list of states in column A and a list of companies in column B (along additional data in columns C-L). I want create a lookup formula on a separate worksheet where users can select the state from a dropdown menu and it will return the information from columns B-L for that state.
For example:
A B C D
Alabama ABC Company Active Expires December 2009
Alabama 123 Company Expired Expired April 2008
Alabama XYZ Company Active Expires August 2009
Alabama Larry Company Expired Expired May 2006
How can I do this without losing my mind? I've tried various Index, Small and Match formulas and none of them work.
View 9 Replies
View Related
Oct 23, 2008
I have a problem with the formula that lookup all values in ascending order and returning all the corresponding values. eg: I was intended to lookup for the value in ascending order under the Total Occurrence and returning all the corresponding value under the Nos Group but encountered the same Nos Group was returned when there is same value appeared under the Total Occurrence.
View 3 Replies
View Related
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.
View 3 Replies
View Related
Nov 19, 2008
Hi there, please can you help me transfer data from 1 spreadsheet to another (I have attached a file which has 2 sheets).
I am trying to populate columns G to L (sheet B) with data from column H (sheet A) - the lookup is between 'Material' & '7410','7090','7680','7060','8630','7580' on sheet B & material & plant on sheet A.
I have tried H&VLOOKUP & INDEX/MATCH but I keep returning a #N/A..
View 10 Replies
View Related
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
View 9 Replies
View Related
Nov 11, 2008
Is there a way to use a lookup formula to return all values of a given text, not just the value of the first listing of the text. In an attempt to clarify my already confusing request, if A5 on "worksheet1" is "Land" I want E5 on "worksheet1" to return the summation of values assigned to "Land" "worksheet2" "column B" any time "Land" appears on "Worksheet2" "Column A".
View 2 Replies
View Related
Feb 4, 2010
I have a column of values for example:
114
162
92
114
120
120
114
162
I need to be able to search this column and return the first instance only of each number in a different column in numerical order - which in this example will result:
92
114
120
162
View 9 Replies
View Related
Apr 1, 2014
I am trying to use a formula similar to VLOOKUP() to look in a table for two values and if both values are found, return another value.
Sheet2 is an example of my output file, sheet one is a generated file. i would like the vlookup formula in sheet 2 cell D2 and down to be something like this,
=VLOOKUP(A2,Sheet1!A:K,10)
But I would like to lookup A2 and B2, and the row that contains both values, return the value in the J column
View 3 Replies
View Related
Jan 20, 2014
I am wanting to use a vlookup or perhaps an array(?) to bring through multiple cell values based on being the maximum value. So say there are multiple employees (all with unique ids) under one manager (also with unique ID), I want to find the employee with the highest value in a particular column and return that employees details, such as name, employee number and the value itself, which are all in the same row. I have attached a basic example of the data involved.
View 8 Replies
View Related
Jan 18, 2010
In the attached sheet, I want to look up values from table (A1:G5) and then for each occurrence of a particular number, return the value in the same row in column H, and sum when there are multiple values (see green shaded area for desired output. For example, the number 8 occurs in E3, E4, and G5. I want to return the sum of the corresponding values in column H (1,5, and 2). I've spent hours trying to work this one out, I'm sure this is probably an array formula, but I can't manage to make it work.
View 3 Replies
View Related
Jan 5, 2007
the formular that i have in the attached file is returing all the customer numbers
Is it possible to return only one value of each
the data is in the "data" sheet and the formulars are in sheet1
View 3 Replies
View Related
Feb 4, 2008
I am currently looking at the workings of a spreadsheet designed by someone else.
First of all i need to know,how these combo boxes are created in the attached sheet,because it cant be edited.in addition to tht in the coloured cell (F17)i'm trying to dereive a formula which is,if (C17 = doll "1"),but its not working.Please someone give me a solution.
View 9 Replies
View Related
Aug 11, 2008
Im trying to work out how to write some code to find and replace from a list.
I have key data in column P on sheet 1, and a list on sheet 2 with the same key data in column C.
Basicially, every month the data on sheet 1 changes (there are usually 100 rows of data each month) and what i want to do is, write some code to look down column P on sheet 1 and for every cell, find the corrosponding value on sheet 2 (column C). When it finds the value, copy and paste it onto sheet 3 then delete the row from sheet 2.
I hve written some code that can do the first part (copy and paste) and a second macro to do the deleting....but it is only for 1 value at a time...
View 8 Replies
View Related
Apr 20, 2014
I have 3 workbooks "ID numbers, 07 Car,and 08 Car".
I need a VBA code that will use the value in column C in each workbook, look up the value in workbook "ID numbers", column C thru G, and return
values into each workbook:
Workbook ID number Column C add values to Each workbook (Column B)
Workbook ID number Columns D thru E) add values to Each workbook (Columns D thru E)
See 07 Car workbook highlighted in orange, every product below the orange highlight must look like for both workbooks this after the code is ran.
View 5 Replies
View Related
Dec 11, 2012
I'm trying to find a way to:
Use a referenced lookup value from sheet "A", to return values, from several columns in sheet "B"
Things to note:
a) The lookup values sometimes repeat. I need all the associated values with each repetition as well.
b) The lookup values in sheet "A" are a comprehensive list, sheet "B" also contains some of these values but not all. Essentially, what I need to do is find a way to lookup each value in an account numbers column in sheet "A", against a different account numbers column in sheet "B".
If that value occurs in sheet "B" I want it to return the values from Columns X, Y, Z, (I want these values returned in sheet "A".
If that value does not occur in sheet B, the corresponding cells should remain blank.
If the lookup value occurs multiple times, I need all the corresponding values from each of X, Y, Z columns.
View 2 Replies
View Related
May 30, 2006
I know something very similar was posted before, but unfortunately, it wasn't what I was looking for. I have a Worksheet tracking several associates and the department they have done work in. (Our associates are contractors for other departments). I need to have a final worksheet that allows me to pull up a name, and display every dept they worked with in the past week.
View 9 Replies
View Related
Feb 20, 2007
i would like the code that allows me to copy enything I typing in column a sheet1 to column a in sheet2
View 8 Replies
View Related
Dec 10, 2007
Working on an export from an e-commerce application that lists the item purchased, unfortunately not by item #, but rather a lengthy description. I'm trying to construct an item number based on that description which contains several constants.
Each item ordered appears in a single row, comprised of ORDERID, QTY, PRODUCT DESCRIPTION
I have several tables to pick out PRODUCT TYPE, COLOR, SIZE, etc. I need a formula to search to see which of the unique values appears in each in the cell and return the shortcut of that value.
PRODUCT DESCRIPTION example "Sweater - Blue - Large - Wool"
TYPE TABLE:
Sweater SWTR
T-Shirt TSHT
Jeans JEANS
COLOR TABLE:
Blue BL
Green GN
Purple PL
Black BK
I would have separate tables for each part of the description.
The formula I'm seeking would search the PRODUCT DESCRIPTION cell for the range of values for each 'part'. So it would search to see which of Sweater, T-Shirt, or Jeans was in the cell and then return the one that was - SWTR in this example. I would modify this formula for each 'part', in its own cell, and then use an =cell#&"-"&cell#&etc... to combine these returned values.
I know I can write a large if- chain using ISERROR & FIND, though some of these lists will get lengthy.
View 9 Replies
View Related
Mar 17, 2008
I need to lookup data based on a cells content. The answer has to output to a table as a sequential list. I have attached a sample workbook (there will be 100+ sheets in the final version!). The Register sheet lists competencies against page# and title. For each Evidence Sheet I need to populate the Competency grid (C4:D10) in a sequential manner i.e. no blank lines,and the Title (B13) based on the Page # (D2). I have looked at a great number of postings, but cannot see a solution that fits.
View 7 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
May 28, 2008
I need to create a macro that will loop through a list of names on Sheet 1 / Column A, and if that name is listed on Sheet 2 / Column A, then it will set Sheet 1 Column B (for that one name) to "1".
The problem I've run in to is that both the lists change in length each time I have to run this (so no set range).
I've been able to get this to work with two ( nested) loops, where the first loop goes through the Sheet 1 list and the nested Loop looks for the name on Sheet2, but it is extremely slow.
Is there any simple VBA code or formula that I'm missing that could do this? I've tried to do something with the " Find" VBA command, but I can't see how it would apply to this if the list changes in size.
View 8 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
Mar 3, 2014
In column CT between rows 11:210 is the vertical data that includes duplicates. I am looking to create a formula that can lookup each value between CT11:CT210 and return only the unique values horizontally starting in cell CW9 and onwards.
Example: CT11:CT14 looks like
5x20
6x4
5x20
5x8
and so on and so forth..
I would then like this formula to return the above data (which goes all the way to CT210) in this format starting in cell CW9 - 5x20 then CX9 - 6x4 and finally CY9 - 5x8.
View 4 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
