generating named data based on values

A

abz2786

New Member
Joined
Jun 12, 2019
Messages
8
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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
abz2786 said:
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
Click to expand...

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.
 
Upvote 0
abz2786 said:
Link is attached below
Click to expand...

<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>
 
Upvote 0
DanteAmor said:
BBC

ABCDEFGHIJ
BB01Account Management
BB02Client Meeting
BB03Client Briefing
BB04Client Planning
BB05Design
BB06Reporting

<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>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="align: right"]Account Handlers[/TD]
[TD="align: center"]Adam[/TD]
[TD="align: center"]Alexa[/TD]
[TD="align: center"]Amy S[/TD]
[TD="align: center"]Annemarie[/TD]
[TD="align: center"]Catherine[/TD]
[TD="align: center"]Charlie F[/TD]
[TD="align: center"]Charlie G[/TD]
[TD="align: center"]Chris[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

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

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

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

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

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

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

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

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

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

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]

</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

ABCDEF
BBCITVNintendoSegaSonyWarner Bros

<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>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

</tbody>
Click to expand...




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.
 
Upvote 0
abz2786 said:
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.
Click to expand...


Use this

Code: 
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
 
Upvote 0
DanteAmor said:
Use this

Code: 
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
Click to expand...


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
 
Upvote 0
Upvote 0
abz2786 said:
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?
Click to expand...


Only the name of Sheet1 changes
Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Question Question
Formatting raw data with extra rows automatically added in-between records
Replies
1
Views
79
Peter_SSs
Peter_SSs
J
  • Question Question
pivot table pull value if name changes
Replies
1
Views
189
Kaper
Kaper
A
  • Solved
Pull data from multiple sheets based off name and month selected
Replies
2
Views
154
Amo840812
A
V
  • Solved
VBA Code or formula to replace "Employee" with the second name of a different cell.
Replies
7
Views
91
Peter_SSs
Peter_SSs
S
  • Question Question
Project Status Spreadsheet
Replies
6
Views
186
Fluff
Fluff

Forum statistics

Threads
1,224,822
Messages
6,181,164
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top