If this, then that

[BWMagee]

Hi, I would like to auto-fill a cell based on conditions. For Example:

We have "Sheet1" with text 'Brad' in B3, and 'Secretary' in F3. (Brad is the secretary).
So when I enter 'Brad' in any row in "Sheet2" (eg. C4), then I want the same row, in column D (eg. D4), to auto fill with 'Secretary'

The catch is there are 270 different conditions I want to apply. Eg.
1) Brad, Secretary
2) Steve, Officer

etc...

So how can I set it so the position (eg. Secretary, Officer), will autofill in the correct column/row whenever I type in the persons name?

Any help is much appreciated!

 

[whudson1980]

OK, so this is my first time actually answering a question, so please bear with me. . . Time to give back to the community right?

So, here's what I would do, and of course this is just my opinion. Since you have 270 possible situations, I would do this using VBA.

Firstly, I would have a another sheet ( hidden if you choose ) with all 270 possibilities. In my example, this other sheet we'll call sheet3, and Column A has the names, and Column B has the positions.

Then, the VBA code within the Sheet2 should look like this:

<code class="" style="box-sizing: border-box; font-family: Menlo, Monaco, Consolas, "Courier New", monospace; font-size: inherit; padding: 0px; color: inherit; background-color: transparent; white-space: pre-wrap; border-radius: 0px;">Private Sub Worksheet_Change(ByVal Target As Range)
Dim entry as Range
Dim employee as Range

For each entry in Sheets("Sheet2").Range("C:C")
     for each employee in </code>Sheets("Sheet3").Range("A:A")
          if entry = employee then
               Sheets("Sheet2").Cells(entry.row, 4).Value = Sheets("Sheet3").Cells(employee.row, 2)
          end if
     next employee
next entry

 

[FormR]

Hi, sounds like a VLOOKUP() to me - i.e.

Excel 2013/2016
	C	D
4	Brad	Secetary
5	Rob	Tea Boy
Sheet2

Cell Formulas
Range	Formula
D4	=VLOOKUP(C4,Sheet1!B:F,5,0)

Excel 2013/2016
	B	C	D	E	F
2	Name				Job
3	Brad				Secetary
4	Steve				Officer
5	Rob				Tea Boy
Sheet1

 

[L. Howard]

Try this in the sheet2 module.

A change event macro set to run when a cell is changed on sheet 2 in the cells range A2:K50.

Howard

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("$A$2:$K$50")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub

Dim nMe As Range
Dim myFnd As String

myFnd = Target.Value
Application.EnableEvents = False

Set nMe = Sheets("Sheet1").UsedRange.Find(What:=myFnd, _
                                               LookIn:=xlValues, _
                                               LookAt:=xlWhole, _
                                               SearchOrder:=xlByRows, _
                                               SearchDirection:=xlNext, _
                                               MatchCase:=False)
                                               
If Not nMe Is Nothing Then

    'MsgBox "Match for " & myFnd & " in cell " & nMe.Address
    Target.Offset(, 1) = nMe.Offset(, 1)
    Application.EnableEvents = True
    Exit Sub
    
    Else
    
    MsgBox "No match found. "
    Target.Select
    
End If

Application.EnableEvents = True
End Sub

 

[BWMagee]

Thanks very much for all the quick responses. They had their errors and will reply to each one, but have stumbled upon an answer via the combination of INDEX and MATCH functions.

=INDEX (column to return a value from, MATCH (lookup value, column to lookup against, 0))

therefore

=INDEX(Sheet1!F1:F270,MATCH(Sheet2!C1:C270,Sheet1!B1:B270,0))

OK, so this is my first time actually answering a question, so please bear with me. . . Time to give back to the community right?

So, here's what I would do, and of course this is just my opinion. Since you have 270 possible situations, I would do this using VBA.

Firstly, I would have a another sheet ( hidden if you choose ) with all 270 possibilities. In my example, this other sheet we'll call sheet3, and Column A has the names, and Column B has the positions.

Then, the VBA code within the Sheet2 should look like this:

<code class="" style="box-sizing: border-box; font-family: Menlo, Monaco, Consolas, "Courier New", monospace; font-size: inherit; padding: 0px; color: inherit; background-color: transparent; white-space: pre-wrap; border-radius: 0px;">Private Sub Worksheet_Change(ByVal Target As Range)
Dim entry as Range
Dim employee as Range

