VBA - Lookup In Another Worksheet
Jun 27, 2013
I'm using VBA code to lookup a code and return the description of it from another worksheet.
It works perfectly when I copy the code from the other sheet into the cell used to lookup, or when I enter a code containing a letter. But as soon as I enter the code manually and the code consists of numbers only, it doesn't find the match on the other sheet.
Code:
If Item_insert = Item_codesheet Then
voucherSheet.Range(address).Offset(0, 1).Value = itemcodesSheet.Range("D" & j).Offset(0, 1).Value
End If
I have added watches and converted the codes to string and even though both would be strings, the code doesn't identify them as being similar.
Watch : : Item_codesheet : "700030 " : Variant/String : Module1.Item_to_Description
Watch : : Item_insert : "700030" : Variant/String : Module1.Item_to_Description
View 2 Replies
ADVERTISEMENT
Jun 17, 2014
I have worksheet 1 and worksheet 2. I have a different list of names in column A in both worksheets. Some of the names in worksheet 1 are in worksheet 2, but only a partial list. Worksheet 2 names are not in any kind of order and the rows do not match up with worksheet 1. In worksheet 1, column B is filled with each person's email address. In worksheet 2, column B NEEDS to be filled with each person's email address.
Is there a way for me to take each name, sequentially, on worksheet 2 and find that same name in worksheet 1, then pull the email address from column B (beside the name) and copy it to that cell in column B of worksheet 2?
View 2 Replies
View Related
Mar 23, 2009
I tried both IF and LOOKUP and failed. I'm trying to search for values from one worksheet and identify whether or not those values exist in another worksheet. I attempted the following lookup in field A2:
=LOOKUP(B2,Sheet3!A$2:A$914,Sheet3!C$2:C$914)
B2 (thru B5000 or so) contains values I want to search for; sheet3!A$2:A914 is where I want to look and column C of that same sheet, entered the text "Yes" in an attempt to have the results list "Yes" for hits and N/A for misses. (All fields are text.) I copied the formula all the way down the sheet in column A. The result it is returning is N/A in A2 and Yes in A2 -to the bottom, which is incorrect.
View 2 Replies
View Related
Oct 17, 2008
I have a workbook with 3 workshhets. In the first worksheet I have companies in a ranked order. Based on the company number, I need to return a value from another worksheet that also has the company number as the key.
The lookup worksheet is FY2008 with the company number in column D and the value to be returned in column F. I know it is simple for you Excel gurus but I am still getting my feet wet.
View 2 Replies
View Related
Oct 22, 2009
I have two series of dates – one daily series and the other is the hourly series – running in parallel. The daily series has the values which correspond to them – YES and NO columns which can be either 1 or 0. I need for each line on the hourly series to have the corresponding value of the YES and NO from the daily sheet. I am not sure how this can be done. I need this to be done by formulas only as this sheet only represents a condensed version of the larger workbook.
View 4 Replies
View Related
Aug 20, 2006
I currently have 2 worksheets.
Is it possible to link an Excel Spreadsheet to an Access Data (formula) what I am trying to do for example:
In worksheet 1 cell A I have dates start from (01-Aug-06) and in cell B a amount (GBP 12,589.44)
In worksheet 2 cell A I have dates start from (Aug-06) and in cell B a exchange
rate (1.4526)
what I want in sheet 1 cell D formula (if A=AUG) find or mach the same from sheet 2 and get the rate * the B1)
View 4 Replies
View Related
Mar 19, 2007
I have got a worksheet called data with values in column M7 to M31 which are only there if they meet a criteria >0
if there is a value in cell M9 only, i would like to select the value of M9 and also the description in B9 and insert this into another worksheet called W1 on row C43 and i43 and repeat on C44 and i44 if values are >0 in M11, this would create 2 rows and would expand if there was more matches >0
View 9 Replies
View Related
Sep 1, 2007
I have been working on a macro and am trying to find an answer. To date I have not found what I am looking for so I thought I might ask for assistance.
I have 2 Worksheets that I am working with and I am trying to paste data from one to the other.
The Worksheet1 is my control sheet. The Worksheet2 contains data that I am trying to move to Worksheet1.
Worksheet1 has 3 columns:Ledger #, Balance 1 and Balance 2
Worksheet2 has 2 columns (not named), which are: Ledger # and Balance
I want to move the Balance on Worksheet2 to Worksheet1 and place the data in the Balance 2 column.
For example, the macro would start and look at the value in Worksheet2.A1 and match it against the value in Worksheet1.A5. If there was a match, then the value from Worksheet2.B1 would be pasted in Worksheet1.C5
I have attached a sample workbook.
My goal is to have Worksheet1 have the Ledger # with the 2 different balances (I will balance those 2 columns later).
View 4 Replies
View Related
Sep 7, 2007
i already have a random generator and when i have the result number i want to search the number from other worksheet.
View 5 Replies
View Related
Oct 1, 2009
I need a formula that will look up a cell to get a figure from, but there is three of the same name (sometimes more, depending on different products sold) i.e. "Dept Total" (shown below & attached for easier reading) ....
View 7 Replies
View Related
Oct 13, 2008
I want to be able to look up a cell and do an index lookup on the worksheet with that cells text. Eg.
=index('B2'!A2:E500,match(1,('B2'!A2:A500=E2)*('B2'!C2:C500=D2),0),5).
Where 'B2'! is the worksheet I want to lookup. If cell B2 contained BAZ, the index would lookup worksheet BAZ. If B2 contained TAZ, the index would lookup worksheet TAZ.
View 5 Replies
View Related
Dec 9, 2009
I'm using a v-lookup to pull information from one worksheet to another. I know that each of the cells has a match on the second sheet, but the calcuation is resulting in a Value Not Available error. The only way I've figured out to get past the error is to go into each cell, hit F2 and then enter.
View 2 Replies
View Related
Nov 23, 2006
I need to retrieve the name of a worksheet for a product.
Example
Workbook1 contains the Product number in column A, i want to enter a formula in column B that will search column A in all the worksheets in workbook2 for that product number, and retrieve the worksheet name that the product number is in
View 9 Replies
View Related
Nov 8, 2008
I need a macro to search for certain info on different sheets
combine them and place it on a Report sheet.
This is extensive and complicated so it would be easier if I
emailed the .xls file
If you think you can help, let me email you the file.
View 9 Replies
View Related
Aug 19, 2006
see teh attached file, you can see on the file. Worksheet B of B62, I typed 15254", it col name "yarn count" and the product code you can see is Q921 at cell A62. Worksheet A, you can see F2 is Q921, col name is "product code". Now I just plugin a vlaue B62 ='15254' and I want it shows on worksheet A - G2 but remember it must match the product code of worksheetB - cellA62 'Q921' and worksheet A cell F2 'Q92' because next time user will plugin a number ie.1111 on B164; where product code of worksheet B - cell A164 must be match to worksheet A - cell F3, but it should show the value 1111 on cell G3.
View 2 Replies
View Related
Aug 19, 2006
I am wanting to do is have the values from a number of cells on sheet2 returned to sheet1. The catch for me is that I am using the validation command on sheet1. When I select an item in the validation field I want to return data that belongs with that selection. It may be best with an example....
On sheet2 I have a list of companies in Col.A, their street address in Col.B & their phone number in Col.C.
On sheet1 I have a Validation field that lets me select from the list of companies...
When a Company is selected I want to have the address data & phone numbers returned and placed in the cells beside the Validation box.
View 3 Replies
View Related
Nov 19, 2007
In one worksheet there is a code for every customer and their details e.g. Name, medication they are on, what they are alergic to etc then in another new worksheet can we just type in that code and their individual information comes up automatically?
View 4 Replies
View Related
Nov 29, 2007
I have been using a look up that should be bringing me back a numeric value when i enter a certain code that relates to that value.
When i enter the code 267760RuVw the number brought back shoud be 850
When i enter the code 532210BiMe the number brought back should be 2000
The look up that i am using is =VLOOKUP(B4,Lorry!B3:O37,14)
All my sheets are in the same workbook
The look up value is on a sheet called "Pricing" and the table array is on a sheet called "Lorry"
The cell that the look up is entered into just returns N/A
Why is this not working?
View 5 Replies
View Related
Feb 19, 2008
I am looking to write a macro that can look at a value (i.e. "X") in a cell and compare it to the elements in a 2D array (where one dimension corresponds to the start value and the other to the end value) that is found on another worksheet. If "X" falls between the starting and ending numbers then I would like the macro to return a string (say "Test1") to a cell on the same row and a couple of columns to the left. The string values are listed to the left of the start and end values on the first worksheet and are elements to a 1D array. I am trying to make this " loop" through all the cells that contain run numbers (I have highlighted the columns in yellow on the attached workbook). There are multiple (i.e. +12) worksheets in this project but I have only included two in the sample I am providing because of file size.
View 4 Replies
View Related
Dec 10, 2013
What I would like to do is have a master workbook that I can import different .csv files into as a new worksheet. Then calculations will be run on the values that are imported. My goal is to select a column and have corresponding list update the values. After that formulas will run on the calculations. I have got the import csv file down using VBA. The problem I was running into was with the Data Validation. Since I was overwriting my existing sheet I would get a #REF error because the link was broken. So I have worked my way around this for the list using the OFFSETSHEET Function:
VB:
Function SHEETOFFSET(offset, Ref)
[COLOR=#0000ff] ' Returns cell contents at Ref, in sheet offset[/COLOR]
Application.Volatile
With Application.Caller.Parent
SHEETOFFSET = .Parent.Sheets(.Index + offset) _
.Range(Ref.Address).Value
End With
End Function
This returns a value to a row in my mater sheet and I reference this for the data validation list.
However, I get the same problem when working with the HLookup function, the #REF error occurs. So far the only way I have figured out to work around this is to create another sheet that dynamically updates its values using the same OFFSETSHEET function, and my master sheet then references it.
View 8 Replies
View Related
Jun 26, 2007
I would like a formula to lookup a value across worksheets and if found return the worksheet name that coresponds to that value. I do have this formula below but it will not retrive the correct worksheet name if there are duplcates values.
=INDEX(WSLST,MATCH(TRUE, COUNTIF(INDIRECT("'"&WSLST&"'!B2:B9"),B2)>0,0))
So I tried to use the define names formula method:
Col_B =N(INDIRECT("'"&XWSLST&"'!B"&(2+MOD(S,N))))
N =8
S =ROW(INDIRECT("1:"&(N*ROWS(WSLST))))-1
WSLST =Sheet1!$A$2:$A$3
XWSLST =T(OFFSET(WSLST,INT(S/N),0,1,1))
and modified the formula into this manner: =INDEX(WSLST,MATCH(TRUE,COUNTIF(Col_B,B2=S)>0,0),COUNTIF(B$2:B2,B2))+1
I left a sample workbook below that in column E are the values that I'm expecting.
View 3 Replies
View Related
Jan 15, 2014
How to create a search formula that will look up a person's name in a given worksheet in a specified cell?
View 8 Replies
View Related
Jan 8, 2009
I have a lookup tab which contains three columns: Column A is a tax ID #, Column B is a set of numbers and Column C is a list of street names. I need help with creating a lookup type formula for my column AB in the (worksheet/tab) titled 2009, which will look at and compare the data in columns F & G and compare them to columns B & C in my "lookup" tab and if a similar address is found based on both the street name and number then the tax ID # affiliated with that address from the "lookup tab" will be automatically placed in the column AB in the 2009 tab. I hope this makes sense.
View 4 Replies
View Related
Jun 17, 2009
I have a data table that contains begin dates, end dates, and colors. The data is input starting with the earliest begin date. On a second worksheet, I have a listing of the years starting with the year of the first begin date and ending with the end of this year. I need to be able to pull any color choices associated with a year from the data table on the first worksheet. Some years may not be specifically listed in the data sheet as the person could have had the same color choice for several years. Sometimes a person could have more than one color choice in a year.
I am at a loss for how to get to the data I need. I created an example spreadsheet to demonstrate the data I am working with, and an example of what I need my results to look like in the end.
View 5 Replies
View Related
Sep 28, 2011
determining the correct formula to lookup various details from multiple worksheet:
Data as follows:
Sheet1: input
AB1MonthSales2AUG55003MAY4000
Sheet2: Data
AB1MonthSales2JAN20003FEB25004MAR30005APR35006MAY40007JUN45008JUL50009AUG550010SEP600011OCT650012NOV700013DEC7500
b2,b3 Formula = Indirect(Address(match(a2,????,0),1,1,1,"Data"))
i dont know what is the correct range inside the match fuction to identiy the row number inside the Data sheet, i think the solution would be match(a2,Data!a2:a13,0) but things might get complicated if you have multiple worksheet such as sheet2: Data , sheet3: Data2, sheet3: Data 3
and each table has unsorted details compared to others sheets (Data vs Data 2)
View 9 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
Oct 16, 2008
I have a table that lists the months of the year down from cell A2:A13, and days of the week along row from cell B1:H1. The data in between (cell B2:H13) is pulled through from elsewhere in the workbook and is in number format.
What I basically want to do is create another worksheet with the months of the years listed down column A, and in column B, for each month, I want the first day of the week where the value in the original table is more than zero, and in column C the second day of the week where the value is more than zero. I really hope that makes sense, was quite difficult to explain!
View 2 Replies
View Related
Jan 8, 2009
I'm looking to create a macro that will take data from an input sheet, and paste it into a cell in another worksheet, based on criteria specified in sheet1.
Specifically in the attached example, the macro would copy the data in cells C8:C10 of sheet 1, then paste them into sheet 2 based on the data specified in cell B3 i.e. it would paste them into the column headed Mar-09. I intend to make this cell a drop down, so that the user can then select the next reporting month and run the macro again to paste the data into the Apr-09 column.
View 2 Replies
View Related
Aug 5, 2008
I have created an index of documents in Excel 2003. I have one "master" worksheet containing a list of documents and their corresponding version numbers. On another worksheet, I record details of changes to the documents and hence changes to version numbers. This list will constantly grow over time, and in any order depending on the documents to be changed. However I want certain fields to automatically update themselves, namely the version number.
So for example, I have a column to evaluate if document changes are required, yes or no.
If "Yes" is selected, I want the version number in another cell to automatically update to the next in sequence (add one). So if changes are required, V1 becomes V2 etc. Now the next version number will depend on the previous version number, so before it decides if it adds one, or not, it needs to lookup the value of the current version number in a range. Again, as its a list of different documents this can only be done by first looking up the unique document number.
So to summarise, I need it to lookup the unique doc number in a range and find the one that has the highest version number against it, and return its version number. And then encompass all of this with the If statement to evaluate whether or not the version number needs to add one.
View 9 Replies
View Related
Apr 7, 2014
I have a table of data (say Column1 to Column 5) with multiple rows.
Column 1 to 4 will have the lookup values in multiple rows and Column 5 data should be picked up using vlookup or other lookup function.
I managed to somehow bring all these lookup values in (Column 1 to 4) in a single column in another sheet. I am now trying to use some lookup or other functions to match this single column and pick column 5 data in original sheet. Result i am expecting is lookup value in first column and next to it column 5 value.
It is basically a lookup wherein lookup value is spread over multiple rows and columns and result column is fixed. I tried using vlookup, but lookup value column and column number had to change every time when i moved from column1 to 4.
View 3 Replies
View Related
