Changing data orientation from one sheet to another

Hi,
On sheet1 the data is oriented vertically as
A1
A2
A3
I'd like on sheet2 the data be oriented horizontally
A1 to A1
A2 to B1
A3 to B3
Can it be done? if so, how?
TIA
Olga
Excell 07
0
4/20/2008 5:55:12 PM
excel.newusers 15348 articles. 2 followers. Follow

10 Replies
832 Views

Similar Articles

[PageSpeed] 38

Copy the data from sheet1

Select A1 of sheet2 and paste special>transpose.

Note:  cannot be done if you have more than 16384 rows of data in column A

Also, I think you have a typo at  A3 to B3.........maybe A3 to C1?


Gord Dibben  MS Excel MVP

On Sun, 20 Apr 2008 19:55:12 +0200, "Olga" <noemail@hotmail.com> wrote:

>Hi,
>On sheet1 the data is oriented vertically as
>A1
>A2
>A3
>I'd like on sheet2 the data be oriented horizontally
>A1 to A1
>A2 to B1
>A3 to B3
>Can it be done? if so, how?
>TIA
>Olga
>Excell 07

0
Gord
4/20/2008 6:32:16 PM
Thank you Gord,
The data is coming from a SQL query and it is text and money values. If I 
copy A1 from sheet 1 and paste it as transpose on sheet2 works however, if I 
drag A1 horizontally to fulfill the rest of the cells then, the information 
is not coherent. Surly I'm doing some wrong.


"Gord Dibben" <gorddibbATshawDOTca> escribi� en el mensaje de noticias 
news:hr2n04d0r9ld2dan3q3oj6ejm6anqh5dhf@4ax.com...
> Copy the data from sheet1
>
> Select A1 of sheet2 and paste special>transpose.
>
> Note:  cannot be done if you have more than 16384 rows of data in column A
>
> Also, I think you have a typo at  A3 to B3.........maybe A3 to C1?
>
>
> Gord Dibben  MS Excel MVP
>
> On Sun, 20 Apr 2008 19:55:12 +0200, "Olga" <noemail@hotmail.com> wrote:
>
>>Hi,
>>On sheet1 the data is oriented vertically as
>>A1
>>A2
>>A3
>>I'd like on sheet2 the data be oriented horizontally
>>A1 to A1
>>A2 to B1
>>A3 to B3
>>Can it be done? if so, how?
>>TIA
>>Olga
>>Excell 07
> 

0
4/20/2008 7:05:07 PM
"Surley I'm doing some wrong"

Yes, you are<g>

Re-read the instructions................I did not say to copy A1 from sheet1

Select the data range on sheet1 and copy.

Select A1 on sheet2 and Edit>Paste Special>Transpose>OK>Esc

No dragging needed.  You are paste/transposing the copied range, not just one
cell.


Gord

On Sun, 20 Apr 2008 21:05:07 +0200, "Olga" <noemail@hotmail.com> wrote:

>Thank you Gord,
>The data is coming from a SQL query and it is text and money values. If I 
>copy A1 from sheet 1 and paste it as transpose on sheet2 works however, if I 
>drag A1 horizontally to fulfill the rest of the cells then, the information 
>is not coherent. Surly I'm doing some wrong.
>
>
>"Gord Dibben" <gorddibbATshawDOTca> escribi� en el mensaje de noticias 
>news:hr2n04d0r9ld2dan3q3oj6ejm6anqh5dhf@4ax.com...
>> Copy the data from sheet1
>>
>> Select A1 of sheet2 and paste special>transpose.
>>
>> Note:  cannot be done if you have more than 16384 rows of data in column A
>>
>> Also, I think you have a typo at  A3 to B3.........maybe A3 to C1?
>>
>>
>> Gord Dibben  MS Excel MVP
>>
>> On Sun, 20 Apr 2008 19:55:12 +0200, "Olga" <noemail@hotmail.com> wrote:
>>
>>>Hi,
>>>On sheet1 the data is oriented vertically as
>>>A1
>>>A2
>>>A3
>>>I'd like on sheet2 the data be oriented horizontally
>>>A1 to A1
>>>A2 to B1
>>>A3 to B3
>>>Can it be done? if so, how?
>>>TIA
>>>Olga
>>>Excell 07
>> 

0
Gord
4/20/2008 8:54:15 PM
Thank you very much, it worked. But how would sheet 2 update itself when 
sheet 1 is updated refreshing the db connection?

"Gord Dibben" <gorddibbATshawDOTca> escribi� en el mensaje de noticias 
news:n2bn04hjl0f2ddtlnf1goupbjj1ttnc01k@4ax.com...
> "Surley I'm doing some wrong"
>
> Yes, you are<g>
>
> Re-read the instructions................I did not say to copy A1 from 
> sheet1
>
> Select the data range on sheet1 and copy.
>
> Select A1 on sheet2 and Edit>Paste Special>Transpose>OK>Esc
>
> No dragging needed.  You are paste/transposing the copied range, not just 
> one
> cell.
>
>
> Gord
>
> On Sun, 20 Apr 2008 21:05:07 +0200, "Olga" <noemail@hotmail.com> wrote:
>
>>Thank you Gord,
>>The data is coming from a SQL query and it is text and money values. If I
>>copy A1 from sheet 1 and paste it as transpose on sheet2 works however, if 
>>I
>>drag A1 horizontally to fulfill the rest of the cells then, the 
>>information
>>is not coherent. Surly I'm doing some wrong.
>>
>>
>>"Gord Dibben" <gorddibbATshawDOTca> escribi� en el mensaje de noticias
>>news:hr2n04d0r9ld2dan3q3oj6ejm6anqh5dhf@4ax.com...
>>> Copy the data from sheet1
>>>
>>> Select A1 of sheet2 and paste special>transpose.
>>>
>>> Note:  cannot be done if you have more than 16384 rows of data in column 
>>> A
>>>
>>> Also, I think you have a typo at  A3 to B3.........maybe A3 to C1?
>>>
>>>
>>> Gord Dibben  MS Excel MVP
>>>
>>> On Sun, 20 Apr 2008 19:55:12 +0200, "Olga" <noemail@hotmail.com> wrote:
>>>
>>>>Hi,
>>>>On sheet1 the data is oriented vertically as
>>>>A1
>>>>A2
>>>>A3
>>>>I'd like on sheet2 the data be oriented horizontally
>>>>A1 to A1
>>>>A2 to B1
>>>>A3 to B3
>>>>Can it be done? if so, how?
>>>>TIA
>>>>Olga
>>>>Excell 07
>>>
> 

