generating named data based on values

[abz2786]

Hi Guys

I am creating a master spreadsheet and need some formula help.

I am trying to pull through names of employees based on if they have a value against a project

For example

A,B,C are three projects on three seperate tabs. Person D,E,F are employees. Person D and F have logged time against Project A.

On the master sheet i have a created a column of projects (A,B,C) and want to pull through the names (D,E,F) if they have any values against the project below the relevant project. How do i do this?

Thanks

 

[DanteAmor]

Hi Guys

I am creating a master spreadsheet and need some formula help.

I am trying to pull through names of employees based on if they have a value against a project

For example

A,B,C are three projects on three seperate tabs. Person D,E,F are employees. Person D and F have logged time against Project A.

On the master sheet i have a created a column of projects (A,B,C) and want to pull through the names (D,E,F) if they have any values against the project below the relevant project. How do i do this?

Thanks

You could explain everything with real examples.

You could upload a copy of your file or images files to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[abz2786]

Link is attached below

https://www.dropbox.com/s/bmucjyqs6csl0mz/Book1.xlsx?dl=0

 

[DanteAmor]

Link is attached below

<b>BBC</b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:60.83px;" /><col style="width:142.57px;" /><col style="width:43.72px;" /><col style="width:68.44px;" /><col style="width:74.14px;" /><col style="width:155.88px;" /><col style="width:108.36px;" /><col style="width:87.45px;" /><col style="width:88.4px;" /><col style="width:73.19px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-weight:bold; font-size:12pt; "> </td><td style="font-weight:bold; font-size:12pt; text-align:right; ">Account Handlers</td><td style="font-weight:bold; font-size:12pt; text-align:center; ">Adam</td><td style="font-weight:bold; font-size:12pt; text-align:center; ">Alexa</td><td style="font-weight:bold; font-size:12pt; text-align:center; ">Amy S</td><td style="font-weight:bold; font-size:12pt; text-align:center; ">Annemarie</td><td style="font-weight:bold; font-size:12pt; text-align:center; ">Catherine</td><td style="font-weight:bold; font-size:12pt; text-align:center; ">Charlie F</td><td style="font-weight:bold; font-size:12pt; text-align:center; ">Charlie G</td><td style="font-weight:bold; font-size:12pt; text-align:center; ">Chris</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >BB01</td><td >Account Management</td><td > </td><td > </td><td style="text-align:right; ">2</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >BB02</td><td >Client Meeting</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">1.25</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >BB03</td><td >Client Briefing </td><td style="text-align:right; ">1</td><td > </td><td style="text-align:right; ">1</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >BB04</td><td >Client Planning</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >BB05</td><td >Design</td><td > </td><td > </td><td style="text-align:right; ">0.5</td><td > </td><td > </td><td > </td><td style="text-align:right; ">3</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >BB06</td><td >Reporting</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr></table>

From the previous data what do you want to put on the sheet1 and how do you want to put them.
Missing examples of what you expect as a result.

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:60.83px;" /><col style="width:60.83px;" /><col style="width:60.83px;" /><col style="width:60.83px;" /><col style="width:60.83px;" /><col style="width:137.82px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td></tr><tr style="height:34px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-family:Arial; font-size:10pt; ">BBC</td><td style="font-family:Arial; font-size:10pt; ">ITV</td><td style="font-family:Arial; font-size:10pt; ">Nintendo</td><td style="font-family:Arial; font-size:10pt; ">Sega</td><td style="font-family:Arial; font-size:10pt; ">Sony</td><td style="font-family:Arial; font-size:10pt; ">Warner Bros</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr></table>

 

[abz2786]

BBC

	A	B	C	D	E	F	G	H	I	J
1		Account Handlers	Adam	Alexa	Amy S	Annemarie	Catherine	Charlie F	Charlie G	Chris
2
3	BB01	Account Management			2
4	BB02	Client Meeting								1.25
5	BB03	Client Briefing	1		1
6	BB04	Client Planning
7	BB05	Design			0.5				3
8	BB06	Reporting

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:60.83px;"><col style="width:142.57px;"><col style="width:43.72px;"><col style="width:68.44px;"><col style="width:74.14px;"><col style="width:155.88px;"><col style="width:108.36px;"><col style="width:87.45px;"><col style="width:88.4px;"><col style="width:73.19px;"></colgroup><tbody>
</tbody>

