Pull data from multiple sheets

Hey all,

Just found this forum and it looks super useful. Apologies if this is in the wrong thread - just had a quick question.

I'm working on a sheet trying to keep track on which client is working with which employee. The first sheet shows tracking for the year and each additional sheet is the month. Basically the Yearly sheet is there so I can quickly control+f "client name" and see which employee he or she is working with.

I attached a simple version of the workbook but basically it basically looks like this: Yearly sheet - Row 1 is the employees name, Row 2 is the month, and the rows after that are the clients name until it gets to the next month. January sheet - cell A1 says client, Cell B1 says employee. I'm inputing the clients in column A and the employee in column B and using this formula for the Yearly sheet (starting in row 3) =IF(January!$B2=Yearly!A$1,January!$A2,"")

This is working for what it's supposed to do, but it's not pretty and leaves a lot of of blank cells. Is there a simpler way of doing this?

YearlyClientsEmployees.xlsx

Hi and welcome to the forum

Please take a moment to read the forum rules regarding giving your thread a title that is more descriptive of your problem (think google search terms?). I have changed it for you - this time

My 1st question to you would be - why the need to have each month entered into a separate sheet? If you enter all data onto 1 sheet, then you can easily do extracts to other sheets if needed

Try this Array Formula. Since this is an an array formula so you need to confirm it with Ctrl+Shift+Enter instead of just Enter. (i.e. after placing the formula in the cell, hold down the Ctrl+Shift and then press Enter.)

In A3

=IFERROR(INDEX(January!$A$2:$A$8,SMALL(IF(January!$B$2:$B$8=A$1,ROW(January!$A$2:$A$8)-ROW(January!$A$2)+1),ROWS(A$3:A3))),"")

then drag across and down.

pulling in month from Row 2

=IFERROR(INDEX(INDIRECT(A$2&"!$A$1:$A$100"),(SMALL(IF(INDIRECT(A$2&"!$b$1:$b$100")=A$1,ROW($B$1:$B$100)),ROWS($D$1:D1)))),"")