Fay has attended:
Excel Advanced course
Transpose a row with formulas
I have data in a column where each cell contains performance data (in %) for a specific month. There are formulas in the cells of this column.
I have to create a table in which the rows are years and the columns are months. I want to refer each cell of this table to a cell in the above column.
I can't copy and paste special transpose because I don't just want the values. I want to refer to a cell.
Is there a formula for this or a quick way to populate the table?
Thanks a lot!
RE: Transpose a row with formulas
Hi Fay,
There is a transpose function (here's the link to the page on it: https://support.office.com/en-gb/article/transpose-function-ed039415-ed8a-4a81-93e9-4b6dfac76027 ) but I'm not sure this will solve your problem.
NB: this is an array formula so you need to finish up with ctrl + shift + enter.
If you just do enter it won't work...
Let me know how you get on and if this solves the problem.
thanks
Claire
RE: Transpose a row with formulas
Hi Claire,
Thank you for coming back so quickly.
I've tried the transpose function but it doesn't do what I need it to do. I want to be able to create a formula in one cell and then drag it to the right with the black cross, with each incremental cell referring to one further down from the list. Not sure if it makes sense?
Many thanks,
Fay
RE: Transpose a row with formulas
You can use the Offset function to give a cell relative to another cell.
The rows position states how many rows from the start point.
The columns position states how many columns from the start point.
Positive numbers go down and right. Negative numbers go up and left.
So if you state the top of the list you can work down from there.
If the top of your list is in row 1 then this is easy.
=OFFSET($A$1,ROW($A$1)+1,0)
We're using the ROW function to get the row position of your start point and then adding one to it. As you drag the formula it will increment.
If it's not then we need to fudge a bit...
=OFFSET($A$2,C1,0)
Where you've put the offset value in C1.
Here's the link to the help page: https://support.office.com/en-us/article/OFFSET-function-C8DE19AE-DD79-4B9B-A14E-B4D906D11B66
I hope that's not too mind blowing. Let me know if you have any questions.
Claire
