Transposing values, formulas or even comments are easy in MS Excel (or OpenOffice / LibreOffice) but transposing links is not an inbuilt function. The problem is explained graphically in part-2. The necessity for such a transpose is explored in part-1. My own 2 cents worth has been shoved in at part-2C.
To recap:
The problem is that transposing from one sheet to another is possible with formulas, values, formatting etc., but not with links.
Merely transposing ‘values’ will not be enough as it would be back to square one with 2 sheets to update. It must be a live link.
With 24*40 sized array, it would be madness to try and enter the formula by hand in 960+ cells.
Thus it was time not to get mad but get smart.
The problem
The problem as explained is that MS Excel or for that matter no spreadsheet program (Google Docs, OpenOffice or LibreOffice) supports transposing ‘links’ but allow only values, formulas, comments, formatting, etc.
Apply Logic
Applying logic superficially the links should be typed in manually. Even I am not that mad to attempt it. If logic is applied disregarding the tunnel vision - something called ‘outside the box thinking’, a solution does appear feasible.
If none of you had figured it out after reading part-1 and part-2 yesterday, as soon as I enumerate it, you’re going to think ‘now, why did not I think of it?’.
Stating it once again is not going to hurt anyone. Transpose function works only with values, formulas, etc., but not with links. If we stop thinking of each type as separate, non-interchangeable ‘data’ and instead think of converting one type into another, temporarily, can we meet our objectives?
Suppose if we can change the ‘link’ into ‘values’, MS Excel will transpose, would it not? So, let’s try...
The 5 step solution
Let us work with a sample worksheet, part of the original hhtable. I have taken a 6 x 6 array and please note that though the data are from the original table, they may not represent actual specifications as some data have been altered to highlight differences visually.
Typing ‘Ctrl+~’ reveals the formula/links. Note that the worksheet ‘transpose’ is linked to specific cells from worksheet ‘specs’
MS Excel (all spreadsheet programs) misbehave in transposing, I suspect, when they encounter the worksheet indicator ‘!’. Since we cannot do anything about it or it is foolhardy to mess around with it, let us tackle the ‘=’ (equal to) sign, which indicates a formula in a spreadsheet.
I am going to use Find and Replace to replace all the ‘=’ signs with ‘$$$$$’ in the worksheet ‘transpose’. I think you can use any character as long as they are not mathematical operators or parenthesis and the pattern (like five ‘$’ signs) do not appear on the worksheet.
Using the ‘Paste Special’ function, it is time to ‘transpose’ with crossed fingers.
Wow! The result looks good! The transpose and the workaround does work !! A small quirk did popup while attempting transpose. MS Excel (ver. 2003 is what I have) complains that the copy and paste/transpose areas are not same.
Even though it is a 6 x 6 cell ‘square’ array, MS Excel still complains.
Does MS Excel want the same *area* to be ‘selected’ to transpose? I mean the selected area is about (I think) 600X200 pixels. Does MS Excel want the selected transpose ‘area’ to be of same size, transposed?
A 200x600 pixel area to be selected? If so, it is stupid and the logic behind it (there must be some logic behind it for MS to adopt it) must be looked into and solutions found. That’s the reason the transposed data starts at A8 instead of A1.
Now to retrace the steps and see whether the links still will work. The question is whether MS Excel or any other spreadsheet program is stupid enough that it cannot see the data has been transposed? In practical terms, can they realise that I have changed the data from its ‘left’ to ‘right’ profile. (Eff it! What would you know! I doubt the present internet kids know or understand a photographic left or right profile) But it MS does not care and that’s what it matters.
8-0
The links are back transposed!!
There you have it!
To recap:
The problem is that transposing from one sheet to another is possible with formulas, values, formatting etc., but not with links.
Merely transposing ‘values’ will not be enough as it would be back to square one with 2 sheets to update. It must be a live link.
With 24*40 sized array, it would be madness to try and enter the formula by hand in 960+ cells.
Thus it was time not to get mad but get smart.
The problem
The problem as explained is that MS Excel or for that matter no spreadsheet program (Google Docs, OpenOffice or LibreOffice) supports transposing ‘links’ but allow only values, formulas, comments, formatting, etc.
Apply Logic
Applying logic superficially the links should be typed in manually. Even I am not that mad to attempt it. If logic is applied disregarding the tunnel vision - something called ‘outside the box thinking’, a solution does appear feasible.
If none of you had figured it out after reading part-1 and part-2 yesterday, as soon as I enumerate it, you’re going to think ‘now, why did not I think of it?’.
Stating it once again is not going to hurt anyone. Transpose function works only with values, formulas, etc., but not with links. If we stop thinking of each type as separate, non-interchangeable ‘data’ and instead think of converting one type into another, temporarily, can we meet our objectives?
Suppose if we can change the ‘link’ into ‘values’, MS Excel will transpose, would it not? So, let’s try...
The 5 step solution
Let us work with a sample worksheet, part of the original hhtable. I have taken a 6 x 6 array and please note that though the data are from the original table, they may not represent actual specifications as some data have been altered to highlight differences visually.
Typing ‘Ctrl+~’ reveals the formula/links. Note that the worksheet ‘transpose’ is linked to specific cells from worksheet ‘specs’
MS Excel (all spreadsheet programs) misbehave in transposing, I suspect, when they encounter the worksheet indicator ‘!’. Since we cannot do anything about it or it is foolhardy to mess around with it, let us tackle the ‘=’ (equal to) sign, which indicates a formula in a spreadsheet.
I am going to use Find and Replace to replace all the ‘=’ signs with ‘$$$$$’ in the worksheet ‘transpose’. I think you can use any character as long as they are not mathematical operators or parenthesis and the pattern (like five ‘$’ signs) do not appear on the worksheet.
Using the ‘Paste Special’ function, it is time to ‘transpose’ with crossed fingers.
Wow! The result looks good! The transpose and the workaround does work !! A small quirk did popup while attempting transpose. MS Excel (ver. 2003 is what I have) complains that the copy and paste/transpose areas are not same.
Even though it is a 6 x 6 cell ‘square’ array, MS Excel still complains.
Does MS Excel want the same *area* to be ‘selected’ to transpose? I mean the selected area is about (I think) 600X200 pixels. Does MS Excel want the selected transpose ‘area’ to be of same size, transposed?
A 200x600 pixel area to be selected? If so, it is stupid and the logic behind it (there must be some logic behind it for MS to adopt it) must be looked into and solutions found. That’s the reason the transposed data starts at A8 instead of A1.
Now to retrace the steps and see whether the links still will work. The question is whether MS Excel or any other spreadsheet program is stupid enough that it cannot see the data has been transposed? In practical terms, can they realise that I have changed the data from its ‘left’ to ‘right’ profile. (Eff it! What would you know! I doubt the present internet kids know or understand a photographic left or right profile) But it MS does not care and that’s what it matters.
8-0
The links are back transposed!!
There you have it!
No comments:
Post a Comment