Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
Need to find a row in a worksheet based on the value of a cell
Hi - My Excel sheet has two columns that represent minimum and maximum income limits - looks a little like this: Column A Column B 1 3 4 6 7 10 11 13 and so on up to 3800 or so. I want users to be able to enter a number, and be taken to the row that corresponds; for example, if a user enters 12, I want them to be taken to the row where column A = 11 and column B=13. I've managed to come up with the VLOOKUP statement to find the closest matching number in column A (VLOOKUP("data entry cell",A3:B1162, 1, TRUE)), but I can't figure out how to use the result of the VLOOKUP in a Find function to take the user to the appropriate row. Any help would be appreciated. Ginger -- Ginger ------------------------------------------------------------------------ Ginger's Profile: http://www.excelforum.com/member.php...o&userid=27034 View this thread: http://www.excelforum.com/showthread...hreadid=442769 |
#2
|
|||
|
|||
Worksheet functions can't change selections. You'll need an event macro
to do that. One way: Put this in the worksheet code module (right-click the worksheet tab and choose View Code): Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim nMax As Long nMax = Cells(Rows.Count, 2).End(xlUp).Value With Target If .Address(False, False) = "D1" Then _ If IsNumeric(.Value) Then _ If .Value = 1 And .Value <= nMax Then _ Cells(Application.Match(.Value, Range("A:A"), _ True), 1).EntireRow.Select End With End Sub This assumes that cell D1 is the user entry cell - modify to suit. In article , Ginger wrote: Hi - My Excel sheet has two columns that represent minimum and maximum income limits - looks a little like this: Column A Column B 1 3 4 6 7 10 11 13 and so on up to 3800 or so. I want users to be able to enter a number, and be taken to the row that corresponds; for example, if a user enters 12, I want them to be taken to the row where column A = 11 and column B=13. I've managed to come up with the VLOOKUP statement to find the closest matching number in column A (VLOOKUP("data entry cell",A3:B1162, 1, TRUE)), but I can't figure out how to use the result of the VLOOKUP in a Find function to take the user to the appropriate row. Any help would be appreciated. Ginger |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referencing data in different worksheet based on month name | Excel Worksheet Functions | |||
How to create a fill down that increments based on the worksheet n | Excel Worksheet Functions | |||
Weekly Transaction Processing | Excel Worksheet Functions | |||
How Do I Find A Specific Worksheet | Excel Discussion (Misc queries) | |||
Need find cell b3 value on another worksheet! | Excel Worksheet Functions |