Transposing while AutoFilling

[dwhims]

Good afternoon, folks!

I have 2 worksheets in my workbook. Sheet 1 contains basically database information, with a subtotal line at the bottom (cells A100:Z100). I will be using filters on this data, thereby changing the subtotals.

Sheet 2 contains a summary of the filtered information, but in a transposed format (A100:Z100 becomes A1:A26). This page contains cell references to Sheet 1.

I am just in the process of setting this up, and am wondering if there is an easier way to do it. What I'm doing right now is:

On Sheet 2, create my cell reference to Sheet 1!A100.

Then I use AutoFill to drag my cell references across, which increments the cell references appropriately.

If I then use copy | paste special Transpose, it changes my cell references based on the target range, rather than keeping the cell references the way I want.

So, before copying and transposing, I edit each cell reference and anchor it. Then I can copy and transpose with no problem.

If this were just being done on one sheet, I would just bite the bullet. However, it's something I'm being asked to do more and more frequently. Am I missing something? Is there an easier way to do this? Have I explained myself clearly? Am I asking too many questions?

Thanks in advance for any advice offered.

 

[Zack Barresse]

how about the TRANSPOSE function.

in sheet 2, highlight A1:A26, enter:

=TRANSPOSE(Sheet1!A100:Z100)

confirm with Ctrl + Shift + Enter as this is an array formula. the ranges must match and you cannot clear only one cell of data, you must clear all of them (if desired) as you cannot change only one part of an array.

anyway, hope this helps.

 

[Andrew Poulsom]

Or you can use one of these formulas:

=INDEX(Sheet1!$A$100:$Z$100,1,ROW(A1))

=OFFSET(Sheet1!$A$100,0,ROW(A1)-1)

and drag it down.

 

[dwhims]

WOW! So many options! I tried all three of these suggestions, and each works great!

Thanks so much for your help -- it's nice to know I always know I can count on this site to help me out!

 

[Data123]

how about the TRANSPOSE function.

in sheet 2, highlight A1:A26, enter:

=TRANSPOSE(Sheet1!A100:Z100)

confirm with Ctrl + Shift + Enter as this is an array formula. the ranges must match and you cannot clear only one cell of data, you must clear all of them (if desired) as you cannot change only one part of an array.

anyway, hope this helps.

Hi I have a similar question and wondering how best to transpose a sheet with data in it already? I would like to move the symbols to column A and the dates to where the symbols are currently (column B, C....) and lastly the data to match the symbols. I can do it by creating a new tab and to copy and paste the symbols, then dates and then data. Are there any shortcuts to transpose this as mentioned without creating a new tab and that's efficient please?

Here is a link to the workbook https://1drv.ms/x/c/c909fcd425cc577...YzRkQ5LTFDQTItNEM3Mi05M0IxLTBGOUEyNEM5ODY5Rn0