reference multiple tabs and combine in one column

[covino]

Hi All. I have always found great help in this forum and here I am again stuck without a solution.

I have a spreadsheet where I want to have referenced to column A all names from the column A in 3 different tabs of another spreadsheet. I also need to put them in order and be able to expand the referenced column every time a new name is added to any of the other spreadsheets.

example :

people I like.xls

A | B | C
Amanda
Cassandra
joannah


People I don't like.xls

A | B | C
fernanda
kimberley
renata

names list.xls (the list I want to make)

A | B | C
Amanda
Cassandra
joannah
fernanda
kimberley
renata

So, this works that every time I add a name to any of the first 2 spreadsheets, it will update the third one. Is this possible with excel formulas? I cant figure out a way.

Any help is much appreciated.

 

[Peter_SSs]

Welcome to the MrExcel board!

Is this the sort of thing you mean?

Book1
	A
1	Amanda
2	Cassandra
3	joannah
4
Like

Book1
	A
1	fernanda
2	kimberley
3	renata
4
Dislike

This formula copied down.

Book1
	A
1	Amanda
2	Cassandra
3	joannah
4	fernanda
5	kimberley
6	renata
7
List

Cell Formulas
Range	Formula
A1	=IF(ROWS(A$1:A1)>COUNTA(Like!A$1:A$1000)+COUNTA(Dislike!A$1:A$1000),"",IF(ROWS(A$1:A1)>COUNTA(Like!A$1:A$1000),INDEX(Dislike!A$1:A$1000,ROWS(A$1:A1)-COUNTA(Like!A$1:A$1000)),INDEX(Like!A$1:A$1000,ROWS(A$1:A1))))

 

[covino]

Hi Peter, Great help thank you.

 

[Peter_SSs]

Hi Peter, Great help thank you.

You are welcome. Thanks for letting us know.