Home > Forum Home > Planning and Managing Projects > Locate row based on cell data | Share |
Forum Topic | Post Reply Login |
Locate Row Based On Cell Data | Rate this: (3/5 from 1 vote) |
Cell A1 contains variable. Macro must look up a column and match A1 data to value in the column and select that row which contains the data so that I can update things in that row. Clear as mud in a beer bottle? e.g. If cell A1 is 10 and in column B there is a series of numbers 1-20 & 10 is in row 14, then select that row in column B so that can copy variables to certain cells the row. Doable? | ||
Posted by ALF on |
Replies - Displaying 1 to 5 of 5 | Order Replies By: Most Recent | Chronological | Highest Rated |
Rate this: (3/5 from 1 vote) Taking your example, the following spreadsheet shows the solution using formulas. Excel Spreadsheet:
Cell A2 finds the index number of column B by matching the value in cell A1 using the MATCH function. The value is 4 as the number 4 is the fourth row in column B. The last parameter is set to 0 to find the first match. Setting it to 1 will find the last match. Cell A3 then gets the cell address of the matching row number and column B (2) using the ADDRESS function. The value is $B$4. Finally, cell A4 creates a hyperlink to the address using the HYPERLINK function. This creates a link in the cell and when clicked goes to the address value in cell A3 - in this case B4. Note that when creating this formula, we need to add the "#" symbol to indicate that the destination is within the same worksheet (in the same way as defining an HTML bookmark link). Combining all of the formulas, we can simply create the dynamic cell hyperlink as one formula in cell A2 as follows: =HYPERLINK("#"&ADDRESS(MATCH(A1,B1:B5,0),2),"Go to row") | |||||||||||||||||||
Excel Business Forums Administrator | |||||||||||||||||||
Posted by Excel Helper on |
Rate this: (3/5 from 1 vote) to that row. HYPERLINK("#"&ADDRESS(MATCH(A1,B1:B5,0),2),"Go to row") tried many combinations of this to create a macro without sucess | |
Posted by ALF on |
Rate this: (3/5 from 1 vote) The idea would be to make it dynamic enough to be used for multiple purposes. | |
Excel Business Forums Administrator | |
Posted by Excel Helper on |
Rate this: (3/5 from 1 vote) | |
keylogger detector | |
Posted by micklejony on |
Rate this: (3/5 from 1 vote) =INDIRECT(ADDRESS(row number,column number)) | |
Excel Business Forums Administrator | |
Posted by Excel Helper on |
Displaying page 1 of 1 |
Find relevant Excel templates and add-ins for Locate row based on cell data in the Excel Business Solutions Directory |