Q: columns to rows

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,

0
JimH (65)
12/6/2005 1:49:03 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
599 Views

Similar Articles

[PageSpeed] 19

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

--
Phillycheese
-----------------------------------------------------------------------
Phillycheese5's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2419
View this thread: http://www.excelforum.com/showthread.php?threadid=49105

0
12/6/2005 3:04:51 PM
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?action=getinfo&userid=24196
> View this thread: http://www.excelforum.com/showthread.php?threadid=491051
> 
> 
0
JimH (65)
12/6/2005 3:32:02 PM
=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?action=getinfo&userid=24196
> > View this thread: http://www.excelforum.com/showthread.php?threadid=491051
> > 
> > 
0
Sloth (218)
12/6/2005 3:43:04 PM
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?action=getinfo&userid=24196
> > > View this thread: http://www.excelforum.com/showthread.php?threadid=491051
> > > 
> > > 
0
JimH (65)
12/6/2005 3:56:06 PM
=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?action=getinfo&userid=24196
> > > > View this thread: http://www.excelforum.com/showthread.php?threadid=491051
> > > > 
> > > > 
0
Sloth (218)
12/6/2005 4:30:04 PM
Reply:

Similar Artilces:

=?Utf-8?Q?Hvordan_=C3=A5bnes_en_csv-fil_i_excel?= =?Utf-8?Q?_2007_=28Office_til_hjemmet_og_stu?= =?Utf-8?Q?di?=
Mit blodtryksapparat leverer data i form af en csv-fil. I min tidligere udgave af Excel (2000) gav det ikke problemer (heller ikke i OpenOffice); men i Excel 2007 åbnes filen i én kolonne - dvs. jeg får ikke oplysningerne fordelt ud i regnearket, så de kan bruges til beregninger af fx gennemsnit. Davs JCTC! Klikk på "Data" Velg ikonet "Fra Tekst" på venstresiden. Du må finne frem til .csv-filen på harddisken. En Wizard som kalles "Text import wizard" kommer frem. Velg "Delimited" og trykk neste. Velg "C...

I've put data to columns - counted
I've got a two columns spreadsheet with cells which have more than one set of data in them. I've put column A from text to columns and managed to count it, but now need to find out how to add the filter button at the top of the column which enables me to filter according to number of names i want to see. It is a name and address spreadsheet but the names and addresses are mixed up, with some cells containing more than 5 names (column a) and some more than 5 addresses (column b). i've sorted column a, but cannot find a unique separator for column b. please help me!!! ...

Column wrapping/formatting/snaking with either Excel or Excel + Word.
Looking for information regarding the possibility of having two columns wrap up to the next two columns to fit a certain page size. Would like to have two sets of columns per page, two pages per sheet. Ideally, I would like these all to wrap or be linked so the end of the first set of columns moves to the beginning of the second set of columns on page 1, then the end of the second set of columns on page 1 becomes the beginning of the first set of columns on page 2 and so on. Is this sort of linked column wrapping possible? How about with Word? In a text layout program like Quark or Illust...

TotChng Column Not Updating for Some Funds
Has anyone noticed the TotChng cloumn not updating for some investments the last few days? The Quantity, Change, %Change, Market Value columns ARE updating...and this is not happening for all investments, just some. I noticed this earlier this week, and it is always the same investments. At first I thought it may be a repeat of the issue we had a few weeks ago, so I just monitored the situation. I have notice this for the past three days. The column showing today's increase/decrease is showing correctly for all investments, but the new price is not being updated and therefore the ...

Row & Column headers
We just had Excel 2000 installed on one of our PC screens at work, and although the row and column headers look OK at first, when you click on a cell the respective column and row header, instead of just turning bold, turns italic, bold and strikethrough (yeah, go figure) as well! Is something corrupted or has this been set up specifically, and if so how can I change it? I have tried the font options but they just say "Normal". Indeed, the font *is* normal until you click on that row/column. Steve Wylie Have a look at the defined styles in your default workbook. My own ...

Sum multiple vlookup returns across multiple columns
Hi All, I need to sum the results of all occurences of 'Brand A' across 4 categories; $, QTY, $YA, QTY YA table would look like this: A B C D E Brand $ Qty $YA QtyYA Brand1 10 2 20 4 Brand2 45 45 10 12 Brand1 20 4 10 4 I need a formula in a cell that sums Brand1 by $ (should=30), A seperate cell should read by Qty (should=6) etc, etc, I was certain that I had done this before with sumproduct, but using the same formula isn't working. Also I seem to re...

