Excel - Keep original cell formatting when using transpose array formula

I am currently using the array formula:

=TRANSPOSE(DataSheet!$AH$1:$BX$8)

to transpose a set of data from another sheet. However, the cell
formatting (specifically cell color) does not transpose with this array
formula as it does when you simple paste> transpose

is there a way to edit this array formula to preserve the original cell
color? Preferably not VBA as this doc will be shared with many who do not
necessarily want to allow macros

Secondly (hope its ok to ask in same post) I would like this same data
set to be copied and pasted next to what i'm talking about above and be
sorted by cell color on top. is there a formula for this?

appreciate any help! thank you!

(Please see attached for what it should ideally look like, though I did
this manually and would like it to be automatic stemming from my
datasheet)


+-------------------------------------------------------------------+
|Filename: EXCEL EX.jpg                                             |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=287|
+-------------------------------------------------------------------+



--
Claire Charron

No, no, and no! Sorry, but everything you want to do requires VBA. The
only reason the formatting transposed with Paste>Transpose is because
it tagged along with Copy.

Your 2nd request also requires VBA and you would  have to specify what order
would you like the colors sorted to.

Is there isome reason you cannot pre-format the cells with the TRANSPOSE
formula?

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc