Find text within a range and return row number

[Monsignor]

WOW! I thought this would be simpler that it's turned out. Please help!

In range A1:G50 there are words. The columns are in no order.

What is a formula that would return the row number of a word I'm interested in?

I would like to type "Farm" and have Excel tell me it's in row 37.


What to do?

 

[Peter_SSs]

This is a smaller example but I think should help you. It assumes the word of interest will only occur once in the range.

I am wondering why you want the row number though. Is is part of some 'next step'?
If so, if you explain what you are trying to achieve it may be possible without actually getting the row number.

Excel Workbook
	A	B	C	D	E	F
1	Apple	House	Mouse		Word of interest	Farm
2	Pear	Mirror	Tree		Row	3
3	Dog	Farm	Post
4	Cat	Pin	Nail
5			Cup
6
7
Row Number

 

[markmzz]

Or try this array formula (use Ctrl+Shift+Enter and not only Enter):

=MAX((A1:C5=F1)*LIN(A1:A5))

Note: I used the sheet of Peter_SSs example.

Markmzz

 

[Peter_SSs]

Or try this array formula (use Ctrl+Shift+Enter and not only Enter):

=MAX((A1:C5=F1)*LIN(A1:A5))

Note: I used the sheet of Peter_SSs example.

Markmzz

Translation issue?
LIN = ROW?

 

[Monsignor]

This is a smaller example but I think should help you. It assumes the word of interest will only occur once in the range.

I am wondering why you want the row number though. Is is part of some 'next step'?
If so, if you explain what you are trying to achieve it may be possible without actually getting the row number.

Thanks for asking if this is an intermediary step.
Yes it is.

I'm doing some validation and error-handling to ensure that a category and item are appropriately matched when they are selected using dependent dropdown lists.


Column A has categories
Columns B through G have items in those categories

But on a separate worksheet the dependent dropdown lists have 3 categories leading up to the final selection.

I want there to be a warning if someone chooses an item that doesn't match a category.

Scenario: someone chooses the category Buildings and selects Farm.
But then they go back to change the category to Fruit and
never clicks the dropdown for a refreshed Items list to choose a Fruit.


I want a check that says, Farm is not a fruit. STOP AND FIX THIS!
The idea is conditional formatting that turns a mismatch red.

 

[Peter_SSs]

What about if we identified the invalid entries and gave the user an option to clear them or leave them (or we could just clear them without asking if you want).

What Excel version are you using?

How many Data Validation cells do you have? If it isn't too many can you tell me where they are and indicate which ones depend on which other ones?

I suggest that you test this on a copy of your workbook.

To implement ..

1. Right click the sheet name tab (the sheet that contains the actual drop-downs that the user will be selecting from) and choose "View Code".

2. Copy and Paste the code below into the main right hand pane that opens at step 1.

3. Close the Visual Basic window.

4. Choose some valid entries from the dependant drop-downs then go back and choose a different value in one of the earlier drop-downs so that a remaining entry is invalid.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim DVcells As Range, DVChanged As Range
Dim rInvalid As Range, c As Range
Dim Ans As VbMsgBoxResult
    On Error Resume Next
    Set DVcells = ActiveSheet.Cells _
        .SpecialCells(xlCellTypeAllValidation)
    On Error GoTo 0
    If Not DVcells Is Nothing Then
        Set DVChanged = Intersect(DVcells, Target)
        If Not DVChanged Is Nothing Then
            Application.EnableEvents = False
            Set rInvalid = Cells(Rows.Count, Columns.Count)
            For Each c In DVcells.Cells
                If Not c.Validation.Value Then
                    Set rInvalid = Union(rInvalid, c)
                End If
            Next c
            Set rInvalid = Intersect(rInvalid, DVcells)
            If Not rInvalid Is Nothing Then
                ActiveSheet.CircleInvalid
                Application.ScreenUpdating = True
                Ans = MsgBox("The following cell(s) now contain invalid data" _
                    & vbLf & "and have been circled on the worksheet." _
                    & vbLf & "Clear invalid cell(s)?" _
                    & vbLf & rInvalid.Address(0, 0), vbYesNo)
                If Ans = vbYes Then
                    rInvalid.ClearContents
                End If
                ActiveSheet.ClearCircles
            End If
            Application.EnableEvents = True
        End If
    End If
End Sub

Note that this will only highlight/clear the next level down from the original changed cell. However, if further entries are made that make other cells invalid, they should show up as invalid.

Anyway, give it a go to see if it is worth pursuing.

 

[Monsignor]

Peter_SSs, thanks for the VBA code. My reluctance to go that route is that I don't know much VBA and I may be the one modifying, maintaining and troubleshooting the spreadsheet in the future.

BTW, the SUMPRODUCT solution worked brilliantly! I've got the conditional formatting added and it's working fine so far.

 

[Peter_SSs]

BTW, the SUMPRODUCT solution worked brilliantly! I've got the conditional formatting added and it's working fine so far.

Great, thank for letting us know.

Peter_SSs, thanks for the VBA code. My reluctance to go that route is that I don't know much VBA and I may be the one modifying, maintaining and troubleshooting the spreadsheet in the future.

I understand that and I was probably trying to be a bit too fancy with that code.

Perhaps you might try giving this one a test? Not much to maintain here and at least it would be easy to remove if you didn't want to stick with it.

Same implementation as before (remove any previous code):

Private Sub Worksheet_Change(ByVal Target As Range)
    ActiveSheet.CircleInvalid
End Sub

 

[markmzz]

Translation issue?
LIN = ROW?

I forgot the translation, thanks Peter_SSs.

Markmzz

 

[Mediawhapper]

Very cool!