More efficient way to lookup and concatenate multiple cells from multiple sheets

[torsades]

I am planning a roster across a 12 month calendar, with "days" split into clusters of cells and months in different sheets.

I need to check each "day" if "leave" is indicated.

If it is, I need to collate the values of the cells in that "day", and output it in a specified format.

I need to do this for every day in each "month" and finally collate all the values from all the "months" (sheets) into a final output cell for the year.

What I've done is create a formula to check for this for each day, copied across the cells it for all the days, then concatenate it for each week and month, copy the same formulas into each month, and finally concatenate it for the year.

Is there a more efficient way to accomplish the same result?

Below is a sample of a week's roster (there are 4-5 weeks in each month, the format is similar).

Thanks for any opinions or suggestions.

1

*

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

*

*

*

*

*

*

25 Jan Wednesday AM: AM roster 25 Jan Wednesday PM: PM roster

*

*

*

*

*

*

*

29 Jan Sunday AM: AM roster 29 Jan Sunday PM: PM roster

*

*

*

*

25 Jan Wednesday AM: AM roster 25 Jan Wednesday PM: PM roster 29 Jan Sunday AM: AM roster 29 Jan Sunday PM: PM roster

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

<tbody>
[TD="bgcolor: #cacaca, align: center"]27[/TD]
[TD="align: center"]25[/TD]
[TD="align: left"]Leave[/TD]
[TD="align: center"]26[/TD]

[TD="align: center"]27[/TD]

[TD="align: center"]28[/TD]

[TD="align: center"]29[/TD]
[TD="align: left"]Leave[/TD]
[TD="align: center"]30[/TD]

[TD="align: center"]31[/TD]

[TD="bgcolor: #cacaca, align: center"]28[/TD]
[TD="colspan: 2"]*[/TD]
[TD="colspan: 2"]*[/TD]
[TD="colspan: 2"]*[/TD]
[TD="colspan: 2"]*[/TD]
[TD="colspan: 2"]*[/TD]
[TD="colspan: 2"]*[/TD]
[TD="colspan: 2"]*[/TD]

[TD="bgcolor: #cacaca, align: center"]29[/TD]
[TD="colspan: 2, align: center"]AM roster[/TD]
[TD="colspan: 2, align: center"]AM roster[/TD]
[TD="colspan: 2, align: center"]AM roster[/TD]
[TD="colspan: 2, align: center"]AM roster[/TD]
[TD="colspan: 2, align: center"]AM roster[/TD]
[TD="colspan: 2"]*[/TD]
[TD="colspan: 2"]*[/TD]

[TD="bgcolor: #cacaca, align: center"]30[/TD]
[TD="colspan: 2, align: center"]PM roster[/TD]
[TD="colspan: 2, align: center"]PM roster[/TD]
[TD="colspan: 2, align: center"]PM roster[/TD]
[TD="colspan: 2, align: center"]PM roster[/TD]
[TD="colspan: 2, align: center"]PM roster[/TD]
[TD="colspan: 2"]*[/TD]
[TD="colspan: 2"]*[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula P27 =IF(ISNUMBER(SEARCH("leave",B27)),CONCATENATE(TEXT(A27,"d mmm [$-4809]dddd "),"AM: ",A29,CHAR(10),TEXT(A27,"d mmm [$-4809]dddd "),"PM: ",A30,CHAR(10)),"") R27 =IF(ISNUMBER(SEARCH("leave",D27)),CONCATENATE(TEXT(C27,"d mmm [$-4809]dddd "),"AM: ",C29,CHAR(10),TEXT(C27,"d mmm [$-4809]dddd "),"PM: ",C30,CHAR(10)),"") T27 =IF(ISNUMBER(SEARCH("leave",F27)),CONCATENATE(TEXT(E27,"d mmm [$-4809]dddd "),"AM: ",E29,CHAR(10),TEXT(E27,"d mmm [$-4809]dddd "),"PM: ",E30,CHAR(10)),"") V27 =IF(ISNUMBER(SEARCH("leave",H27)),CONCATENATE(TEXT(G27,"d mmm [$-4809]dddd "),"AM: ",G29,CHAR(10),TEXT(G27,"d mmm [$-4809]dddd "),"PM: ",G30,CHAR(10)),"") X27 =IF(ISNUMBER(SEARCH("leave",J27)),CONCATENATE(TEXT(I27,"d mmm [$-4809]dddd "),"AM: ",I29,CHAR(10),TEXT(I27,"d mmm [$-4809]dddd "),"PM: ",I30,CHAR(10)),"") Z27 =IF(ISNUMBER(SEARCH("leave",L27)),CONCATENATE(TEXT(K27,"d mmm [$-4809]dddd "),"AM: ",K29,CHAR(10),TEXT(K27,"d mmm [$-4809]dddd "),"PM: ",K30,CHAR(10)),"") AB27 =IF(ISNUMBER(SEARCH("leave",N27)),CONCATENATE(TEXT(M27,"d mmm [$-4809]dddd "),"AM: ",M29,CHAR(10),TEXT(M27,"d mmm [$-4809]dddd "),"PM: ",M30,CHAR(10)),"") AC27 =CONCATENATE(P27,R27,T27,V27,X27) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4