From the previous data what do you want to put on the sheet1 and how do you want to put them.
Missing examples of what you expect as a result.

Sheet1

	A	B	C	D	E	F
1	BBC	ITV	Nintendo	Sega	Sony	Warner Bros
2

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:60.83px;"><col style="width:60.83px;"><col style="width:60.83px;"><col style="width:60.83px;"><col style="width:60.83px;"><col style="width:137.82px;"></colgroup><tbody>
</tbody>

So from the data above you can see Adam, Amy, Chris and Charlie G have logged time under BBC. So on the front sheet i would like all of their names to appear under BBC.

 

[DanteAmor]

So from the data above you can see Adam, Amy, Chris and Charlie G have logged time under BBC. So on the front sheet i would like all of their names to appear under BBC.

Use this

Sub generating_named_data()
    Dim sh1 As Worksheet, sh As Worksheet
    Dim i As Long, exists As Boolean, j As Long, lr As Long, wSum As Double
    
    Set sh1 = Sheets("Sheet1")
    sh1.Rows("2:" & Rows.Count).ClearContents
    For i = 1 To sh1.Cells(1, Columns.Count).End(xlToLeft).Column
        wname = sh1.Cells(1, i).Value
        exists = False
        For Each sh In Sheets
            If LCase(sh.Name) = LCase(wname) Then
                For j = 3 To sh.Cells(1, Columns.Count).End(xlToLeft).Column
                    lr = sh.Cells(Rows.Count, j).End(xlUp).Row
                    If lr < 3 Then lr = 3
                    wSum = WorksheetFunction.Sum(sh.Range(sh.Cells(3, j), sh.Cells(lr, j)))
                    If wSum <> 0 Then
                        sh1.Cells(Rows.Count, i).End(xlUp)(2).Value = sh.Cells(1, j)
                    End If
                Next
                Exit For
            End If
        Next
    Next
    MsgBox "End"
End Sub

the example below

https://www.dropbox.com/s/ze9o6qp3y1pfq8k/Book1 dam.xlsm?dl=0

 

[abz2786]

Use this

Sub generating_named_data()
    Dim sh1 As Worksheet, sh As Worksheet
    Dim i As Long, exists As Boolean, j As Long, lr As Long, wSum As Double
    
    Set sh1 = Sheets("Sheet1")
    sh1.Rows("2:" & Rows.Count).ClearContents
    For i = 1 To sh1.Cells(1, Columns.Count).End(xlToLeft).Column
        wname = sh1.Cells(1, i).Value
        exists = False
        For Each sh In Sheets
            If LCase(sh.Name) = LCase(wname) Then
                For j = 3 To sh.Cells(1, Columns.Count).End(xlToLeft).Column
                    lr = sh.Cells(Rows.Count, j).End(xlUp).Row
                    If lr < 3 Then lr = 3
                    wSum = WorksheetFunction.Sum(sh.Range(sh.Cells(3, j), sh.Cells(lr, j)))
                    If wSum <> 0 Then
                        sh1.Cells(Rows.Count, i).End(xlUp)(2).Value = sh.Cells(1, j)
                    End If
                Next
                Exit For
            End If
        Next
    Next
    MsgBox "End"
End Sub

the example below

https://www.dropbox.com/s/ze9o6qp3y1pfq8k/Book1 dam.xlsm?dl=0

Thank you for this.

Would you be able to apply this macro to the below link under the overview tab as this is the actual sheet i will be using.

https://www.dropbox.com/s/5v27n5iqq8uvq5x/Capacity Plans.xls?dl=0

Thanks

 

[DanteAmor]

Thank you for this.

Would you be able to apply this macro to the below link under the overview tab as this is the actual sheet i will be using.

https://www.dropbox.com/s/5v27n5iqq8uvq5x/Capacity Plans.xls?dl=0

Thanks

If the actual sheet is like the sheet you sent then it does work.


Remember what I asked you:

You could explain everything with real examples.

 

[abz2786]

If the actual sheet is like the sheet you sent then it does work.


Remember what I asked you:

the only thing that differs is the name of the tabs, client names. Which lines on the macro need to be updated to reflect this?

 

[DanteAmor]

the only thing that differs is the name of the tabs, client names. Which lines on the macro need to be updated to reflect this?

Only the name of Sheet1 changes

Set sh1 = Sheets("[COLOR=#ff0000]Sheet1[/COLOR]")

If everything else is like the sheet you sent me, the macro should not have problems.
Take a test