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

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


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