Index across multiple sheets returning multiple values

[Joak]

Index across multiple sheets returning multiple values
Hi all,

I am building a payroll workbook and I need to build a sheet that will allow me to choose an employee name and the formula will return all the data related to this employee. I found this forumla to do this:
=IF(ROWS(...)<=...;INDEX(INDIRECT(...);SMALL(IF(...=...;ROW(...)-ROW(...)+1);ROWS(...)));"")

But in my workbook, the data is on multiple sheets . How do I adapt this formula to look into 12 sheets?

Hopefully, that data would be returned in order of the date of the pay.

Thanks for your help

file: Test formula on payroll_v3.xlsx


ps: the sheet names are in french on my computer. sorry if it make some confusion.

[Joak]

the file: Test formula on payroll_v3.xlsx
Thanks!

[SDCh]

Hi, Joak

From what i see your formula work well on my excel with little change on sheet name.

Check what result on this formula

Please Login or Register  to view this content.

then change your sheet name exactly with that result.

On my excel "08-02-2014" the result is "Feb", so I will change all tab with "Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec" for 3 digit month.

Or may be I miss something what exactly what you want?

[Joak]

Hi SDCh,

Thanks for your help.

The formula in "payslip" sheet works find, thans to the help of hemesh on this forum.

The formula im looking to develop now is on the sheet "Total_employee".
It would look for an employee name across all the sheets (jan, feb and march) and give all the data related to this employee.

[Joak]

I finally found a way around.
Each employee have no more then 6 rows per month. In my total_employee sheet, I entered the formula for each of the 12 sheets separately. Each sub-table as 6 rows. The total table as 12x6 rows. The result is okay. It return the good data, but with a lot of empty rows. I then applied a macro hiding the empty rows. It will do the job.

But I still looking for a formula that will allow doing INDEX across multiple sheets and returning multiple values. If it is possible. Thank you for your help. See ya.