Q: columns to rows

[JIM.H.]

Hello,

Sheet1 has column based data such as there are values in A1,B1,C1,..
Now I need to create Steet2 that holds row based data such as
A1->A1,B1->A2,C1->A3 and change the background color of A1,A2,A3, if the
data in A1,B1,C1 in Sheet1 is zero.
How can I do this?
Thanks,

[Phillycheese5]

Jim,
Select A1:Z1 and copy it, then select cell A1 on the second sheet. Use paste special (check the transpose box). That flips your data from horizontal to vertical so it's in rows.
To format the cells, go to Format: Conditional formatting. If the cell value is "equal to" then specify "0" and click the format button to select the color of the pattern you want.
Hope it helps,
Phillycheese

[JIM.H.]

Hi Phillycheese5
Thanks for your response. However, I need to keep link between sheet1 and
new sheet2. If the data has changed in sheet1, it should be changed in sheet2
too.


"Phillycheese5" wrote:

>
> Jim,
> Select A1:Z1 and copy it, then select cell A1 on the second sheet. Use
> paste special (check the transpose box). That flips your data from
> horizontal to vertical so it's in rows.
> To format the cells, go to Format: Conditional formatting. If the cell
> value is "equal to" then specify "0" and click the format button to
> select the color of the pattern you want.
> Hope it helps,
> Phillycheese
>
>
> --
> Phillycheese5
> ------------------------------------------------------------------------
> Phillycheese5's Profile: http://www.excelforum.com/member.php...o&userid=24196
> View this thread: http://www.excelforum.com/showthread...hreadid=491051
>
>

[Sloth]

=Sheet1!A1
=Sheet1!B1
=Sheet1!C1
etc.
to change the background color use conditional formating select "equal to"
and insert 0. Then click format and choos the color you want.

"JIM.H." wrote:

> Hi Phillycheese5
> Thanks for your response. However, I need to keep link between sheet1 and
> new sheet2. If the data has changed in sheet1, it should be changed in sheet2
> too.
>
>
> "Phillycheese5" wrote:
>
> >
> > Jim,
> > Select A1:Z1 and copy it, then select cell A1 on the second sheet. Use
> > paste special (check the transpose box). That flips your data from
> > horizontal to vertical so it's in rows.
> > To format the cells, go to Format: Conditional formatting. If the cell
> > value is "equal to" then specify "0" and click the format button to
> > select the color of the pattern you want.
> > Hope it helps,
> > Phillycheese
> >
> >
> > --
> > Phillycheese5
> > ------------------------------------------------------------------------
> > Phillycheese5's Profile: http://www.excelforum.com/member.php...o&userid=24196
> > View this thread: http://www.excelforum.com/showthread...hreadid=491051
> >
> >

[JIM.H.]

Hi Sloth,
Thanks, this is great help. This takes row=1, if I need row=2, I want to
type 2 somewhere in this Sheet 2 and get second row from Sheet1 in the same
column in sheet2, is there any way I can change '=Sheet1!A1' to make it
dynamic for row?


"Sloth" wrote:

> =Sheet1!A1
> =Sheet1!B1
> =Sheet1!C1
> etc.
> to change the background color use conditional formating select "equal to"
> and insert 0. Then click format and choos the color you want.
>
> "JIM.H." wrote:
>
> > Hi Phillycheese5
> > Thanks for your response. However, I need to keep link between sheet1 and
> > new sheet2. If the data has changed in sheet1, it should be changed in sheet2
> > too.
> >
> >
> > "Phillycheese5" wrote:
> >
> > >
> > > Jim,
> > > Select A1:Z1 and copy it, then select cell A1 on the second sheet. Use
> > > paste special (check the transpose box). That flips your data from
> > > horizontal to vertical so it's in rows.
> > > To format the cells, go to Format: Conditional formatting. If the cell
> > > value is "equal to" then specify "0" and click the format button to
> > > select the color of the pattern you want.
> > > Hope it helps,
> > > Phillycheese
> > >
> > >
> > > --
> > > Phillycheese5
> > > ------------------------------------------------------------------------
> > > Phillycheese5's Profile: http://www.excelforum.com/member.php...o&userid=24196
> > > View this thread: http://www.excelforum.com/showthread...hreadid=491051
> > >
> > >

[Sloth]

=INDIRECT("Sheet1!R"&COLUMN()&"C"&ROW(),FALSE)

The formula essentialy switches the row and column (ie. C7 will link to F3
of Sheet1).

You can shorten it, in your case by using

=INDIRECT("Sheet1!R1"&"C"&ROW(),FALSE)

This will only look at row 1. (ie. B7 will link to E1 of sheet1).

The INDIRECT function should point you in the right direction. Let me know
if you need more help. You can't do a whole row at a time without copying
and pasteing the formula, unless you use a macro (can't help you there).

"JIM.H." wrote:

> Hi Sloth,
> Thanks, this is great help. This takes row=1, if I need row=2, I want to
> type 2 somewhere in this Sheet 2 and get second row from Sheet1 in the same
> column in sheet2, is there any way I can change '=Sheet1!A1' to make it
> dynamic for row?
>
>
> "Sloth" wrote:
>
> > =Sheet1!A1
> > =Sheet1!B1
> > =Sheet1!C1
> > etc.
> > to change the background color use conditional formating select "equal to"
> > and insert 0. Then click format and choos the color you want.
> >
> > "JIM.H." wrote:
> >
> > > Hi Phillycheese5
> > > Thanks for your response. However, I need to keep link between sheet1 and
> > > new sheet2. If the data has changed in sheet1, it should be changed in sheet2
> > > too.
> > >
> > >
> > > "Phillycheese5" wrote:
> > >
> > > >
> > > > Jim,
> > > > Select A1:Z1 and copy it, then select cell A1 on the second sheet. Use
> > > > paste special (check the transpose box). That flips your data from
> > > > horizontal to vertical so it's in rows.
> > > > To format the cells, go to Format: Conditional formatting. If the cell
> > > > value is "equal to" then specify "0" and click the format button to
> > > > select the color of the pattern you want.
> > > > Hope it helps,
> > > > Phillycheese
> > > >
> > > >
> > > > --
> > > > Phillycheese5
> > > > ------------------------------------------------------------------------
> > > > Phillycheese5's Profile: http://www.excelforum.com/member.php...o&userid=24196
> > > > View this thread: http://www.excelforum.com/showthread...hreadid=491051
> > > >
> > > >