Concatenate Function

[Am5687]

I am trying to pull data from individual employee sheets in a workbook. I am trying to concatenate the sheets and match them to the employee name and input the data from the same 4 cells on their worksheet into a table on another worksheet. If I use the IF function I will have to do that formula each time the list changes so I am trying to automate that. I also need to do this for a date in four other cells.

I have tried

=CONCATENATE(associate,IF('Ringo Starr'!$B$21=4,"B",IF('Ringo Starr'!$D$21=4,"S",IF('Ringo Starr'!$F$21=4,"G",IF('Ringo Starr'!$H$21="P","")))))

Thanks for any help.

 

[ravishankar]

Re: Concatenate Function help

try the following macro codes

Sub join()
Dim a As Long
Sheets.Add.Name = "summary"
For a = 1 To Sheets.Count
Sheets("summary").Cells(a, 1) = Sheets(a).Name
Sheets("summary").Cells(a, 2) = Sheets(a).Cells(21, 2) & " " & Sheets(a).Cells(21, 4) & " " & Sheets(a).Cells(21, 6)
Next a
MsgBox "complete"
End Sub

i am not clear on your conditional clauses.
ravishankar

 

[Am5687]

What I have is a cover sheet where all the tabs are indexed. Each employee has their own tab with the machines they are certified on. There are 7 machines and 4 levels of certification starting at B21 to H27. I also have a display page with a list of the employees name that I take from the index on page 1. What I am looking for is a formula that will match the name in column A to the index and pull the data from each employee page and put it in a column on the display page. Right now I can do that with the if function. The problem is I need to do it individually and if we let an employee go I will need to manually change everything. So in this example =if(‘Ringo Starr’b$B$21=4,”B”) instead of Ringo Starr I would like something that would match the name in column A to the employee sheet that could be copied down the list 150 times and reference each employees sheet.