0
4/20/2008 9:54:53 PM
Sheet2 will not update itself using the manual transpose method we just did.

We will have to link the cells to the source range then tranpose those links.

Or write a macro to do the job after each query.

What is your original source range that will be transposed?

Is it consistent or variable range?


Gord

On Sun, 20 Apr 2008 23:54:53 +0200, "Olga" <noemail@hotmail.com> wrote:

>Thank you very much, it worked. But how would sheet 2 update itself when 
>sheet 1 is updated refreshing the db connection?
>
>"Gord Dibben" <gorddibbATshawDOTca> escribi� en el mensaje de noticias 
>news:n2bn04hjl0f2ddtlnf1goupbjj1ttnc01k@4ax.com...
>> "Surley I'm doing some wrong"
>>
>> Yes, you are<g>
>>
>> Re-read the instructions................I did not say to copy A1 from 
>> sheet1
>>
>> Select the data range on sheet1 and copy.
>>
>> Select A1 on sheet2 and Edit>Paste Special>Transpose>OK>Esc
>>
>> No dragging needed.  You are paste/transposing the copied range, not just 
>> one
>> cell.
>>
>>
>> Gord
>>
>> On Sun, 20 Apr 2008 21:05:07 +0200, "Olga" <noemail@hotmail.com> wrote:
>>
>>>Thank you Gord,
>>>The data is coming from a SQL query and it is text and money values. If I
>>>copy A1 from sheet 1 and paste it as transpose on sheet2 works however, if 
>>>I
>>>drag A1 horizontally to fulfill the rest of the cells then, the 
>>>information
>>>is not coherent. Surly I'm doing some wrong.
>>>
>>>
>>>"Gord Dibben" <gorddibbATshawDOTca> escribi� en el mensaje de noticias
>>>news:hr2n04d0r9ld2dan3q3oj6ejm6anqh5dhf@4ax.com...
>>>> Copy the data from sheet1
>>>>
>>>> Select A1 of sheet2 and paste special>transpose.
>>>>
>>>> Note:  cannot be done if you have more than 16384 rows of data in column 
>>>> A
>>>>
>>>> Also, I think you have a typo at  A3 to B3.........maybe A3 to C1?
>>>>
>>>>
>>>> Gord Dibben  MS Excel MVP
>>>>
>>>> On Sun, 20 Apr 2008 19:55:12 +0200, "Olga" <noemail@hotmail.com> wrote:
>>>>
>>>>>Hi,
>>>>>On sheet1 the data is oriented vertically as
>>>>>A1
>>>>>A2
>>>>>A3
>>>>>I'd like on sheet2 the data be oriented horizontally
>>>>>A1 to A1
>>>>>A2 to B1
>>>>>A3 to B3
>>>>>Can it be done? if so, how?
>>>>>TIA
>>>>>Olga
>>>>>Excell 07
>>>>
>> 

0
Gord
4/20/2008 10:58:17 PM
The original sourse range is composed of two columns containing labels(A1) 
and numbers (euros B1).
It's variable, every month a new row is added by the query.

"Gord Dibben" <gorddibbATshawDOTca> escribi� en el mensaje de noticias 
news:74in045cci7i669u7aegrsqj56b7ii3196@4ax.com...
> Sheet2 will not update itself using the manual transpose method we just 
> did.
>
> We will have to link the cells to the source range then tranpose those 
> links.
>
> Or write a macro to do the job after each query.
>
> What is your original source range that will be transposed?
>
> Is it consistent or variable range?
>
>
> Gord
>
> On Sun, 20 Apr 2008 23:54:53 +0200, "Olga" <noemail@hotmail.com> wrote:
>
>>Thank you very much, it worked. But how would sheet 2 update itself when
>>sheet 1 is updated refreshing the db connection?
>>
>>"Gord Dibben" <gorddibbATshawDOTca> escribi� en el mensaje de noticias
>>news:n2bn04hjl0f2ddtlnf1goupbjj1ttnc01k@4ax.com...
>>> "Surley I'm doing some wrong"
>>>
>>> Yes, you are<g>
>>>
>>> Re-read the instructions................I did not say to copy A1 from
>>> sheet1
>>>
>>> Select the data range on sheet1 and copy.
>>>
>>> Select A1 on sheet2 and Edit>Paste Special>Transpose>OK>Esc
>>>
>>> No dragging needed.  You are paste/transposing the copied range, not 
>>> just
>>> one
>>> cell.
>>>
>>>
>>> Gord
>>>
>>> On Sun, 20 Apr 2008 21:05:07 +0200, "Olga" <noemail@hotmail.com> wrote:
>>>
>>>>Thank you Gord,
>>>>The data is coming from a SQL query and it is text and money values. If 
>>>>I
>>>>copy A1 from sheet 1 and paste it as transpose on sheet2 works however, 
>>>>if
>>>>I
>>>>drag A1 horizontally to fulfill the rest of the cells then, the
>>>>information
>>>>is not coherent. Surly I'm doing some wrong.
>>>>
>>>>
>>>>"Gord Dibben" <gorddibbATshawDOTca> escribi� en el mensaje de noticias
>>>>news:hr2n04d0r9ld2dan3q3oj6ejm6anqh5dhf@4ax.com...
>>>>> Copy the data from sheet1
>>>>>
>>>>> Select A1 of sheet2 and paste special>transpose.
>>>>>
>>>>> Note:  cannot be done if you have more than 16384 rows of data in 
>>>>> column
>>>>> A
>>>>>
>>>>> Also, I think you have a typo at  A3 to B3.........maybe A3 to C1?
>>>>>
>>>>>
>>>>> Gord Dibben  MS Excel MVP
>>>>>
>>>>> On Sun, 20 Apr 2008 19:55:12 +0200, "Olga" <noemail@hotmail.com> 
>>>>> wrote:
>>>>>
>>>>>>Hi,
>>>>>>On sheet1 the data is oriented vertically as
>>>>>>A1
>>>>>>A2
>>>>>>A3
>>>>>>I'd like on sheet2 the data be oriented horizontally
>>>>>>A1 to A1
>>>>>>A2 to B1
>>>>>>A3 to B3
>>>>>>Can it be done? if so, how?
>>>>>>TIA
>>>>>>Olga
>>>>>>Excell 07
>>>>>
>>>
> 

