transfer data from 3x15 grid to a single list?

[Roady]

Hi:

I have data in cells Y15:AA29. (3 columns, 15 rows). What I want to be able
to do (easily) is have the data from those cells feed into a single list.
However, I then have many more pages of data in this 3 column 15 row format
and I want the order to be the same as the first group. So, the first 15
boxes (across 3 columns) will be grouped together and then it will start down
with the next group of 15 (which in this case would be Y30:AA44.) My ultimate
goal after this is complete is to then be able to create numerous named data
validation lists (because you can't do this unless the data is all in the
same column).

As an additional note, the data in these cells are formulas pulling the data
from another worksheet tab within the same workbook.

thanks! JR

 

[T. Valko]

One way:

Assume you want the transposed range to start in U15 downwards.

Enter this formula in U15 and copy down as needed:

=OFFSET(Y$15,INT((ROWS(U$15:U15)-1)/3),MOD(ROWS(U$15:U15)-1,3))

The 3 in the formula refers to the width (in columns) of your original
table.

 

[Roady]

That's remarkable! I must look up more information about array tables and
educate myself. thank you!

 

[T. Valko]

You're welcome. Thanks for the feedback!