Excel 2007 : from horizontal to vertical cell reference

Hi all,

I need to move data from a horizontal line in sheet 2 to a vertical line in sheet 1. That by itself I can do with the transpose funtion. The problem is that I have many horizontal lines on sheet 2 and I don' t want to have to type a new transpose formula for each line.

The trick I don't know is how, when copying this formula, I can get Excel to automatically change the cell references vertically. By default it seems to want to change the cell references horizontally. Unless you add the $A$1 to fix the cell to be referenced.

I think my question is somewhat similar to this thread - http://www.excelforum.com/excel-gene...ml#post2348213 - but that solution doesn't let me continue making vertical lists of data on sheet 1 from horizontal lists of data on sheet 2.

I'm including a simplified version of the spreadsheet I'm trying to use. Hope you guys can help.

Thanks!

Rarely a good idea to "dumb down" your requirements as what works in one "simplified" scenario is unlikely to work with more complex arrangements.

We can only work with what you provide and so on that basis:

Sheet1!C5:
=INDEX(Sheet2!$2:$1000,ROUNDUP(COLUMNS($C13:C13)/3,0),ROWS(C$5:C5))

the above can be applied to all [MJ] cells without need for alteration.

Originally Posted by Cashew

I didn't mean any disrespect when posting a simplified version of the file. I just removed some of the clutter of headings, notes, and units and took into account the privacy of the data.

Not an issue of respect merely practicality.
Often people ask questions based on hypothetical setups which in reality bear little resemblance to their actual model and as you know there are (in Excel) many ways "to skin the same cat" - the most appropriate of which will largely depend upon the complexities rather than the simplicities of the problem.
It follows that being as detailed as possible from the start generally increases the chance of a quick turnaround for all concerned.

Originally Posted by Cashew

One question is why the formula, when pasted into C5, references the empty cell C13?

Ha, my bad - because when testing I was using C13 rather than C5 so as not to override your expected results (ie for comparison).
Though in effect the row is an irrelevance to the function in question (ie count of Columns in range which is unaffected) it would still be more transparent if you were to substitute $C13:C13 for $C5:C5

Originally Posted by Cashew

Also, for the bit that reads "/3,0" I understand the 3 is the number of columns over which the sheet 2 data repeats, but what does the 0 refer to?

The ROUNDUP is used to determine the Column from which data is to be extracted.

For Col C the COLUMNS function will return 1 (C:C)... 1 / 3 -> 0.333... -> rounded up to nearest whole number this becomes 1 (ie Column 1 contains our data)

For Col F the COLUMNS function will return 4 (C:F) ... 4 / 3 -> 1.333 -> rounded up to nearest whole number this becomes 2 (ie Column 2 contains our data)

and so on and so forth... so the 0 specifies the decimal significance with which to ROUNDUP the value resulting from the initial division.