0
4/21/2008 6:15:14 AM
If once a month, automation should not really be required.

But, this macro can be run to copy the sheet1 data to a new sheet.

Sub select_transpose()
    Range(Range("A1:B1"), Cells(Rows.Count, Selection.Column).End(xlUp)).Copy
    Sheets.Add
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Application.CutCopyMode = False
    Range("A1").Select
End Sub


Gord

On Mon, 21 Apr 2008 08:15:14 +0200, "Olga" <noemail@hotmail.com> wrote:

>The original sourse range is composed of two columns containing labels(A1) 
>and numbers (euros B1).
>It's variable, every month a new row is added by the query.
>
>"Gord Dibben" <gorddibbATshawDOTca> escribi� en el mensaje de noticias 
>news:74in045cci7i669u7aegrsqj56b7ii3196@4ax.com...
>> Sheet2 will not update itself using the manual transpose method we just 
>> did.
>>
>> We will have to link the cells to the source range then tranpose those 
>> links.
>>
>> Or write a macro to do the job after each query.
>>
>> What is your original source range that will be transposed?
>>
>> Is it consistent or variable range?
>>
>>
>> Gord
>>
>> On Sun, 20 Apr 2008 23:54:53 +0200, "Olga" <noemail@hotmail.com> wrote:
>>
>>>Thank you very much, it worked. But how would sheet 2 update itself when
>>>sheet 1 is updated refreshing the db connection?
>>>
>>>"Gord Dibben" <gorddibbATshawDOTca> escribi� en el mensaje de noticias
>>>news:n2bn04hjl0f2ddtlnf1goupbjj1ttnc01k@4ax.com...
>>>> "Surley I'm doing some wrong"
>>>>
>>>> Yes, you are<g>
>>>>
>>>> Re-read the instructions................I did not say to copy A1 from
>>>> sheet1
>>>>
>>>> Select the data range on sheet1 and copy.
>>>>
>>>> Select A1 on sheet2 and Edit>Paste Special>Transpose>OK>Esc
>>>>
>>>> No dragging needed.  You are paste/transposing the copied range, not 
>>>> just
>>>> one
>>>> cell.
>>>>
>>>>
>>>> Gord
>>>>
>>>> On Sun, 20 Apr 2008 21:05:07 +0200, "Olga" <noemail@hotmail.com> wrote:
>>>>
>>>>>Thank you Gord,
>>>>>The data is coming from a SQL query and it is text and money values. If 
>>>>>I
>>>>>copy A1 from sheet 1 and paste it as transpose on sheet2 works however, 
>>>>>if
>>>>>I
>>>>>drag A1 horizontally to fulfill the rest of the cells then, the
>>>>>information
>>>>>is not coherent. Surly I'm doing some wrong.
>>>>>
>>>>>
>>>>>"Gord Dibben" <gorddibbATshawDOTca> escribi� en el mensaje de noticias
>>>>>news:hr2n04d0r9ld2dan3q3oj6ejm6anqh5dhf@4ax.com...
>>>>>> Copy the data from sheet1
>>>>>>
>>>>>> Select A1 of sheet2 and paste special>transpose.
>>>>>>
>>>>>> Note:  cannot be done if you have more than 16384 rows of data in 
>>>>>> column
>>>>>> A
>>>>>>
>>>>>> Also, I think you have a typo at  A3 to B3.........maybe A3 to C1?
>>>>>>
>>>>>>
>>>>>> Gord Dibben  MS Excel MVP
>>>>>>
>>>>>> On Sun, 20 Apr 2008 19:55:12 +0200, "Olga" <noemail@hotmail.com> 
>>>>>> wrote:
>>>>>>
>>>>>>>Hi,
>>>>>>>On sheet1 the data is oriented vertically as
>>>>>>>A1
>>>>>>>A2
>>>>>>>A3
>>>>>>>I'd like on sheet2 the data be oriented horizontally
>>>>>>>A1 to A1
>>>>>>>A2 to B1
>>>>>>>A3 to B3
>>>>>>>Can it be done? if so, how?
>>>>>>>TIA
>>>>>>>Olga
>>>>>>>Excell 07
>>>>>>
>>>>
>> 

0
Gord
4/22/2008 5:08:06 PM
Hi Gord,
apparently I need to do my transformation manually or by recording  a macro 
because otherwise, my cells options (borders, alignments, colors, etc) get 
mist up when actualizing the data/ query. Using the below test micro, only 
the data is updated leaving the cells format intact. My problems is that I've 
over 100 cells (a + b = 200) to manually transform. So, perhaps, I need to 
think of a loop.
------------------------------------------------------
Sub Macro2()
Sheets("TEST").Select
    Range("B16").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=DB!R[-14]C[-1]"
    Range("C16").Select
    ActiveCell.FormulaR1C1 = "=DB!R[-13]C[-2]"
    Range("D16").Select
    ActiveCell.FormulaR1C1 = "=DB!R[-12]C[-3]"
    Range("E16").Select
    ActiveCell.FormulaR1C1 = "=DB!R[-11]C[-4]"
    Range("B17").Select
    ActiveCell.FormulaR1C1 = "=DB!R[-15]C"
    Range("C17").Select
    ActiveCell.FormulaR1C1 = "=DB!R[-14]C[-1]"
    Range("D17").Select
    ActiveCell.FormulaR1C1 = "=DB!R[-13]C[-2]"
    Range("E17").Select
    ActiveCell.FormulaR1C1 = "=DB!R[-12]C[-3]"
    Range("E18").Select
End Sub

--------------------------------------------------------------

