Compiling columns from multiple sheets

Question

Hello,

I'd like to have a hidden sheet in a workbook compile a list of all the values of Column A across multiple sheets in the workbook.

What would be the easiest way to do this so it does it automatically?

TY

Answer 1

The easiest way to do it is to use a formula like this in cell A1 of your (not-yet-hidden) sheet:

=FILTER(Sheet1!A:A,Sheet1!A:A<>"")

and then in A1001 (assuming you have fewer than 1000 entries in column A of sheet1)

=FILTER(Sheet2!A:A,Sheet2!A:A<>"")

and then in A2001

=FILTER(Sheet3!A:A,Sheet3!A:A<>"")

and so on,

and then use this to create your final list:

=FILTER(A:A,A:A<>"")

You can also sort and remove duplicates from that list easily.

As an alternative, you could create a power query against each of your column A values, then combine the queries using the Append Queries function. Then anytime you add data to any of the columns, those tables will expand, and refreshing the data will update the table. But that is a lot more work....