On 2/18/2011 2:56 PM, . wrote:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
My spreadsheet has a tiny monthly calendar on it that has 1 cell for
each day. So, it has 7 columns across and 8 rows down. When you select
the cells it has a range like this- A1:G8
I want to put the contents of cells A1 to G8 into 1 cell on different
sheet in the same file.
It can be done by;
- - Selecting and Copying A1:G8
- - Pasting it into Writer (at this point it appears like a graphic that
you can rescale)
- - Copying it from Writer and pasting it into the single cell using
Paste Special-----> GDI metafile
That works well but there's got to be an easier way.
The basic approach I'm suggesting is to concatenate the 56 cells (along
with end-of-lines) to give the contents of the target cell.
The formula used is:
***** Long Line Begins *****
=A1&B1&C1&D1&E1&F1&G1&H1
&A2&B2&C2&D2&E2&F2&G2&H1
&A3&B3&C3&D3&E3&F3&G3&H1
&A4&B4&C4&D4&E4&F4&G4&H1
&A5&B5&C5&D5&E5&F5&G5&H1
&A6&B6&C6&D6&E6&F6&G6&H1
&A7&B7&C7&D7&E7&F7&G7&H1
&A8&B8&C8&D8&E8&F8&G8
***** End of Long Line ****
Explanation: cells A1:G8 contained the Name of the Month, Sun, Mon,
etc., and the Day-numbers, 1-31, as appropriate, but formatted as text
with a single quote, zero or one space, the number, and two more spaces.
Cell H1 contained an End-of-Line, created by pressing F2, ctrl+Enter,
Enter. The resulting calendar cell was formatted Courier New, a
fixed-width font.
Since in practice the number wouldn't naturally be formatted as text,
the practical implementation I suggest is to generate a second matrix
somewhere -- I put mine in I1:O8 -- and use formulas to produce the
formatted text. Assuming the first two rows and the last are text
already, the formula '=A1 (for I1) serves to simply copy directly.
For I3:O7 the formulas read like this (for I3)
=RIGHT(" "&TEXT(A3;"#")&" ";4)
The formula for the target cell is similar to that given above, except
that it references the cells in the new matrix (I1:O8) and the
end-of-line cell, which has been relocated to I9. (Incidently, to
remind me to not over-write or delete that cell, I color the background
Red.) Here is the formula.
***** Long Line Begins *****
=I1&J1&K1&L1&M1&N1&O1&$I$9
&I2&J2&K2&L2&M2&N2&O2&$I$9
&I3&J3&K3&L3&M3&N3&O3&$I$9
&I4&J4&K4&L4&M4&N4&O4&$I$9
&I5&J5&K5&L5&M5&N5&O5&$I$9
&I6&J6&K6&L6&M6&N6&O6&$I$9
&I7&J7&K7&L7&M7&N7&O7&$I$9
&I8&J8&K8&L8&M8&N8&O8
***** End of Long Line ****
I can send a copy of the spreadsheet by private email if you wish.
--
------------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected] with Subject: help