"Gord Dibben" <gorddibbATshawDOTca> escribi� en el mensaje de noticias 
news:be5s04dnrnitqh48ac82ngt9ejeke1pe3s@4ax.com...
> If once a month, automation should not really be required.
>
> But, this macro can be run to copy the sheet1 data to a new sheet.
>
> Sub select_transpose()
>    Range(Range("A1:B1"), Cells(Rows.Count, 
> Selection.Column).End(xlUp)).Copy
>    Sheets.Add
>    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, 
> SkipBlanks:= _
>        False, Transpose:=True
>    Application.CutCopyMode = False
>    Range("A1").Select
> End Sub
>
>
> Gord
>
> On Mon, 21 Apr 2008 08:15:14 +0200, "Olga" <noemail@hotmail.com> wrote:
>
>>The original sourse range is composed of two columns containing labels(A1)
>>and numbers (euros B1).
>>It's variable, every month a new row is added by the query.
>>
>>"Gord Dibben" <gorddibbATshawDOTca> escribi� en el mensaje de noticias
>>news:74in045cci7i669u7aegrsqj56b7ii3196@4ax.com...
>>> Sheet2 will not update itself using the manual transpose method we just
>>> did.
>>>
>>> We will have to link the cells to the source range then tranpose those
>>> links.
>>>
>>> Or write a macro to do the job after each query.
>>>
>>> What is your original source range that will be transposed?
>>>
>>> Is it consistent or variable range?
>>>
>>>
>>> Gord
>>>
>>> On Sun, 20 Apr 2008 23:54:53 +0200, "Olga" <noemail@hotmail.com> wrote:
>>>
>>>>Thank you very much, it worked. But how would sheet 2 update itself when
>>>>sheet 1 is updated refreshing the db connection?
>>>>
>>>>"Gord Dibben" <gorddibbATshawDOTca> escribi� en el mensaje de noticias
>>>>news:n2bn04hjl0f2ddtlnf1goupbjj1ttnc01k@4ax.com...
>>>>> "Surley I'm doing some wrong"
>>>>>
>>>>> Yes, you are<g>
>>>>>
>>>>> Re-read the instructions................I did not say to copy A1 from
>>>>> sheet1
>>>>>
>>>>> Select the data range on sheet1 and copy.
>>>>>
>>>>> Select A1 on sheet2 and Edit>Paste Special>Transpose>OK>Esc
>>>>>
>>>>> No dragging needed.  You are paste/transposing the copied range, not
>>>>> just
>>>>> one
>>>>> cell.
>>>>>
>>>>>
>>>>> Gord
>>>>>
>>>>> On Sun, 20 Apr 2008 21:05:07 +0200, "Olga" <noemail@hotmail.com> 
>>>>> wrote:
>>>>>
>>>>>>Thank you Gord,
>>>>>>The data is coming from a SQL query and it is text and money values. 
>>>>>>If
>>>>>>I
>>>>>>copy A1 from sheet 1 and paste it as transpose on sheet2 works 
>>>>>>however,
>>>>>>if
>>>>>>I
>>>>>>drag A1 horizontally to fulfill the rest of the cells then, the
>>>>>>information
>>>>>>is not coherent. Surly I'm doing some wrong.
>>>>>>
>>>>>>
>>>>>>"Gord Dibben" <gorddibbATshawDOTca> escribi� en el mensaje de noticias
>>>>>>news:hr2n04d0r9ld2dan3q3oj6ejm6anqh5dhf@4ax.com...
>>>>>>> Copy the data from sheet1
>>>>>>>
>>>>>>> Select A1 of sheet2 and paste special>transpose.
>>>>>>>
>>>>>>> Note:  cannot be done if you have more than 16384 rows of data in
>>>>>>> column
>>>>>>> A
>>>>>>>
>>>>>>> Also, I think you have a typo at  A3 to B3.........maybe A3 to C1?
>>>>>>>
>>>>>>>
>>>>>>> Gord Dibben  MS Excel MVP
>>>>>>>
>>>>>>> On Sun, 20 Apr 2008 19:55:12 +0200, "Olga" <noemail@hotmail.com>
>>>>>>> wrote:
>>>>>>>
>>>>>>>>Hi,
>>>>>>>>On sheet1 the data is oriented vertically as
>>>>>>>>A1
>>>>>>>>A2
>>>>>>>>A3
>>>>>>>>I'd like on sheet2 the data be oriented horizontally
>>>>>>>>A1 to A1
>>>>>>>>A2 to B1
>>>>>>>>A3 to B3
>>>>>>>>Can it be done? if so, how?
>>>>>>>>TIA
>>>>>>>>Olga
>>>>>>>>Excell 07
>>>>>>>
>>>>>
>>>
> 

0
olgass (1)
4/22/2008 7:19:04 PM
Did you try the macro I supplied?

What alterations do you think it needs?

Why do you think you need to link the cells if the changes are to be made once a
month?


Gord

On Tue, 22 Apr 2008 21:19:04 +0200, "Olga" <olgass@yahoo.com> wrote:

