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
------------------------------------------------------------------------
Richard J's Profile: http://www.excelforum.com/member.php...o&userid=36962
View this thread: http://www.excelforum.com/showthread...hreadid=566844

Well linked cells can be transposed.

So what exactly is the problem ?

Steve

On Mon, 31 Jul 2006 23:04:37 +0100, Richard J
<(E-Mail Removed)> 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.
>
>

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

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

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

Steve

On Tue, 01 Aug 2006 00:21:02 +0100, Max <(E-Mail Removed)> 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.

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

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
-----------------------------------------------------------------------
Richard J's Profile: http://www.excelforum.com/member.php...fo&userid=3696
View this thread: http://www.excelforum.com/showthread.php?threadid=56684

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

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

--
Richard
-----------------------------------------------------------------------
Richard J's Profile: http://www.excelforum.com/member.php...fo&userid=3696
View this thread: http://www.excelforum.com/showthread.php?threadid=56684

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