Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem with transpose function
Hi All
I've always been a passive user of this pages which have provided me a solution to all my excel problems just browsing through these pages. For the first time it hasn' worked so posting my problem here. On using the following function excel crashes. Can someone figure out why: =TRANSPOSE(INDIRECT(ADDRESS(row($A$1:$B$5),COLUMN( $A$1:$B $5),,,"Sheet1"))) I'm writing the formula on Sheet2. The formula works fine when being used on Sheet1. It goes without saying that I will owe a huge debt of gratitude to anyone who can shed some light on this. Ajitpal |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem with transpose function
It crashed on me too (either sheet) using Excel 2002. Don't know why.
But, this formula will do the same thing and doesn't crash (array entered): =TRANSPOSE(Sheet1!A1:B5) -- Biff Microsoft Excel MVP wrote in message ups.com... Hi All I've always been a passive user of this pages which have provided me a solution to all my excel problems just browsing through these pages. For the first time it hasn' worked so posting my problem here. On using the following function excel crashes. Can someone figure out why: =TRANSPOSE(INDIRECT(ADDRESS(row($A$1:$B$5),COLUMN( $A$1:$B $5),,,"Sheet1"))) I'm writing the formula on Sheet2. The formula works fine when being used on Sheet1. It goes without saying that I will owe a huge debt of gratitude to anyone who can shed some light on this. Ajitpal |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem with transpose function
On Oct 7, 11:00 am, "T. Valko" wrote:
It crashed on me too (either sheet) using Excel 2002. Don't know why. But, this formula will do the same thing and doesn't crash (array entered): =TRANSPOSE(Sheet1!A1:B5) -- Biff Microsoft Excel MVP wrote in message ups.com... Hi All I've always been a passive user of this pages which have provided me a solution to all my excel problems just browsing through these pages. For the first time it hasn' worked so posting my problem here. On using the following function excel crashes. Can someone figure out why: =TRANSPOSE(INDIRECT(ADDRESS(row($A$1:$B$5),COLUMN( $A$1:$B $5),,,"Sheet1"))) I'm writing the formula on Sheet2. The formula works fine when being used on Sheet1. It goes without saying that I will owe a huge debt of gratitude to anyone who can shed some light on this. Ajitpal- Hide quoted text - - Show quoted text - Hi Thanks for the prompt response. You Idea will work but I have a specific need to provide the input to Transpose as I do. I construct the range that Transpose needs to look at in another cell not by direct reference to the range as you suggest. Ajitpal |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem with transpose function
Using indirect with other functions like lookup in array formulas can also
cause crashes. It seems Excel does not convert references to numbers/text inside arrays like this by default so they need to be "Dereferenced". This can be done using N(INDIRECT(...)) or T(INDIRECT(...) inside the Transpose function depending on the type of data. " wrote: Hi All I've always been a passive user of this pages which have provided me a solution to all my excel problems just browsing through these pages. For the first time it hasn' worked so posting my problem here. On using the following function excel crashes. Can someone figure out why: =TRANSPOSE(INDIRECT(ADDRESS(row($A$1:$B$5),COLUMN( $A$1:$B $5),,,"Sheet1"))) I'm writing the formula on Sheet2. The formula works fine when being used on Sheet1. It goes without saying that I will owe a huge debt of gratitude to anyone who can shed some light on this. Ajitpal |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem with transpose function
On Oct 7, 3:51 pm, Lori wrote:
Using indirect with other functions like lookup in array formulas can also cause crashes. It seems Excel does not convert references to numbers/text inside arrays like this by default so they need to be "Dereferenced". This can be done using N(INDIRECT(...)) or T(INDIRECT(...) inside the Transpose function depending on the type of data. " wrote: Hi All I've always been a passive user of this pages which have provided me a solution to all my excel problems just browsing through these pages. For the first time it hasn' worked so posting my problem here. On using the following function excel crashes. Can someone figure out why: =TRANSPOSE(INDIRECT(ADDRESS(row($A$1:$B$5),COLUMN( $A$1:$B $5),,,"Sheet1"))) I'm writing the formula on Sheet2. The formula works fine when being used on Sheet1. It goes without saying that I will owe a huge debt of gratitude to anyone who can shed some light on this. Ajitpal- Hide quoted text - - Show quoted text - Hi Lori A great suggestion. Thank you very much for taking out time for this. What I actually need from Indirect is the referencing which I would be using as an input to the get.cell function (in defined names ofcourse) to return the Cell shading (par exemple) for an array and use it as an array formula. I picked up the idea from a post on cpearson.com (address : http://www.cpearson.com/excel/Call.htm) It would be great to be able to understand why does only Transpose have this property and not another function. Again thank you very much. Ajit |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Transpose Problem | Excel Discussion (Misc queries) | |||
Transpose Function | Excel Worksheet Functions | |||
Not exactly a transpose problem | Excel Discussion (Misc queries) | |||
How do I use the Transpose Function | Excel Worksheet Functions | |||
Transpose Problem | Excel Discussion (Misc queries) |