>Hi Gord,
>apparently I need to do my transformation manually or by recording  a macro 
>because otherwise, my cells options (borders, alignments, colors, etc) get 
>mist up when actualizing the data/ query. Using the below test micro, only 
>the data is updated leaving the cells format intact. My problems is that I've 
>over 100 cells (a + b = 200) to manually transform. So, perhaps, I need to 
>think of a loop.
>------------------------------------------------------
>Sub Macro2()
>Sheets("TEST").Select
>    Range("B16").Select
>    Application.CutCopyMode = False
>    ActiveCell.FormulaR1C1 = "=DB!R[-14]C[-1]"
>    Range("C16").Select
>    ActiveCell.FormulaR1C1 = "=DB!R[-13]C[-2]"
>    Range("D16").Select
>    ActiveCell.FormulaR1C1 = "=DB!R[-12]C[-3]"
>    Range("E16").Select
>    ActiveCell.FormulaR1C1 = "=DB!R[-11]C[-4]"
>    Range("B17").Select
>    ActiveCell.FormulaR1C1 = "=DB!R[-15]C"
>    Range("C17").Select
>    ActiveCell.FormulaR1C1 = "=DB!R[-14]C[-1]"
>    Range("D17").Select
>    ActiveCell.FormulaR1C1 = "=DB!R[-13]C[-2]"
>    Range("E17").Select
>    ActiveCell.FormulaR1C1 = "=DB!R[-12]C[-3]"
>    Range("E18").Select
>End Sub
>
>--------------------------------------------------------------
>
>"Gord Dibben" <gorddibbATshawDOTca> escribi� en el mensaje de noticias 
>news:be5s04dnrnitqh48ac82ngt9ejeke1pe3s@4ax.com...
>> If once a month, automation should not really be required.
>>
>> But, this macro can be run to copy the sheet1 data to a new sheet.
>>
>> Sub select_transpose()
>>    Range(Range("A1:B1"), Cells(Rows.Count, 
>> Selection.Column).End(xlUp)).Copy
>>    Sheets.Add
>>    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, 
>> SkipBlanks:= _
>>        False, Transpose:=True
>>    Application.CutCopyMode = False
>>    Range("A1").Select
>> End Sub
>>
>>
>> Gord
>>
>> On Mon, 21 Apr 2008 08:15:14 +0200, "Olga" <noemail@hotmail.com> wrote:
>>
>>>The original sourse range is composed of two columns containing labels(A1)
>>>and numbers (euros B1).
>>>It's variable, every month a new row is added by the query.
>>>
>>>"Gord Dibben" <gorddibbATshawDOTca> escribi� en el mensaje de noticias
>>>news:74in045cci7i669u7aegrsqj56b7ii3196@4ax.com...
>>>> Sheet2 will not update itself using the manual transpose method we just
>>>> did.
>>>>
>>>> We will have to link the cells to the source range then tranpose those
>>>> links.
>>>>
>>>> Or write a macro to do the job after each query.
>>>>
>>>> What is your original source range that will be transposed?
>>>>
>>>> Is it consistent or variable range?
>>>>
>>>>
>>>> Gord
>>>>
>>>> On Sun, 20 Apr 2008 23:54:53 +0200, "Olga" <noemail@hotmail.com> wrote:
>>>>
>>>>>Thank you very much, it worked. But how would sheet 2 update itself when
>>>>>sheet 1 is updated refreshing the db connection?
>>>>>
>>>>>"Gord Dibben" <gorddibbATshawDOTca> escribi� en el mensaje de noticias
>>>>>news:n2bn04hjl0f2ddtlnf1goupbjj1ttnc01k@4ax.com...
>>>>>> "Surley I'm doing some wrong"
>>>>>>
>>>>>> Yes, you are<g>
>>>>>>
>>>>>> Re-read the instructions................I did not say to copy A1 from
>>>>>> sheet1
>>>>>>
>>>>>> Select the data range on sheet1 and copy.
>>>>>>
>>>>>> Select A1 on sheet2 and Edit>Paste Special>Transpose>OK>Esc
>>>>>>
>>>>>> No dragging needed.  You are paste/transposing the copied range, not
>>>>>> just
>>>>>> one
>>>>>> cell.
>>>>>>
>>>>>>
>>>>>> Gord
>>>>>>
>>>>>> On Sun, 20 Apr 2008 21:05:07 +0200, "Olga" <noemail@hotmail.com> 
>>>>>> wrote:
>>>>>>
>>>>>>>Thank you Gord,
>>>>>>>The data is coming from a SQL query and it is text and money values. 
>>>>>>>If
>>>>>>>I
>>>>>>>copy A1 from sheet 1 and paste it as transpose on sheet2 works 
>>>>>>>however,
>>>>>>>if
>>>>>>>I
>>>>>>>drag A1 horizontally to fulfill the rest of the cells then, the
>>>>>>>information
>>>>>>>is not coherent. Surly I'm doing some wrong.
>>>>>>>
>>>>>>>
>>>>>>>"Gord Dibben" <gorddibbATshawDOTca> escribi� en el mensaje de noticias
>>>>>>>news:hr2n04d0r9ld2dan3q3oj6ejm6anqh5dhf@4ax.com...
>>>>>>>> Copy the data from sheet1
>>>>>>>>
>>>>>>>> Select A1 of sheet2 and paste special>transpose.
>>>>>>>>
>>>>>>>> Note:  cannot be done if you have more than 16384 rows of data in
>>>>>>>> column
>>>>>>>> A
>>>>>>>>
>>>>>>>> Also, I think you have a typo at  A3 to B3.........maybe A3 to C1?
>>>>>>>>
>>>>>>>>
>>>>>>>> Gord Dibben  MS Excel MVP
>>>>>>>>
>>>>>>>> On Sun, 20 Apr 2008 19:55:12 +0200, "Olga" <noemail@hotmail.com>
>>>>>>>> wrote:
>>>>>>>>
>>>>>>>>>Hi,
>>>>>>>>>On sheet1 the data is oriented vertically as
>>>>>>>>>A1
>>>>>>>>>A2
>>>>>>>>>A3
>>>>>>>>>I'd like on sheet2 the data be oriented horizontally
>>>>>>>>>A1 to A1
>>>>>>>>>A2 to B1
>>>>>>>>>A3 to B3
>>>>>>>>>Can it be done? if so, how?
>>>>>>>>>TIA
>>>>>>>>>Olga
>>>>>>>>>Excell 07
>>>>>>>>
>>>>>>
>>>>
>> 

0
Gord
4/22/2008 11:23:43 PM
Hello Gord, thank you for your support.

Yes I did test your macro; in fact, thanks to you, I learned how to use 
them. I made a little change to your working macro so that it always points 
to the same datasheet (DB) and paste the copy in the same worksheet (TEST).

---------------------------------------

 Sub select_transpose()
   Sheets("DB").Select
   Range(Range("A2:B70"), Cells(Rows.Count, 
Selection.Column).End(xlUp)).Copy
    'Sheets.Add
    Sheets("TEST").Select
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, 
SkipBlanks:= _
        False, Transpose:=True
    Application.CutCopyMode = False
   'Range("A2").Select
 End Sub

------------------------------------

Ones pasted the information; I need to format the cells giving color, type 
of text, size. in few words, making it looking nice. Now, if I run the macro 
again, I lose all the cells formats, thus my work. Even if I need to do it 
once a month, why repeat all the work done? That's why I thought of my 
recorded macro which does leave the cells format intact updating only the 
containing data. I tried to format the datasheet so that the format can be 
pasted as well but it's not the same as formatting the worksheet itself, in 
my case. I hope to have explained myself well, English is not my native 
language and sorry for the time delay, I'm in Spain.

Olga



