Difficulty in transposing

  • Thread starter Thread starter Richard J
  • Start date Start date
R

Richard J

Hi,
This is my first post and there will be many more to come I expect.
My first Question is in relation to transposing. I cant seem to
transpose linked cells, is it possible at all, I have tried
everything.
Thank you all,
Richard.
 
Richard J said:
My first Question is in relation to transposing. I can't seem to
transpose linked cells, is it possible at all, I have tried everything.
Click to expand...

One way to achieve it ..

Assume source data is in Sheet1

In another sheet,
put in any starting cell, say in A2:
=INDEX(Sheet1!$1:$1,ROW(A1))
Copy down as far as required to "transpose-link" to Sheet1's A1, B1, C1, ..

If your source data in Sheet1 starts in B1 across,
use this instead in A2:
=INDEX(Sheet1!$B$1:$IV$1,ROW(A1))

To link the other way around ..
Put in any starting cell, say in E10:
=INDEX(Sheet1!$A:$A,COLUMN(A1))
Copy across as far as required to "transpose-link" to Sheet1's A1, A2, A3,
...

If your source data in Sheet1 starts in A2 down to say A100,
use this instead in E10:
=INDEX(Sheet1!$A$2:$A$100,COLUMN(A1))

Empty source cells, if any, will be returned as zeros. But we can maintain a
clean look in the sheet by suppressing the display of zeros through clicking:
Tools > Options > View tab > Uncheck "Zero values" > OK

---
 
SteveW said:
Well linked cells can be transposed.
So what exactly is the problem ?
Click to expand...

Believe Richard meant the transpose is to remain dynamic to the source data,
not transpose "frozen" values.

---
 
SteveW said:
That'll work if $$ format used in cell formula
Click to expand...
But we won't get it (ie a simple link formula) to fill across / down and
remain linked "transpose-wise" unless you subsequently edit each cell's
formula manually, or unless we use formulas such as those illustrated in my
response (the illustration's just one way, of course). Think ease of formula
propagation could be the crux issue here.

---
 
Hi Guys,

Thanks a million for the help but I'm afraid you are talking to
beginner here.
Sorry for being a bit vague with my question.
Max, you are right, I do mean the transpose to remain dynamic to th
source data, and your tip on clearing the zero values is great to know
but I found your example on transposing a bit hard to follow, my faul
for not giving a clear example.
What I am trying to do is follows: I have a bank account on on
sheet(called Bank Account) with category headings for each spend in
row and have linked each months totals to another sheet(Quart Results
where I have the 12 months broken down into 4 Quarts, here I have jus
subtotaled each Quarter, then I have subtotaled each Quarter(agai
linked) to get a year total.Then I divide each category by 12 to get
month average for the year.

Now I want to transpose the category headings, which are in a row to
column setting. No problem. But when I go to do the monthly average
just get the #REF! message in the whole column.

Sorry if this is all a bit complicated but I would love to get it righ
without having to 'frozen values' as you call them Max. I tried you
suggestion for nearly 2 hrs but couldn't figure it out, sorry.
Would love a bit more help.

Richar
 
Richard said:
.. Now I want to transpose the category headings, which are in a row to
a column setting. No problem. But when I go to do the monthly average I
just get the #REF! message in the whole column.
Click to expand...

Could you go to the top* cell in that column, copy directly from the formula
bar & paste the formula thats returning this error message in reply ?
*I'll presume you're copying this cell down the whole column


---
 
Max,
Thanks for following this up. The formula ba
reads:=SUM(#REF!/52).
The cell in question is B13.
To explain further, the sheet is called Budget and in B3 is th
word 'In' and in C3 is the word 'Out' and from D3 onwards are th
various categorys. Then in B4: ='Quart Results'!F8 the first linke
cell, then in B5: ='Quart Results'!F13, B6: ='Quart Results'!F20 and i
B7: ='Quart Results'!F26. Then in B8: =SUM(B4:B7), the years total. The
in B9 I have =SUM(B8/12) and in B10 I have =SUM(B8/52).
And the same the whole way accross for each categeory. But when
try to transpose the B10 row, to start a budget sheet I run int
trouble.

Hope this is more help.

Thanks again for your time,
Richard
 
In B13: =SUM(#REF!/52)

You got the error above because you probably earlier deleted the row or col
which previously contained the cell referenced. Once we have the formulas
carefully set-up and running in the book, it's usually best to have to just
*clear* the cell contents of data input cells (press Delete key) in routine
ops/updates. Avoid subsequent deletion of entire rows or cols which
invariably messes things up. We should also avoid having to insert new rows
within the data area. This may also throw things off somewhere else within
the formulas set-up.

in B10 I have =SUM(B8/52).
And the same the whole way across for each categeory. But when I try to transpose the B10 row, to start a budget sheet I run into trouble.
Click to expand...

Assume the above is what you have in sheet: Budget
Lets say we want to transpose link what's in B10, C10, D10 in sheet: Budget
in a new sheet, beginning with say, cell B2 (in the new sheet)

We could place in B2:
=INDEX(Budget!$B$10:$IV$10,,ROW(A1))

This returns the same as the simple link formula: =Budget!B10
When we copy B2 down to B3, B3 will then return the same as: =Budget!C10
and so on, to yield the required transpose of the horizontal source cells in
sheet: Budget into a vertical linked range here, in the new sheet.


---
 
Max,
You are a genius! It works a treat, not sure how it works but it
works. I wanted to put the category titles in column A, but that messed
things up a bit, so I moved to column C and then it worked in B,
proberbly because of your reference to A in the formula.
You are right about deleting entire rows etc, I tend to do that and
must be more careful in future.
Just one last question, what is the 'IV' in the formula and why
reference the A column?

Thanks a lot,
Richard.
 
I am not Max but IV is the last column in Excel 97 - 2003 (in 20047 it will
be XFD
The reference to A1 and ROW is because ROW(A1) will return 1 (A1 is the
first row)
and copied down it will return ROW(A2) = 2 and so on
I would personally use ROWS($A$1:A1) since it is unaffected by inserting
rows above it


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
 
Peo, thanks for the help !

Richard: In the top formula cell for copying down, I'd use ROW(A1) out of
"convention". ROW(B1) , ROW(C1), ROW(D1) ... all of these also return the
same result as ROW(A1) and could have been used instead of ROW(A1) as the
incrementer within the INDEX formula.

---
 
Back
Top