Concatenate based on contents of six different cell values

[Legacy 175602]

I'm working on a multi sheet spreadsheet in Excel 2003 and I'm having trouble working out the right formula to use. Here's what I'm trying to do:
- enter text (multiple lines of text) in cell A3 in 6 individual sheets (named 2B.1, 2B.2, 2D, JEFMS, MEMS & DLSP);
- on the first sheet (named Matrix) I want to type 1 or 0 or something similar to indicate true or false (currently using 1 and 0 and conditional formatting to change colours to red or green) in cells !Matrix#D5-I5 through to !Matrix#D48-I48 (I'm ok up until this point..)
- now I want to have a formula in the KeyMessages column !Matrix#M5 through to !Matrix#M48 which will look at the 1s and 0s and for every 1 it will copy the text in A3 of each of other six sheets (2B.1, 2B.2 etc) and concatenate them into the one cell M5 through to M48).

Any advice you could give me would be greatly appreciated.

 

[jbeaucaire]

EXCEL JEANIE
Use Excel Jeanie to post up some visible examples of the data you're referring to, and perhaps a mockup of the desired results you have in mind.

Excel Workbook
	A	B	C	D
1	Number	Title	Name	Many Data Filed
2	111111	Mr	Ashman	Address 1
3	111111	Mr	Ashman	Address 2
4	111111	Mr	Ashman	Address 3
5	111111	Mr	Ashman	Address 4
6	222222	Mrs	Smith	Address 1
7	222222	Mrs	Smith	Address 2
8	222222	Mrs	Smith	Address 3
9	222222	Mrs	Smith	Address 4
10	222222	Mrs	Smith	Address 5
11	333333	Mr	Jones	Address 1
12	333333	Mr	Jones	Address 2
13	444444	Miss	Smith	Address 1
14	444444	Miss	Smith	Address 2
Example