Data fill based on values

[Mr Seeker]

Hi

I need a macro/formula to pull through names of people if they have a value against a client onto the master spreadsheet

I have separate client tabs, with people putting values against an activity. I have a master sheet which lists the clients under each column. I would like to pull through the names of people if they have any values against any of these clients.

 

[Joe4]

Welcome to the Board!

You haven't provided a lot of detail, but I think you may be able to do what you want either a VLOOKUP formula.
See the "How to vlookup from another sheet" section here: https://www.ablebits.com/office-addins-blog/2014/07/17/excel-vlookup-tutorial-beginner/
If you are unfamiliar with VLOOKUP, check out the "Excel VLOOKUP syntax" at the top of that link.

 

[Mr Seeker]

Welcome to the Board!

You haven't provided a lot of detail, but I think you may be able to do what you want either a VLOOKUP formula.
See the "How to vlookup from another sheet" section here: https://www.ablebits.com/office-addins-blog/2014/07/17/excel-vlookup-tutorial-beginner/
If you are unfamiliar with VLOOKUP, check out the "Excel VLOOKUP syntax" at the top of that link.

Don't think VLOOKUP will work this as it needs to search the whole sheet and pull through names of people who have input a value under the client

I have attached a link which will hopefully make more sense.

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

Hope it makes a little more sense now

 

[Joe4]

I have attached a link which will hopefully make more sense.

https://www.dropbox.com/s/s7j11pjqly...Plans.xls?dl=0

Unfortunately, I do not have the ability to view/download those files from my current location. My workplace security blocks all those sites (many other people are also unable or unwilling to download files from the internet due to security reasons). Hopefully, someone who can download the file will take a look at it.

Note that while you cannot upload files to this site, there are tools you can use to post screen images. They are listed in Section B of this link here: http://www.mrexcel.com/forum/board-a...forum-use.html. Also, there is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.

If the issue is that you may need to return multiple records, you may want to look at using Filters.

 

[Fluff]

You could try in A2 filled down & across
=IFERROR(INDEX(INDIRECT("'"&A$1&"'!$C$4:$AF$4"),AGGREGATE(15,6,(COLUMN(INDIRECT("'"&A$1&"'!$C$7:$AF$7"))-COLUMN(INDIRECT("'"&A$1&"'!$C$7"))+1)/(INDIRECT("'"&A$1&"'!$C$7:$AF$32")>0),ROWS($A$1:$A1))),"")

Although it will show each persons name for as many entries as they have on the relevant sheet

 

[Fluff]

Another option with amacro

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
   Dim Ws As Worksheet
   Dim Nme As String
   Dim Fnd As Range
   
   If Sh.Name = "Overview" Then Exit Sub
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Sh.Range("C7:AF32")) Is Nothing Then
      Set Ws = Sheets("Overview")
      Set Fnd = Ws.Range("1:1").Find(Sh.Name, , , xlWhole, , , False, , False)
      Nme = Sh.Cells(4, Target.Column)
      If Target <> "" Then
         If Application.CountIf(Fnd.EntireColumn, Nme) = 0 Then Ws.Cells(Rows.Count, Fnd.Column).End(xlUp).Offset(1).Value = Nme
      ElseIf Application.CountA(Sh.Range(Sh.Cells(7, Target.Column), Sh.Cells(32, Target.Column))) = 0 Then
         Set Fnd = Fnd.EntireColumn.Find(Nme, , , xlWhole, , , False, , False)
         Fnd.Delete xlUp
      End If
   End If
End Sub

This needs to go in the ThisWorkbook module