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

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.

--
Biff
Microsoft Excel MVP


"Roady" wrote in message
...
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

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

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"Roady" wrote in message
...
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

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


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

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"Roady" wrote in message
...
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