Refer Data from Other Sheets

One of the employee came to me with a file which has 96 sheets and basically it was all payroll data, he wanted a summary sheet with all the employee names and the month over month tax deductions.

 

I didn’t want to write a macro…..after a small thought here is the solutions

 

a)      Build Index of Sheets

=IFERROR(INDEX(MID(Sheets,FIND("]",Sheets)+1,255),ROW(A1),1),"")

 

b)      Refer the values from different sheets

=indirect( " ' "&Sheet Name&" '!"&Cell Name)

 

In one go all the data employee wise month wise is right in front of me…..