Consolidate Cells Containing Text From Multiple Worksheets

[andyporter1975]

Hi,

I need a solution to the following scenario please.

I have 2 worksheets which contains different data around a consistent template. I need to be able to automatically combine these worksheets so that they create a Summary sheet. Examples of what I want to try and achieve is below:

Worksheet 1

	A	B	C	D
1	Registrations	Q1	Q2	Q3	Q4
2	AB65 123	Yes	No	No	Yes
3	AB65 124	No	Yes	Yes	No
4	AB65 125
5	AB65 126

<tbody>
</tbody>

Worksheet 2

	A	B	C	D
1	Registrations	Q1	Q2	Q3	Q4
2	AB65 123
3	AB65 124
4	AB65 125	No	No	No	No
5	AB65 126	Yes	Yes	Yes	Yes

<tbody>
</tbody>

Summary (a combination of worksheets 1 and 2)

	A	B	C	D	E
1	Registrations	Q1	Q2	Q3	Q4
2	AB65 123	Yes	No	No	Yes
3	AB65 124	No	Yes	Yes	No
4	AB65 125	No	No	No	No
5	AB65 126	Yes	Yes	Yes	Yes

<tbody>
</tbody>

Any help will be greatly appreciated.

 

[SQUIDD]

Hi

On your 3rd sheet could you not enter formula?

=Sheet1!E7&Sheet2!E7 for example or if data is in both, then a different formula.

or do you want to do it with code



Dave

 

[andyporter1975]

That works perfectly, thank you.

As there are likely to be in excess of 20 worksheets, is there a way of amending the formula so that it doesn't refer to specific worksheets? For example, if I bookended the worksheets containing the data with Worksheet A and Worksheet Z could the formula reference all worksheets inbetween A and Z???

 

[SQUIDD]

Andy

Regarding what i posted above, does this mean that there is only ever data in that particular cell on 1 sheet.

Dave

 

[andyporter1975]

Yes, that's correct.

 

[SQUIDD]

i think we maybe better doing this with a UDF.

i will be back in a second

Dave

 

[SQUIDD]

how many sheets do you have

 

[andyporter1975]

27

 

[andyporter1975]

Forgot to say, the reason for asking for this amendment is that the worksheets have random names.

 

[SQUIDD]

oh right, gotcha

Makes it a bit more confusing for me.
but if you drop me your sheet names and the 1st cell you want formula in, i can write your formula quickly, then you can copy it down and over.

Im really not sure if a UDF would work, i see Rick Rothstein is on tonight and he is fantastic, he has helped me loads.

Dave
[h=1][/h]
Dave