"Gord Dibben" <gorddibbATshawDOTca> escribi� en el mensaje de noticias 
news:0pss045e6ebiqdlpqlp2plenu2ctbb0i32@4ax.com...
> Did you try the macro I supplied?
>
> What alterations do you think it needs?
>
> Why do you think you need to link the cells if the changes are to be made 
> once a
> month?
>
>
> Gord
>
> On Tue, 22 Apr 2008 21:19:04 +0200, "Olga" <olgass@yahoo.com> wrote:
>
>>Hi Gord,
>>apparently I need to do my transformation manually or by recording  a 
>>macro
>>because otherwise, my cells options (borders, alignments, colors, etc) get
>>mist up when actualizing the data/ query. Using the below test micro, only
>>the data is updated leaving the cells format intact. My problems is that 
>>I've
>>over 100 cells (a + b = 200) to manually transform. So, perhaps, I need to
>>think of a loop.
>>------------------------------------------------------
>>Sub Macro2()
>>Sheets("TEST").Select
>>    Range("B16").Select
>>    Application.CutCopyMode = False
>>    ActiveCell.FormulaR1C1 = "=DB!R[-14]C[-1]"
>>    Range("C16").Select
>>    ActiveCell.FormulaR1C1 = "=DB!R[-13]C[-2]"
>>    Range("D16").Select
>>    ActiveCell.FormulaR1C1 = "=DB!R[-12]C[-3]"
>>    Range("E16").Select
>>    ActiveCell.FormulaR1C1 = "=DB!R[-11]C[-4]"
>>    Range("B17").Select
>>    ActiveCell.FormulaR1C1 = "=DB!R[-15]C"
>>    Range("C17").Select
>>    ActiveCell.FormulaR1C1 = "=DB!R[-14]C[-1]"
>>    Range("D17").Select
>>    ActiveCell.FormulaR1C1 = "=DB!R[-13]C[-2]"
>>    Range("E17").Select
>>    ActiveCell.FormulaR1C1 = "=DB!R[-12]C[-3]"
>>    Range("E18").Select
>>End Sub
>>
>>--------------------------------------------------------------
>>
>>"Gord Dibben" <gorddibbATshawDOTca> escribi� en el mensaje de noticias
>>news:be5s04dnrnitqh48ac82ngt9ejeke1pe3s@4ax.com...
>>> If once a month, automation should not really be required.
>>>
>>> But, this macro can be run to copy the sheet1 data to a new sheet.
>>>
>>> Sub select_transpose()
>>>    Range(Range("A1:B1"), Cells(Rows.Count,
>>> Selection.Column).End(xlUp)).Copy
>>>    Sheets.Add
>>>    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
>>> SkipBlanks:= _
>>>        False, Transpose:=True
>>>    Application.CutCopyMode = False
>>>    Range("A1").Select
>>> End Sub
>>>
>>>
>>> Gord
>>>
>>> On Mon, 21 Apr 2008 08:15:14 +0200, "Olga" <noemail@hotmail.com> wrote:
>>>
>>>>The original sourse range is composed of two columns containing 
>>>>labels(A1)
>>>>and numbers (euros B1).
>>>>It's variable, every month a new row is added by the query.
>>>>
>>>>"Gord Dibben" <gorddibbATshawDOTca> escribi� en el mensaje de noticias
>>>>news:74in045cci7i669u7aegrsqj56b7ii3196@4ax.com...
>>>>> Sheet2 will not update itself using the manual transpose method we 
>>>>> just
>>>>> did.
>>>>>
>>>>> We will have to link the cells to the source range then tranpose those
>>>>> links.
>>>>>
>>>>> Or write a macro to do the job after each query.
>>>>>
>>>>> What is your original source range that will be transposed?
>>>>>
>>>>> Is it consistent or variable range?
>>>>>
>>>>>
>>>>> Gord
>>>>>
>>>>> On Sun, 20 Apr 2008 23:54:53 +0200, "Olga" <noemail@hotmail.com> 
>>>>> wrote:
>>>>>
>>>>>>Thank you very much, it worked. But how would sheet 2 update itself 
>>>>>>when
>>>>>>sheet 1 is updated refreshing the db connection?
>>>>>>
>>>>>>"Gord Dibben" <gorddibbATshawDOTca> escribi� en el mensaje de noticias
>>>>>>news:n2bn04hjl0f2ddtlnf1goupbjj1ttnc01k@4ax.com...
>>>>>>> "Surley I'm doing some wrong"
>>>>>>>
>>>>>>> Yes, you are<g>
>>>>>>>
>>>>>>> Re-read the instructions................I did not say to copy A1 
>>>>>>> from
>>>>>>> sheet1
>>>>>>>
>>>>>>> Select the data range on sheet1 and copy.
>>>>>>>
>>>>>>> Select A1 on sheet2 and Edit>Paste Special>Transpose>OK>Esc
>>>>>>>
>>>>>>> No dragging needed.  You are paste/transposing the copied range, not
>>>>>>> just
>>>>>>> one
>>>>>>> cell.
>>>>>>>
>>>>>>>
>>>>>>> Gord
>>>>>>>
>>>>>>> On Sun, 20 Apr 2008 21:05:07 +0200, "Olga" <noemail@hotmail.com>
>>>>>>> wrote:
>>>>>>>
>>>>>>>>Thank you Gord,
>>>>>>>>The data is coming from a SQL query and it is text and money values.
>>>>>>>>If
>>>>>>>>I
>>>>>>>>copy A1 from sheet 1 and paste it as transpose on sheet2 works
>>>>>>>>however,
>>>>>>>>if
>>>>>>>>I
>>>>>>>>drag A1 horizontally to fulfill the rest of the cells then, the
>>>>>>>>information
>>>>>>>>is not coherent. Surly I'm doing some wrong.
>>>>>>>>
>>>>>>>>
>>>>>>>>"Gord Dibben" <gorddibbATshawDOTca> escribi� en el mensaje de 
>>>>>>>>noticias
>>>>>>>>news:hr2n04d0r9ld2dan3q3oj6ejm6anqh5dhf@4ax.com...
>>>>>>>>> Copy the data from sheet1
>>>>>>>>>
>>>>>>>>> Select A1 of sheet2 and paste special>transpose.
>>>>>>>>>
>>>>>>>>> Note:  cannot be done if you have more than 16384 rows of data in
>>>>>>>>> column
>>>>>>>>> A
>>>>>>>>>
>>>>>>>>> Also, I think you have a typo at  A3 to B3.........maybe A3 to C1?
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> Gord Dibben  MS Excel MVP
>>>>>>>>>
>>>>>>>>> On Sun, 20 Apr 2008 19:55:12 +0200, "Olga" <noemail@hotmail.com>
>>>>>>>>> wrote:
>>>>>>>>>
>>>>>>>>>>Hi,
>>>>>>>>>>On sheet1 the data is oriented vertically as
>>>>>>>>>>A1
>>>>>>>>>>A2
>>>>>>>>>>A3
>>>>>>>>>>I'd like on sheet2 the data be oriented horizontally
>>>>>>>>>>A1 to A1
>>>>>>>>>>A2 to B1
>>>>>>>>>>A3 to B3
>>>>>>>>>>Can it be done? if so, how?
>>>>>>>>>>TIA
>>>>>>>>>>Olga
>>>>>>>>>>Excell 07
>>>>>>>>>
>>>>>>>
>>>>>
>>>
> 

