Difficulty in transposing

[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.

[SteveW]

Well linked cells can be transposed.

So what exactly is the problem ?

Steve

On Mon, 31 Jul 2006 23:04:37 +0100, Richard J
<[email protected]> wrote:

>
> 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.
>
>

[Max]

"Richard J" wrote:
> 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.

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

[Max]

"SteveW" wrote:
> Well linked cells can be transposed.
> So what exactly is the problem ?

Believe Richard meant the transpose is to remain dynamic to the source data,
not transpose "frozen" values.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

[SteveW]

That'll work if $$ format used in cell formula

Steve

On Tue, 01 Aug 2006 00:21:02 +0100, Max <[email protected]> wrote:

> "SteveW" wrote:
>> Well linked cells can be transposed.
>> So what exactly is the problem ?
>
> Believe Richard meant the transpose is to remain dynamic to the source=
=

> data,
> not transpose "frozen" values.

[Max]

"SteveW" wrote:
> That'll work if $$ format used in cell formula
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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

[Richard J]

Hi Guys,

Thanks a million for the help but I'm afraid you are talking to a beginner here.
Sorry for being a bit vague with my question.
Max, you are right, I do mean the transpose to remain dynamic to the 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 fault for not giving a clear example.
What I am trying to do is follows: I have a bank account on one sheet(called Bank Account) with category headings for each spend in a 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 just subtotaled each Quarter, then I have subtotaled each Quarter(again linked) to get a year total.Then I divide each category by 12 to get a month average for the year.

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.

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

Richard

[Max]

Richard wrote:
> .. 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.

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
Singapore
http://savefile.com/projects/236895
xdemechanik
---

[Richard J]

Max,
Thanks for following this up. The formula bar reads:=SUM(#REF!/52).
The cell in question is B13.
To explain further, the sheet is called Budget and in B3 is the word 'In' and in C3 is the word 'Out' and from D3 onwards are the various categorys. Then in B4: ='Quart Results'!F8 the first linked cell, then in B5: ='Quart Results'!F13, B6: ='Quart Results'!F20 and in B7: ='Quart Results'!F26. Then in B8: =SUM(B4:B7), the years total. Then 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 I try to transpose the B10 row, to start a budget sheet I run into trouble.

Hope this is more help.

Thanks again for your time,
Richard.

[Max]

> 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.

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
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Richard J" wrote:
>
> Max,
> Thanks for following this up. The formula bar
> reads:=SUM(#REF!/52).
> The cell in question is B13.
> To explain further, the sheet is called Budget and in B3 is the
> word 'In' and in C3 is the word 'Out' and from D3 onwards are the
> various categorys. Then in B4: ='Quart Results'!F8 the first linked
> cell, then in B5: ='Quart Results'!F13, B6: ='Quart Results'!F20 and in
> B7: ='Quart Results'!F26. Then in B8: =SUM(B4:B7), the years total. Then
> 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 I
> try to transpose the B10 row, to start a budget sheet I run into
> trouble.
>
> Hope this is more help.
>
> Thanks again for your time,
> Richard.
>
>
> --
> Richard J
> ------------------------------------------------------------------------
> Richard J's Profile: http://www.excelforum.com/member.php...o&userid=36962
> View this thread: http://www.excelforum.com/showthread...hreadid=566844
>
>

[Richard J]

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.

[Peo Sjoblom]

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





"Richard J" <[email protected]> wrote
in message news:[email protected]...
>
> 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.
>
>
> --
> Richard J
> ------------------------------------------------------------------------
> Richard J's Profile:
> http://www.excelforum.com/member.php...o&userid=36962
> View this thread: http://www.excelforum.com/showthread...hreadid=566844
>

[Max]

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---