Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
In Excel, How to get cell reference of a particular word in anoth.
Hi, Actually I have to copy a value from one sheet to another which I had
done easily by typing '=' and then clicking on the cell in that sheet. But, I want to search for a word in another sheet, find the cell reference which is next to that word. For eg., In c5 value is 'YZ Bank'& in C6 value is 10 In D5 value is 'BC Bank'& in D6 Value is 16 What actually I need is, In a new sheet, I want to search for 'BC Bank' and get the value 16 from that sheet. Please help me with this. Thanking you Best regards. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
In Excel, How to get cell reference of a particular word in anoth.
Nivas,
Here is a starter Dim oCell As Range Set oCell = Cells.Find(What:="BC Bank", _ After:=ActiveCell, _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not oCell Is Nothing Then MsgBox oCell.Offset.Address & " contains " & oCell.Offset(0, 1).Value End If -- HTH RP (remove nothere from the email address if mailing direct) "Nivas" wrote in message ... Hi, Actually I have to copy a value from one sheet to another which I had done easily by typing '=' and then clicking on the cell in that sheet. But, I want to search for a word in another sheet, find the cell reference which is next to that word. For eg., In c5 value is 'YZ Bank'& in C6 value is 10 In D5 value is 'BC Bank'& in D6 Value is 16 What actually I need is, In a new sheet, I want to search for 'BC Bank' and get the value 16 from that sheet. Please help me with this. Thanking you Best regards. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
In Excel, How to get cell reference of a particular word in anoth.
Have a look in HELP index for Vlookup
-- Don Guillett SalesAid Software "Nivas" wrote in message ... Hi, Actually I have to copy a value from one sheet to another which I had done easily by typing '=' and then clicking on the cell in that sheet. But, I want to search for a word in another sheet, find the cell reference which is next to that word. For eg., In c5 value is 'YZ Bank'& in C6 value is 10 In D5 value is 'BC Bank'& in D6 Value is 16 What actually I need is, In a new sheet, I want to search for 'BC Bank' and get the value 16 from that sheet. Please help me with this. Thanking you Best regards. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
In Excel, How to get cell reference of a particular word in anoth.
Hi Nivas:
Command to use is... =HLOOKUP(lookup_value,table_array,col_index_num,ra nge_lookup) Example =HLOOKUP(D1,A2:B20,2,TRUE) placed in D2 cell where... D1 = The information to lookup (search) cell data, enter for example 'BC Bank' in cell A1. (Use any cell or even 'sheet2' cell.) 'A2:B20' = The Range of your data to lookup. 2xFields and 19xRecords. The 1st Column is your data record fields starting from left to right, The 2nd Column is the 1st data record, The 3rd Column is the 2nd data record, The 4th Column is the 3rd data record etc., '2' = The database row number you wish to lookup data. 1st cell of the range on the top LHS is row 1. 'TRUE' = Whether you wish the action to be True or False (should be TRUE in most cases). TIPS 1: The 1st row of your data records must be the search data entered in cell D1 of the formula. 2: Define the database range by giving it a name. "Insert/Name/Define" from the menu. 3: Click on the 'fx' command button (very small) just to the left of the formula entry, for ease of entry & help. 4: If your data fields are horizontal (Records would also be horizont) then use the VLOOKUP command instead. 5: If using another excel sheet for the data the syntax is... =VLOOKUP(D1,Sheet2!A2:B20,2,TRUE). 6: If using another excel filename for the data the syntax is... =VLOOKUP(D1,[filename.xls.Sheet2]Sheet2!A2:B20,2,TRUE). where... 'filename' = the other excel filename, 'Sheet1' = the sheet name used in that filename, '!' = signifies the end of the lookup data source formula string (I guess). 8: Value of col_index_num: If the data you wish to lookup changes rows, then you must find the location of the 'BC Bank' cell first and add 1 to it if it's to the right of that cell. 9: During the lookup process you may have incorrect results if the search data is not unique. 10: Another formala you may want to try is MATCH(lookup_value,lookup_array,match_type) eg. Assuming you have only 2 fields and 20 records and they are on sheet2 & D1 is the Current Sheet1 =hlookup(D1,Sheet2!A1:B20,2,TRUE) eg. for below. All on the same Sheet =HLOOKUP(A1,C5:H6,2,TRUE) in cell B1. A B C D 1 BC Bank =HLOOKUP(A1,C5:DH6,2,TRUE) 2 3 4 5 YZ Bank DC Bank 6 10 16 Wow, looking back, that looks more complicated than I ever thought. I've had a quick check and hope it's accurate and I covered the important aspects. Cheers Sharpy "Nivas" wrote in message ... Hi, Actually I have to copy a value from one sheet to another which I had done easily by typing '=' and then clicking on the cell in that sheet. But, I want to search for a word in another sheet, find the cell reference which is next to that word. For eg., In c5 value is 'YZ Bank'& in C6 value is 10 In D5 value is 'BC Bank'& in D6 Value is 16 What actually I need is, In a new sheet, I want to search for 'BC Bank' and get the value 16 from that sheet. Please help me with this. Thanking you Best regards. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Countif contains a word in a reference cell | Excel Worksheet Functions | |||
Copy last cell with data in column E on one sheet to cell on anoth | Excel Worksheet Functions | |||
How do I Reference an Excel cell in Ms Word | Excel Discussion (Misc queries) | |||
formatting a cell to display a word when i enter a reference lette | Excel Discussion (Misc queries) | |||
clicking cell containing text, and a numeric value appear in anoth | Excel Discussion (Misc queries) |