Array formula across Multiple Worksheets

[shaka]

Hi folks stuck on this formula


I have this array formula that returns a cell value from another worksheet named (1)


IF(ROWS($C$7:C7)>$B$6,"",INDEX('1'!$H$11:$AO$11,SMALL(IF('1'!$H$11:$AO$11='1'!$K$4,COLUMN('1'!$H$11:$AO$11)-COLUMN('1'!$H$11)+3),ROWS($C$7:C7))))


now this works ok but I have 54 sheets with the exact same layout and was wondering if it was possible to run this formula across all 54 sheets


sheets are named 1-54

 

[Hui]

Shaka


Firstly, Welcome to the Chandoo.org Forums


Can you explain what the formula is doing?


You may be better to run it as is on each worksheet and then extract the First/Last Value or Max/Min etc from that summarized data

 

[kaushik03]

Hi Shaka,


I am not very sure what exactly you are after.


According to my guess, you may find the below thread useful for you:


http://www.myonlinetraininghub.com/excel-vlookup-multiple-sheets


Kaushik

 

[shaka]

Hi sorry I should have added what I need the formula to do


I have 54 sheets each showing a weekly view of a staff roster schedule all will be the exact same layout

column B contains the employee names starting from B11

H9,M9,W9,AB9,AG9,AL9 all contain a date from the 7 days of that week

H11:AO11 contains shift information for the employee in row B


I then have another profile sheet for each employee

C7:C contains the Vacation dates taken for that year


In sheet 1 if I were to add V (vacation) to any cells in H11:AO11 the date for that days vacation would be added to column C in the employee name worksheet


here is my WIP workbook


https://www.dropbox.com/s/fnt4emyfqfx1lrs/Employee%20Stats.xlsx


thanks for taking the time to reply