For each entry in Sheets("Sheet2").Range("C:C")
     for each employee in </code>Sheets("Sheet3").Range("A:A")
          if entry = employee then
               Sheets("Sheet2").Cells(entry.row, 4).Value = Sheets("Sheet3").Cells(employee.row, 2)
          end if
     next employee
next entry

Unfortunately I got an error message 'subscript out of range'. I have no experience with VBA, so its a bit like reading French for me. I took up the idea of putting all 270 possibilities on a second sheet however - good idea!

Hi, sounds like a VLOOKUP() to me - i.e.

Excel 2013/2016

	C	D
Brad
Rob	Tea Boy

<tbody>
[TD="align: center"]4[/TD]

[TD="bgcolor: #FFFF00"]Secetary[/TD]

[TD="align: center"]5[/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]D4[/TH]
[TD="align: left"]=VLOOKUP(C4,Sheet1!B:F,5,0)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Excel 2013/2016

	B	C	D	E	F
Name	Job
Brad	Secetary
Steve	Officer
Rob	Tea Boy

<tbody>
[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; line-height: 16.0px; font: 13.0px Verdana; color: #333333 ; -webkit-text-stroke: #333333 ; background-color: #ffffff}span.s1 {font-kerning: none}</style>Unfortunately that will only work if Brad was in C4. Not sure if I made it clear, but Brad could be anywhere from C1 to C270. As could all the other people. So I can’t do a formula specific to C4 or Brad.

Try this in the sheet2 module.

A change event macro set to run when a cell is changed on sheet 2 in the cells range A2:K50.

Howard

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("$A$2:$K$50")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub

Dim nMe As Range
Dim myFnd As String

myFnd = Target.Value
Application.EnableEvents = False

Set nMe = Sheets("Sheet1").UsedRange.Find(What:=myFnd, _
                                               LookIn:=xlValues, _
                                               LookAt:=xlWhole, _
                                               SearchOrder:=xlByRows, _
                                               SearchDirection:=xlNext, _
                                               MatchCase:=False)
                                               
If Not nMe Is Nothing Then

    'MsgBox "Match for " & myFnd & " in cell " & nMe.Address
    Target.Offset(, 1) = nMe.Offset(, 1)
    Application.EnableEvents = True
    Exit Sub
    
    Else
    
    MsgBox "No match found. "
    Target.Select
    
End If

Application.EnableEvents = True
End Sub

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; line-height: 16.0px; font: 13.0px Verdana; color: #333333 ; -webkit-text-stroke: #333333 ; background-color: #ffffff}span.s1 {font-kerning: none}</style>Same error as above, subscript out of range :/

 

[FormR]

that will only work if Brad was in C4. Not sure if I made it clear, but Brad could be anywhere from C1 to C270. As could all the other people.

Hi, no - it does not rely on "Brad" being in C4 - it is entirely equivalent to the INDEX(MATCH(.. option that you have stumbled across.

EDIT:

Although, the red section below should refer to a single cell.

=INDEX(Sheet1!F1:F270,MATCH(Sheet2!C1:C270,Sheet1!B1:B270,0))

 

[L. Howard]

If your sheet name is NOT Sheet1 then change this to your name for the sheet with 270 different conditions...

Set nMe = Sheets("Sheet1").UsedRange.Find(What:=myFnd, _

And the code goes in the sheet module that you refer to as Sheet2, the one where you want to type in the name and get the result in the adjacent cell. ("Sheet2" name is not important to the code.)

Howard

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; line-height: 16.0px; font: 13.0px Verdana; color: #333333 ; -webkit-text-stroke: #333333 ; background-color: #ffffff}span.s1 {font-kerning: none}</style>
Same error as above, subscript out of range :/




Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("$A$2:$K$50")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub

Dim nMe As Range
Dim myFnd As String

myFnd = Target.Value
Application.EnableEvents = False

Set nMe = Sheets("Sheet1").UsedRange.Find(What:=myFnd, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not nMe Is Nothing Then

'MsgBox "Match for " & myFnd & " in cell " & nMe.Address
Target.Offset(, 1) = nMe.Offset(, 1)
Application.EnableEvents = True
Exit Sub

Else

MsgBox "No match found. "
Target.Select

End If

Application.EnableEvents = True
End Sub