Run A Macro

[gmazza76]

Good Afternoon,

I am trying to automatically populate some cells depending on a drop down.

The drop down is in cell "B2" on Sheet1.

I am using the code below to enter the names etc... but am unsure of how to assign it to change when Cell "B2" does
I know the code looks promative but it is the only way i can think of doing it as the file is shared and i cannot use AutoFilter due to this

If "B2" = "Atiff Mazhar" Then
 
    B4 = "Carolyne Anderson"
    B5 = "Charlotte Ryalls"
    
End If

Many Thanks
Gavin

 

[CWatts]

To answer your question specifically, you can use the following worksheet change function:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B2")) Is Nothing Then
    If Range("B2").Value = "Atiff Mazhar" Then
       Range("B4").Value = "Carolyne Anderson"
       Range("B5").Value = "Charlotte Ryalls"
    End If
End If
End Sub

I'm going to guess that this is a list of managers and their employees so when you select the manager, their employees auto populate. If that is the case, you might be better off using some sort of index/match instead of VBA.

 

[gmazza76]

Thanks for the idea of Index, but do you know where i can look at for examples on how to use this

Cheers

 

[CWatts]

Example of how to use which? The macro or the index?

 

[gmazza76]

Either!

The issue i have is the manager name can be selected from a drop down box ( say B4 ) I need excel then to populate his team's name downwards from B10, b11, etc.. until all the names are showing.

I have all the area managers name, managers name and agent names stored in Sheet2 with the drop downs on sheet 1

Area Manager is chosen which then restricts it to manager options, but i am unsure how to get the agent names to appear from B10

Cheers

 

[CWatts]

Perhaps something like this:

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 95px"><COL style="WIDTH: 62px"><COL style="WIDTH: 67px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="FONT-WEIGHT: bold">Area Manager</TD><TD style="FONT-WEIGHT: bold">Manager</TD><TD style="FONT-WEIGHT: bold">Agent</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>AM1</TD><TD>M1</TD><TD>Person 1</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>AM1</TD><TD>M1</TD><TD>Person 2</TD><TD> </TD><TD>M1</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>AM1</TD><TD>M1</TD><TD>Person 3</TD><TD> </TD><TD>Person 1</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD>AM1</TD><TD>M1</TD><TD>Person 4</TD><TD> </TD><TD>Person 2</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD>AM1</TD><TD>M2</TD><TD>Person 5</TD><TD> </TD><TD>Person 3</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD>AM1</TD><TD>M2</TD><TD>Person 6</TD><TD> </TD><TD>Person 4</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD>AM1</TD><TD>M2</TD><TD>Person 7</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD>AM1</TD><TD>M2</TD><TD>Person 8</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD>AM1</TD><TD>M2</TD><TD>Person 9</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD>AM1</TD><TD>M2</TD><TD>Person 10</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD>AM1</TD><TD>M3</TD><TD>Person 11</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD>AM1</TD><TD>M3</TD><TD>Person 12</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD>AM1</TD><TD>M3</TD><TD>Person 13</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD>AM2</TD><TD>M4</TD><TD>Person 14</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD>AM2</TD><TD>M4</TD><TD>Person 15</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD>AM2</TD><TD>M4</TD><TD>Person 16</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</TD><TD>AM2</TD><TD>M6</TD><TD>Person 17</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">19</TD><TD>AM2</TD><TD>M6</TD><TD>Person 18</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">20</TD><TD>AM2</TD><TD>M6</TD><TD>Person 19</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">21</TD><TD>AM2</TD><TD>M6</TD><TD>Person 20</TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>E4</TD><TD>=INDEX($C:$C,MATCH($E$3,$B:$B,0))</TD></TR><TR><TD>E5</TD><TD>=IF(COUNTA($E$4:E4)<COUNTIF(B:B,$E$3),INDEX($C:$C,MATCH($E$3,$B:$B,0)+COUNTA($E$4:E4)),"")</TD></TR><TR><TD>E6</TD><TD>=IF(COUNTA($E$4:E5)<COUNTIF(B:B,$E$3),INDEX($C:$C,MATCH($E$3,$B:$B,0)+COUNTA($E$4:E5)),"")</TD></TR><TR><TD>E7</TD><TD>=IF(COUNTA($E$4:E6)<COUNTIF(B:B,$E$3),INDEX($C:$C,MATCH($E$3,$B:$B,0)+COUNTA($E$4:E6)),"")</TD></TR><TR><TD>E8</TD><TD>=IF(COUNTA($E$4:E7)<COUNTIF(B:B,$E$3),INDEX($C:$C,MATCH($E$3,$B:$B,0)+COUNTA($E$4:E7)),"")</TD></TR><TR><TD>E9</TD><TD>=IF(COUNTA($E$4:E8)<COUNTIF(B:B,$E$3),INDEX($C:$C,MATCH($E$3,$B:$B,0)+COUNTA($E$4:E8)),"")</TD></TR><TR><TD>E10</TD><TD>=IF(COUNTA($E$4:E9)<COUNTIF(B:B,$E$3),INDEX($C:$C,MATCH($E$3,$B:$B,0)+COUNTA($E$4:E9)),"")</TD></TR><TR><TD>E11</TD><TD>=IF(COUNTA($E$4:E10)<COUNTIF(B:B,$E$3),INDEX($C:$C,MATCH($E$3,$B:$B,0)+COUNTA($E$4:E10)),"")</TD></TR><TR><TD>E12</TD><TD>=IF(COUNTA($E$4:E11)<COUNTIF(B:B,$E$3),INDEX($C:$C,MATCH($E$3,$B:$B,0)+COUNTA($E$4:E11)),"")</TD></TR><TR><TD>E13</TD><TD>=IF(COUNTA($E$4:E12)<COUNTIF(B:B,$E$3),INDEX($C:$C,MATCH($E$3,$B:$B,0)+COUNTA($E$4:E12)),"")</TD></TR><TR><TD>E14</TD><TD>=IF(COUNTA($E$4:E13)<COUNTIF(B:B,$E$3),INDEX($C:$C,MATCH($E$3,$B:$B,0)+COUNTA($E$4:E13)),"")</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

 

[gmazza76]

Afternoon,

I am trying to use the formulas you have provided and i am having an issue. The first line works with no issues, but when i try to use the second one down

=IF(COUNTA($E$4:E4)(B:B,$E$3),INDEX($C:$C,MATCH($E$3,$B:$B,0)+COUNTA($E$4:E4)),"")

Excel is trying to put a * in as follows on the first part

=IF(COUNTA($E$4:E4)*(B:B,$E$3)

Sorry but do you have any ideas on how to fix this

Cheers
Gavin

 

[CWatts]

UGH!. I hate how this board can't differentiate that< without a > is not HTML code....even in a code box so I have to use & l t ;

=IF(COUNTA($E$4:E4<COUNTIF(B:B,$E$3),INDEX($C:$C,MATCH($E$3,$B:$B,0)+COUNTA($E$4:E4)),"")

 

[gmazza76]

Sorry, this seems to be the same as the start of the formula, which part of the formula needs changing ?

Cheers

 

[gmazza76]

Sorry,


I realise my pc didnt pull all the details through