How do I sum a column based on another column
I am trying to sum grades for a class. When recording the grades I have a grade for in class and one for homework so the column headings alternate between the date, HW(for homework) and a possible column for projects. I want to total each of the three types of grades seperately so I need to use the column heading to determine if the column is added and it is fine for the HW and project columns but I want to write the equation so that the in class grade is added if the heading is a date. I hope this makes sense. Hi, You could use the fact that dates (column headings for InClass grade...

Sorting & Combining Rows
I am trying to figure out how I can sort these rows and then combine the like rows. Obviously, I am not describing it well, but this is what I want: Start with: Column (A) Qty per system; (B) # of Systems; (C) Total Qty (D) Part #; (E) Part Description 1 2 2 W0000 Widgets 1 3 3 G0000 Gidgets 1 4 4 D0000 Didgets 1 2 2 W0000 Widgets 1 2 2 W0000 Widgets 1 3 3 G0000 Gidgets 1 4 4 D0000 Didgets 1 2 2 W0000 Widgets I can sort and end up with: 1 2 2 W0000 Widgets 1 2 ...

Formating rows and columns
Is there an easier way to format columns, to total 4 columns over a 30 row span? I need to create a cost tracking system to analyze cost vs: budget over a 30 day period containing four items; Material, Labor, Equipment, and Contingency. Having to format the" total" columns for each row is very time consuming. You have four columns of numbers Lets say the first row is B2,C2,D2 and E2 In F2 type =SUM(B2:E2) or use the AutoSum tool (Greek symbol bit like E) Click on F2 and look in its lower right corner - small black square is called "fill handle" Either drag this dow...

Another Synchornizing Subforms Q
Okay I have a main form with 2 continous subforms. Subform1 - sbfmClientSearch with a wildcard search by client field. I type "Toy" I get every client that contains this text. I placed a check box so I can select the exact client I want to see details in Subform2. Subform2 - sbfmCustDetail I'm still struggling with the check box and linking the master/child fields. Is this possible? Any suggestions. Create an invisible (visible for testing) TextBox on the MainForm. Set the ControlSource to SubFormControl1Name.FORM.IDControlOnThisForm The ID should be availabl...

only the first 5 columns of a 10 column excel spreadsheet sort
How do I get the whole spread sheet to sort? There is a blue lox for the first 5 columns that limits the range of the sort. How do I remove it? Using Office 2003. Maybe if you remove the Data|list Select a cell in that blue box. Data|list|convert to range jrw562 wrote: > > How do I get the whole spread sheet to sort? There is a blue lox for the > first 5 columns that limits the range of the sort. How do I remove it? > Using Office 2003. -- Dave Peterson ...

Calendar in two columns or specified range
I have used the following which works fine except that I would like to have the calendar available for two specific columns or a specified range. What is the syntax to replace the "If Target.Column = 1" to accomplish this. > This is a reprint of a post from Ron de Bruin last year on this subject > > ============================================== > > Do Insert-Object from the menubar and place a calendar control on your > sheet. > If you select a cell in Column A the calendar will popup and when > you DblClick on the calendar the date will be placed in the...

Find second blank row #2
Can someone edit this so that it will find the second blank row instea of the first blank row? Thanks Function FindBlankRow() x = 2 Do While ActiveSheet.Cells(x, 1) <> "" x = x + 1 Loop FindBlankRow = x - -- singlgl ----------------------------------------------------------------------- singlgl1's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2638 View this thread: http://www.excelforum.com/showthread.php?threadid=48836 Function FindBlankRow() As Integer Dim First As Boolean First = True For i = 1 To Rows.Count If IsEmpty(Cells(...

q-q plots
I originally posted this in the excel.misc group but received no replies. I then found this group and it makes more sense to post here (I hope)... Is there anyone who can tell me please how to make quantile-quantile plots in excel? I have two sets of data (one with 500 records, one with 300) and I want to make a q-q plot of them. I am not sure how to handle data sets that do not have equal numbers of records. Thanks Chris Inquirer, Here's a site that might help. It notes that the sample sizes do not need to be the same: http://www.itl.nist.gov/div898/handbook/eda/section3/qqplot.ht...

How do I convert rows into columns?
J C's Generator Starter & Alternator Co 1160 Providence Rd Scranton, PA 18508 570-347-0861 A J's Auto Clinic 1430 Main Ave Ste Rear Scranton, PA 18508 570-343-7828 AUTO REPAIR Aamco Transmissions 1230 Keyser Ave Scranton, PA 18504 570-969-1940 AUTO REPAIR "Rick" <Rick@discussions.microsoft.com> wrote in message news:7F66082C-9DBE-42BE-B02C-B96805172660@microsoft.com... >J C's Generator Starter & Alternator Co > 1160 Providence Rd > Scranton, PA 18508 > 570-347-0861 > > > A J's Auto Clinic > 1430 Ma...

Auto Fit Row and Columns
Greetings, I am using Excel 2002 and the auto fit feature does not seem to work. I am using wrap text in the row. After pressing enter, only a portion of the cell contents show. I tried autofit but it does not work. I thought I would be able to type and hit enter and the row height would adjust automatically. I tried to double click between the rows but that does not work. Is there a Options setting I need to turn on or something? Thanks in advance. Mike Autofit will not work on a merged cell, nor when the text length gets over about a thousand characters. Maybe you have one of these condi...

Excel bug when reordering query columns?
Appears to be an excel bug (using excel 2002) Anyone know a workarround? If I insert a database query using "New database query" and then later edit the query (Edit Query context menu option on the query), and insert one more column and re-order the columns (in Query Wizard: Choose Columns), the new column order is NOT reflected in the speadsheet. Excel will force the newly added column to go as the last column. Any way to work-arround it? The columns are unlocked, if it matters. Currently my workarround is to delete the inserted query and re-insert it again. Sort of defeats the pu...

Cells to columns
Hi, Easy I know, but i just can't get. I have a list of names, A1:A100, and i want these to run across the top of my sheet as column titles. i.e. A1:CV1 can any one help. Thanks Craig Select A1:A100 Edit|Copy Select B1 (can't use A1) Edit|paste special|transpose Delete column A if you don't need it. craiglittleperth@aol.com wrote: > > Hi, > > Easy I know, but i just can't get. > > I have a list of names, A1:A100, and i want these to run across the > top of my sheet as column titles. i.e. A1:CV1 > > can any one help. > > Thanks > ...

