Copy data from one sheet to another - with merged cells

[mutrus]

Hi,

I wish to be able to read data from a range of cells on one sheet and use this data to update a range of cells on another sheet

This table shows the range to copy from:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1		1	2	3	4	5	6	7	8	9	10	11
2	John	Sick				Course
3	Mary			Course
4	Richard
5
6	Blake							Sick
7	Ewen
8
9	Trevor
Sheet1

This table shows how I want the result range to look

Book1
	N	O	P	Q	R	S	T	U	V	W	X	Y
12		1	2	3	4	5	6	7	8	9	10	11
13	John	0	0	0	1	0	0	0	0	1	1	1
14	Mary	1	1	0	0	0	1	1	1	1	1	1
15	Richard	1	1	1	1	1	1	1	1	1	1	1
16
17	Blake	1	1	1	1	1	1	0	0	0	0	0
18	Ewen	1	1	1	1	1	1	1	1	1	1	1
19
20	Trevor	1	1	1	1	1	1	1	1	1	1	1
Sheet1

To Explain: The source range is on a different sheet than the destination range. The first column from the source is already duplicated in the destination table however the destination range does not equal the source range (e.g John is in A2 on source and N13 on destination.

I need to be able to read each cell from the source, if the cell is empty then the corresonding cell on the destination needs to be updated to a '1'. If the source cell is a merged cell, then the cells in the destination need to updated to show a '0' for each cell that was included in the original merged cell

Hope this is not too confusing

Thanks

 

[DonkeyOte]

shouldn't be too difficult...

O13 = if(sheet1!b2<>"",1,0)
etc...

 

[mutrus]

Sorry not really, I will expand the problem a bit more. There could be up to 31 columns and up to 100 rows to iterate through. The values of each source cell that I want to pass to the target cell is the source cells color and there are up to 20 different colours which may be used. So if there is a red cell in the source then the corresponding target cell will contain a 1, if blue then 0, if yellow then 1, if red then 0 on so on.