# Copy data from one sheet to another - with merged cells

#### mutrus

##### Board Regular
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
ABCDEFGHIJKL
11234567891011
2JohnSickCourse
3MaryCourse
4Richard
5
6BlakeSick
7Ewen
8
9Trevor
Sheet1

This table shows how I want the result range to look
Book1
NOPQRSTUVWXY
121234567891011
13John00010000111
14Mary11000111111
15Richard11111111111
16
17Blake11111100000
18Ewen11111111111
19
20Trevor11111111111
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

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
shouldn't be too difficult...

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

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.

Replies
4
Views
138
Replies
14
Views
737
Replies
11
Views
352
Replies
1
Views
535
Replies
5
Views
163

1,219,959
Messages
6,151,153
Members
451,011
Latest member
Pigdog89

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back