Data fill based on values

Mr Seeker

New Member
Joined
Jun 24, 2019
Messages
9
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.
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Mr Seeker

New Member
Joined
Jun 24, 2019
Messages
9
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,071
Office Version
365
Platform
Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,590
Office Version
365
Platform
Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,590
Office Version
365
Platform
Windows
Another option with amacro
Code:
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
 

Forum statistics

Threads
1,089,432
Messages
5,408,187
Members
403,188
Latest member
Sanjana Ramesh

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top