Looking to automatically "unhide" rows in excel using hyperlinks
Using Excel 2000 and want to hide rows then have those rows automatically displayed if a hyperlink is selected. ...

Matching up two columns with different data
Okay, so here's my dilemma. I have 2 different systems that I am trying to match up. System A has about 1600 lines, system B has about 1000. A is the older system and has a lot of lines that don't matter much anymore. B has some things that aren't in A, but doesn't need a lot of the stuff in A. I'm trying to get the descriptions from A into B. So, here's what I've got. Columns A & C are item number, B & D are description. I'm trying to match up the columns by item number. Example: A B C D 1 glass 1 3 metal 2 ...

Search in multiple columns using VLOOKUP?
Hi, I'm looking for a formula to find a row in a spreadsheet with two matching fields which returns the value of a field in a certain column in that row. VLOOKUP only supports searching in one column as far as I know. Let me give you an example to clarify my request. Please read the following text in a non-proportional font like Courier New. Worksheet A A B C D 0 4 7 0 3 6 1 0 7 5 0 6 Remarks: either column A or B is always zero, and the other one non-zero. I need the formula to use the value of the field which is non-zero. Further on, the value of the column C has to match...

2 columns -> 1 column
Hello all I have 2 columns and i want to make a 1 column as follows: a b a c d b e f => c g h d i j e .. f .. g .. h .. i .. j Any help would be appreciated. Danny Hi Danny, On another worksheet you can refer to the first by it's s...

How do I get rid of extra white space when row autofit is on?
When row autofit is on, I get a lot of extra white space above or below text in the cell with the most text. How do I get rid of it without manually adjusting every row? I don't get it for all the cells, but this might work for you... make the columnwidth just a bit wider--then autofit the rowheights. (It might just make the problem occur on different cells, though.) Jane wrote: > > When row autofit is on, I get a lot of extra white space above or below text > in the cell with the most text. How do I get rid of it without manually > adjusting every row? -- Dave Pe...

how do you hide letter/numbers column/row?
Hi all Basically as it says in the subject. How do you hide the numbers and letters in grey - your column/row identifiers. Cheers Paul tools>options>view> -- Don Guillett SalesAid Software donaldb@281.com "paul thomas" <paul.thomas5@ntlworld.com> wrote in message news:RKhBc.190$8V4.187@newsfe5-gui.server.ntli.net... > Hi all > > Basically as it says in the subject. > > How do you hide the numbers and letters in grey - your column/row > identifiers. > > Cheers > > Paul > > Paul Tools>Options>View. Uncheck "row...

Inserting new rows, which contain the formula in the above the new
I am not very technical when it comes to excel, and am creating a sheet. I have various information in each column, and in two of them I have formula eg - (on row 1) A1, B1 has personal info . C1, D1 have formula inserted, which work I have copied the formula/ data down to say Row 18. when I insert a new row inbetween rows 1 & 18 the formula is not copied down/ up eg - (on row 2) A2, B2 I want to remain blank, so I can input the data C2, D2 I want the formula automatically copied. How do I do this? Please help, and please try and keep it simple I'm not very compu...