0
4/23/2008 7:14:53 AM
Reply:

Similar Artilces:

Changing Item Tracking To Enable Serial Number Tracking
Through GP you cannot change the tracking option if an item has any on hand or on order quantity amounts. Has anybody changed the tracking option outside of GP and can tell me the other steps that need to be taken to build the serial number inventory? Thanks -- Charles Allen, MVP Charles: First, you need to do a decrease adjustment to move the items out of stock. The, change the ITMTRKOP field in IV00101 to '2' for those items you want to track. Finally, perform an increase adjustment to bring the material back in and record the serial numbers. Then I'd run reconcile on ...

How do I copy data from one workbook to another whilst retaining formulas on original
I'm looking to run a macro from workbook SWT (sheet CLT) which will cu and paste data from a range of cells (A2,C2,E2,F2,G2,I2,K2.. A3,C3,E3,F3,G3,I3,K3... A4,C4,E4,F4,G4,I4,K4.... until end of data from workbook SWT (Sheet CS) to rows A to G in Workbook SWTS (Shee Stats). I have vlookup formulas (eg =IF(B2="","",VLOOKUP($B2,'Data Field'!$A$7:$B$12,2))) in place in columns A,C,E,F,G,I,K on sheet CS and I'm looking to keep the these formulas in place (unless someone ca think of better alternatives). When I paste the data on sheet Stats i must paste onto the...

formula change problem #2
The columns that the formula is referencing can change locations on th spreadsheet, if a user inserts a new column. To simplify the questio I put the actual column numbers in the formula but actually I will hav an integer defined that will hold the column number. That integer wil get populated by grabbing the column number from a named cell in tha particular column. I hope I explained that good enough. I was using the offset's in the formulas because that is what I am mos familiar with. Can offset's not be used in formulas? Is there a better way? Thanks -- cparson ---------...

