Transpose data but still have cell referecnicng 1

[withoutaclue]

I have 2 work sheets in excel.
Sheet 1 with data imported from an Access table
and Sheet 2 with data transposed/rotaed from sheet 1,
(so that the colume headings now read as vertical headings and vice versa)

however i want it so that when i change data in the access table or sheet 1, sheet 2 will automaticaly change as well,

So i guess im trying to say can some one please help me transpose data that can still cell refencence

Many thanks
Jo

 

[SkipVought]

withoutaclue,

Generally, this is NOT a good idea. Why? What if the number of rows from your Access table exceeds 255? Can't possibly transpose that!. 256 columns is all you've got in Excel!

Now in your specifice case, are you ever gonna come close to importing a table of 255 rows? If so, give it up. Otherwise, you'll have to go with a macro. Let me know.



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[withoutaclue]

Hi skip, thanks to your responce, its only a small DB of 32 colums max and 14 rows,

Im able to copy and paste data in excel from 1 sheet to anouther and transopose it, but it has no Cell referenceing, and thats they key thing that i need,

Any ideas??

 

[GlennUK]

Hi withoutaclue,

select an area in sheet2 that is 32 rows by 14 columns, and type this formula:

=TRANSPOSE(Sheet1!A1:AF14)

and press Ctrl-Shift-Enter, and the linked area from sheet1 will be transposed into sheet2.

Cheers, Glenn.

 

[SkipVought]

Glenn,

Of course, but what happens with the next import from Access? A spreadsheet function does not cut it.

But I learned something from your tip, and you deserve a STAR.


Skip,

http://www.TheOfficeExperts.com

 

[SkipVought]

withoutaclue,

If you can live with #N/A displays...

1. Name you import data table using the OFFSET Function in Insert/Name/Define -- I used rData for the Range Name and this formula in the RefersTo textbox...

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))

2. On sheet 2 select 32 rows (32 is the MAX number of columns of import data)

3. enter the transpose formula...

=transpose(rData)

and Ctrl-Shift-Enter

VOLA!

Your transpose is DYNAMIC!

Skip,

http://www.TheOfficeExperts.com

 

[SkipVought]

I made what I consider an improvement.

1. I modified the ROW RANGE in the offset formula to be 256, the maximum number of columns in Excel.

2. I modified the transpose formula...

=IF(ISBLANK(TRANSPOSE(rData)),"",TRANSPOSE(rData))

Result: no #N/A or other extraneous data

Skip,

http://www.TheOfficeExperts.com