Macro
Hi all!!! I am trying to get my macro to run a sort in a non specific cell for column D.....there are many steps up to this point, I will include the last couple in the code...here is what I have so far.... Columns("C:C").Find(What:="x").EntireRow.Insert Columns("C:C").Find(What:="x").EntireRow.Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select Selection.Sort Key1:=Range("D63"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientati...

excel spreadsheet 2003 wont show content when moved to another par
i have backed all documents which included excel spreadsheets from c drive to d drive (ANOTHER PARTITION). Now i cannot see the content of the spreadsheet when i access it from the D drive or C drive. what have i done to it? It also wont open when itry to access it from backed up USB stick. Thanks Chris I had a similar issue with some files. What happened was that somehow the windows for the files got minimized or dragged out of view somehow (not sure how this happened). Users would open a file and see nothing. This fixed it for me. Goto the Window item on toolbar, S...

Data fill question
Hi all, How do I do an auto fill on a certain amount of cells in a range of numbers but at the end of the number there is a dash s. For example: 343-s, 344-s,345-s and so on. I tried to do the auto fill but it is just repeating the sequence of numbers? You could use a formula. With the first cell filled in (like A1), put this in A2 and fill down: =(LEFT(A1,FIND("-",A1)-1)+1)&"-s" HTH Jason Atlanta, GA >-----Original Message----- >Hi all, >How do I do an auto fill on a certain amount of cells in a range of numbers >but at the end of the number the...

[P2007]
Hi, I have a PM user who has just changed the login account password in AD and after that he is no longer able to login to PWA, an access denied message will show up instead. Even if I assign him as an administrator, he is still getting the same problem. He has no issue using Project Professional (with his account) to connect to the project server, it is just the PWA that he is unable to access. Did anyone encountered the same issue before? Regards Godrid Godrid: Most likely his desktop is sending the incorrect credentials. Verify that the user didn't save the passw...

Data Analysis
Hi, In Excel 2000 I used a feature called Data Analysis. Now I have Excel 2003 and I cant find this feature anywhere?? Any help appreciated. thanks, Paul Load the Analysis Toolpak Add-in (Tools/Add-ins...). In article <070401c4d7f5$3f64da70$a401280a@phx.gbl>, "Paul" <anonymous@discussions.microsoft.com> wrote: > Hi, > > In Excel 2000 I used a feature called Data Analysis. Now I > have Excel 2003 and I cant find this feature anywhere?? > > Any help appreciated. > > thanks, > > Paul ...

Put data rows per sheet
I have an Excel workbook with 2 sheets. One of them is for data entering (e.g. rows: employee's name, ID#, working dates, fee etc.) and the other presents that data among other constant value cells (e.g. name of company, titles of columns, place for signatures etc.). What I want is when data in the presentation sheet reach at 22 rows to be automatically continued to the next page of the sheet. So, each printed page will always shows 22 rows of data with the constant data ABOVE and BELOW of these 22 rows. Thanks, Hi not fully automatic but try the following two macros (assumption: row 1 ...

company name change
Hello Our company has recently changes names. We already have two email domains currently in use, one set as primary for everything and another which was used in the past. We have one defined SMTP address, which is primary for all users. We also have another which was used int he past, but is not listed as a SMTP address in EXCH system mgr, recipients policies. Of course, I have a new domain to add as our primary smtp address and will need to make the current smtp address secondary for users. In EXCH system mgr, I do see CCMAIL and MS properties which appear to be our old email domain...

how do i change the color of a cell auotmatically?
I am desiging a data shee and i want to changes the color of a cell based on the valuse of anthor cell. ex. if "cell m34" is equal to 0 then "cell p21" is white, if cell m34 less than 1548, then "cell P21" is yellow Can this be done? If so, please help me out. Hi! Select cell P21 Goto Format>Conditional Formatting In the dropdown select Formula is Enter this formula in the box: =AND(M34<>"",M34=0) Click the Format button and select the style to apply then click OK. Click the Add button In Condition 2 Formula is: =AND(M34>=1,M34<=154...

graph data from multiple worksheets
I have about 10 worksheets with data in them, I would like to put it all on one chart, how do I do it? I cannot fit it all in one worksheet. Produce the chart from the data from your first workshet. Go to your second worksheet, select the relevant data, copy, go to your chart, edit/ paste special, and accept the relevant option to add a new data series or add data to an existing series. Alternatively having produced your original chart, go to Source Data, and either adding data to an existing series or adding a new series will allow you to select the relevant data from whichever work...

Is it possible to have text and currancy in one cell and ...
Is it possible to have text and currancy in one cell and be able to use the numbers in that cell to make an equation with another? The short answer .. No -- Rgds Max xl 97 --- Please respond in thread xdemechanik <at>yahoo<dot>com ---- "thegen" <thegen@discussions.microsoft.com> wrote in message news:1552FF10-9CAA-42AD-B401-B0D229D6A343@microsoft.com... > Is it possible to have text and currancy in one cell and be able to use the > numbers in that cell to make an equation with another? well maybe depending on how the "text & currency" was ar...

Segrigation of different datas from one colum
Hi , I am trying to segrigate and pull data from one colum to different colums. For this I want to created a macro . Also I want this data to be pulled from a word document and to be pasted on an Excel template.Is it possible ??then How can we do it . Hi Roy- Providing a sample of the type of data you are working with (whatever you want to segregate, also called "parsing") would be helpful in order to provide you more direction. You can definitely extract data from a Word document, but how efficient it is will vary depending on how your Word document was set up...

How to Route Outbound E-mail from the Exchange Server 5.5 to another server.
Hi. I'm implementing an e-mail filter software and I need to configure my Exchange Server to route outbound e-mail to the e-mail filter server. I went to the connection tap and changed the message delivery to : Forward all message to host : "My e-mail filter server ". I need to know if what I've done is correct ? Thank you for your help. Yes, that's where you make the change. Don't forget to restart the IMS in order to affect the change. -- hth, SusanV "Javier" <jbisono@tricom.com.do> wrote in message news:uPVHjhl9DHA.1472@TK2MSFTNGP11.phx.gbl...

Inserting a value based on a calculation from another sheet
Hi all I have a sheet that performs a series of calculations based on a single value and culminating in another single value. Also the series of calculations is too complicated and long and numerous to do in a single calculation. What I have is, in another sheet, I have a range of that single initial value, from -90% to +90% of the initial value, and I'd like to somehow calculate that value resulting from all the various initial values and insert them in the first sheet...how can i do that? for example: sheet A contains the range of initial values and the result of the calculations per...

changing calendar appearance
I can not get the calendar day of the week to start on Sunday. Version of Outlook? Are you using Compressed weekends? You need to disable compressed weekends if you want Sunday at the beginning of the week. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips by email: mailto:dailytips-subscribe-request@lists.outlooktips.net EMO - a weekly newsletter about Outlook and Exchange: mailto:EMO-NEWSLETTER-SUBSCRIBE-REQUEST@PEACH.EASE.LSOFT.COM New Poll: What type of email acc...

Why does Outlook display text in yellow? How can i change it?
When pasting text into an e-mail, it appears in pale yellow. Sometimes, parts of meesages from others are also yellow. How can I prevent this? Are you using stationery by any chance? -- Kathleen Orland "Wise Dog" <Wise Dog@discussions.microsoft.com> wrote in message news:2D2FBAF7-2B2B-4C66-AE33-B2CA5A51B821@microsoft.com... > When pasting text into an e-mail, it appears in pale yellow. Sometimes, > parts of meesages from others are also yellow. How can I prevent this? ...

5000 character limit in data fields
Is there any way to extend the 5000 character limit for data fields? I have one field that will be at least 10000 characters regularly. It appears that I cannot make a custom field larger then 5000 characters though even with the ntext format. Just wanted you to know that we have just created a 5000 character ntext field in the Leads entity and get an error going offline with that data. Microsoft is working on a fix for this, but in the meantime we're stuck. Can you use the Notes Entity? "fbell@itstrategists.com" wrote: > Is there any way to extend the 5000 character...

Moving in sheet
I want to tab over x number of columns left or right to a specific cell. on a number of worksheets. the number of columns to move over is constant Hi maybe the followng helps: http://www.xldynamic.com/source/xld.xlFAQ0008.html -- Regards Frank Kabel Frankfurt, Germany "JPD" <anonymous@discussions.microsoft.com> schrieb im Newsbeitrag news:305001c4a7e1$efa0f5c0$a601280a@phx.gbl... > I want to tab over x number of columns left or right to a > specific cell. on a number of worksheets. the number of > columns to move over is constant ...

Coping numbers to next blank cell on another worksheet
I am just learning Excel (need SIMPLE help) but any help would be greatly appreciated. My OS is XL Professional and I have MS Excel 2002 SP2. I need to copy calculated numbers (I got this much done on my own) in specific cells on worksheet # 1 to the next blank cells in existing columns of numbers used in calculations on other worksheets in the same workbook. I other words, I have a formula that calculates numbers and places the results in the same column and row each time it is used. Those calculated numbers need to be copied and used on different worksheets in the same workbook. While...

Scrolls Far Below Actual Data
I have a spreadsheet in which the moving piece in the vertical scroll bar is very narrow and, if you move it to the bottom, you have gone far below the bottom of the data. I tried deleting all rows (i.e. entire rows) between the end of my data and the scroll end, but this didn't seem to have any effect. Then I tried deleting rows all the way to the bottom of the spreadsheet. This didn't help either. Any ideas on why this is happening or how to fix it? Thanks in advance, Will Hello roadkill- Try this... Select all rows below your data, go to Edit>Clear>All, then close...

Split combined date time data #2
From file dump have combined date time cells eg "14/04/03 14:20" (value 37725.59722). Want to perform time analysis so need to split to 2 separate cells; eg. "14/04/2003" (value 37725) & "14:20" (value 0.59722). Is there a fnc to do this? (Currently convert cell to value, then fncs trunc & cell less int(cell) then refmt to date dd/mm/yy & time hh:mm respectively) See one answer in reply to your slightly later post. In article <EDD7065A-E5AA-40AB-BF38-50707E04109B@microsoft.com>, "Mark Ada" <MarkAda@discussions.microsoft.co...

Sheet tab text color
...

Changing the data label position below the chart axis.
I'm charting some percentages. Many of them are negatives, but not all. The Y axis in my chart is more in the middle, instead of on the bottom, as with normal charts that have all positive numbers. The data labels are overlapping some of bars in the chart that are negatives. I can't get the data labels to move any further south. I've already changed the 'data label distance from axis' to the maximum of 1000. The data labels are still overlapping. Is there anything else I can try? The chart looks terrible with the labels partially on the negative bars, please help! H...