Reply:

Top row of excel stays even in sorting
I need a header in the top row of my excel worksheet so that even when I sort the data the top row remains the same. 2003:- Data>>Sort>>My Data Range As>> Check the “Header Row” Option Button. 2007:- Press Alt+D+S the Sort Dialog Box will appear Press Alt+H which will select the “My Data has headers” check box. -- Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "willyd" wrote: > I need a header in the top row of my excel worksheet so that even when I sort > the data the top ro...

How to protect data in rows from being seperated during sort
I have a huge spreadsheet that needs to be sorted a million times but several people. The problem is that during some of the sorts they don't always grab the whole row and the data then gets scrambled. how can I protect the sheet so that the rows can be moved and sorted and edited but the data within the rows always stays together If your sheet is not too dynamic, and tends to stay the same size, then simply create a named range. Select *all* the rows and columns concerned. Click in the "Name box", and enter a short appropriate name, like "sort" (no quotes), an...

Finding the last row
Hi gurus - I've posted this before but none of the responses I received worked properly, so maybe I didn't state my question clearly. Here goes again: In my spreadsheet I need to add two cells: one is in the last row of column C and one is 3 rows above it. So, essentially, I'm looking to add C{last row} + C{last row -3}. I can't figure out what function to use that will automatically figure out what the number of the last row is. Can anyone shed some light? TIA, Craig First, define the following... Insert > Name > Define Name: BigNum Refers to: =9.999999999...

repeating number in a data
hiii sir i am working in a excel with 10,000 number of data...... these are the customers numbers and such time it will be repeated like that First Name 9936615220 9336963152 9818141525 9798415632 9838810086---repeated 9026929808 9336931125-- 9838200296 9336931125-- 9838810086---repeated i want to find and destroy the no.. give me a best way.......thankyou I would add a helper column with a formula like: =countif(a:a,a1) and drag down Then I could filter this column to show the values that are greater than 1 and delete those visible rows. This formula will mark e...

Column Auto Width and Hidden Rows
I have a macro that selects certain columns and auto sets the width. The problem is it is using data in rows that are hidden. How can I make it ignore these rows when selecting the width? Thanks, KWCounter I'm not sure you can. But you could copy that column (visible cells only) to a new sheet (in column A), autofit that column, keep track of the width and use it on the original column. Option Explicit Sub testme() Dim curWks As Worksheet Dim tempWks As Worksheet Dim rng As Range Dim myWidth As Double Set curWks = ActiveSheet '??? Set tempWks = Workshee...

Automatically Hiding Rows when certain cell value has been selecte
I am trying to hide rows 13-16 when a certain value has been selected from a named range that contatins a drop down list. Any help will be greatly appreciated. Regards -- Jason Select the sheet tab which you want to work with. Right click the sheet tab and click on 'View Code'. This will launch VBE. Paste the below code to the right blank portion. Get back to to workbook and try out. -Change the range and the text string to suit your requirement Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Not Application.Intersect(Tar...

Pivot tables 2 row labels
I am trying to create a pivot table and want to have sales rep names and company as the rows. Is there a way to have the sales rep name repeat everytime with the company instead of how it looks below? I am going to have to use the output of the sheet to do a vlookup on another sheet and in this format I can't make it work. Thanks! Row Labels Jane Doe Company 1 John Doe Company 2 Company 1 Company 3 Company 5 Nope--not if you want to keep it a pivottable. But you could convert to values (or copy|paste values in a different locatio...

Excel columns to row
I have a spreadsheet with data arranged like: Case ID+ Priority* Type* Item* Group+ Create Date It is 75 Rows long. I need to convert the data to look like the following: Case ID+ Priority* Type* Item* Group+ Create Date Case ID+ Priority* Type* Item* Group+ Create Date Case ID+ Priority* Type* Item* Group+ Create Date On Sun, 7 Sep 2008 08:28:01 -0700, dr <dr@discussions.microsoft.com> wrote: >I have a spreadsheet with data arranged like: >Case ID+ Priority* Type* Item* Group+ Create Date > >It is 75 Rows long. > >I need to convert the data to look like the...

how to delete a row in excel with a specific word using visual basic
Hi, I have an excel file that always come with a line at the end with "downloaded:" in the cell. How can I write a macro to search for that cell and delete the row that cell is in? Thanks for all your help! Any time you are in doubt on how to interact with excel, try recording a new macro and then physically do the steps you are trying to code. Stop the recording and then go look at the recorded macro www.jameswesleybluesband.com Hi "mchen" mchen@gellerco.com, Delete rows with "N" in Column 31 (col AE) from my http://www.mvps.org/dmcritchie/excel/delem...

GET UNIQUE ROWS FROM ONE PAGE TO ANOTHER
I track jobs for techs page one example. Excel 2003 JOB TECH ACCT# STAT ADDRESS 1B 603 162395-7 CP 6844 N DE CHELLY LOOP 3B 617 164655-11 CP 10700 N LA RESERVE DR # 2106 5B 603 267454-1 CP 7270 S SAND DUNE VLY D 4B 634 131976-3 CP 5702 N CAM LAGUNA 3B 609 193005-2 CP 4961 N DIAMOND PL 16B 650 267451-1 XO 9950 N CAM DEL PLATA 1B 636 206822-7 CP 7525 W SUMMER SKY DR 10B 607 120813-8 CP 3028 W WYOMING ST 2B 609 100114-2 RS$ 6430 N MONTROSE DR 3B 603 168731-9 CP 4156 N RIO CANCION # 33 3B 626 231452-8 CP 7990 E SNYDER RD 11 107 I want to take all of ie 603 info in...

Converting Header Rows to Add'l Detail in Rows
I am working with a spreadsheet created from our mainframe and need some assistance in converting it to a file easily used for data mining. The records are numbered by 1,2,3 ; where 1=header row, 2=detailed records for each header row, 3=sum count of the # of detailed records. Here is a sample of the data: A B C D E F 1 Test Texas PlanNumber RedLight 2 John Addy State ZipCode Expense 2 Sally Addy State ZipCode Expense 2 Jake Addy State ZipCode Ex...

How do I stop Word putting text at bottom of page onto next page?
Word pushes some text at the bottom of the page onto the next page, thereby creating a gap at the bottom of the page. How do I get Word to stop doing this? The following settings may be relevant in such a case: "Keep with next," "Keep lines together," and "Widow/orphan control" (all found in the Paragraph dialog box). Note, however, that the best way to deal with this depends on what kind of a document you are creating. Is it a numbered outline of heading styles? A document with footnotes in it? -- Stefan Blom Microsoft Word MVP &quo...

Rows to repeat at top
We know how to repeat rows for printing, but want to be able to view, or keep them stationary, the top 2 rows of the sheet while scrolling or paging down. Can this be done? Thanks for your time and interest. M. Cooper drag the little bars on the scroll bars and then freeze panes on the window menu "Coopfab" <coopfab@email.msn.com> wrote in message news:uanEcdaJFHA.1280@TK2MSFTNGP09.phx.gbl... > We know how to repeat rows for printing, but want to be able to view, or > keep them stationary, the top 2 rows of the sheet while scrolling or paging > down. > > C...

Data row wise, formula column wise
I have data in cells b2:aj2 I want to refer to these cells in a column from a2 down. I can manually type in: A2: =B2 A3: =C2 A4: =D2 Rather than doing so, is there a formula I can type in once in A2 then copy down? -- Regards, Fred If you just need the data in column A........ Select B2:AJ2 and copy. Select A2 and Paste Special>Transpose>OK>Esc Gord Dibben Excel MVP On Wed, 7 Dec 2005 17:18:55 -0600, "Fred Smith" <fredsmith99@yahoo.com> wrote: >I have data in cells b2:aj2 > >I want to refer to these cells in a column from a2 down. I can manuall...

Highlighted Cell group provides a Count instead of a Sum in bottom right
Sometimes when I download a file into excel it doesn't allow me to ad the group. Additionally, when highlighting the group when it i supposed to give a SUM in the bottom right (off the worksheet), i shows a count. I've verified the cells are formatted as numbers. I for instance I overwrite the existing amount with the same number i becomes addable. I would like to be able to toggle between these tw abilities. Thanks. MACR -- Message posted from http://www.ExcelForum.com Hi the numbers are probably stroed as 'Text'. Try the following: - select an empty cell and copy thi...

setting up "find and delete row" macro?
I'm using Excel 2000. I've been trying to set up a macro to perform a loop function: 1) "find next" the occurrence of a specific text string 2) finding the text string in a cell, select the entire row 3) delete the entire row 4) find next occurrence of specific text string and repeat the process. So basically, I want to search an entire worksheet for every occurrence of the string "abc" for example -- and delete every row with "abc" in it, but leave all rows that do NOT have "abc" in them. I've been trying to set up a macro for tha...

Sorting rows by identical value of columns
I have an inventory worksheet with 3 columns. Each of the columns contains values that should exist in at least one of the other two columns. We want to visualize the rows that contains values not represented in another column. The data in each column is sorted A-Z, but this does not show the "gaps" where a value is not in one of the other columns. A B C 1 PC1 PC2 PC2 2 PC2 PC3 PC4 3 PC3 PC4 PC5 4 PC5 PC5 PC6 We want this sorted by identical values on the "row axis". A ...

Format Cell/Row
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) I am not sure if this can be done, but is there a way to insert space above ling of text so that it is not flush with the line above it? like an indent, only above the contents of the cell so that there is white space around the cell/line content? <br> I tried centering the text, but I wanted a more uniform look. Nothing such as the paragraph spacing in Word. Adjust the height of the row then use Format> Cells> Alignment> Vertical Alignment, set it to either Bottom or Center. I'm not sure what you mean by &qu...

Filter/Display only certain rows of information based on a user na
Good Morning, I have a spreadsheet that contains a list of employee names (In Column "A") and information across a row related to them....Each employee is under 1 of 4 managers and what I need to do is have a way for the manager to log into the spreadsheet and only display the employees that is under them. Thank you in advance for your help, George Is the exclusivity down to confidential data or is it just to make it easy for the Managers to see their own staff? Is there a column that contains the Manager's name? If so and if confidential matter is not a p...

Copy Top 10 conditional formatting down 1000+ rows
I have 20 columns which I am using top 10 conditional formatting. When I copy this (B3:T3) down to the bottom of my spreadsheet, the range changes to (B4:T1200) instead of each row (such as row 4 = B4:T4 and row 10 = B10:T10). Any ideas on how to copy this logic down the spreadsheet without doing it row by row? thanks This code should work. Start with leftmost cell in top, conditionally formatted row. Good luck! Dim MyCounter As Integer Range(Selection, Selection.End(xlToRight)).Select Application.CutCopyMode = False Selection.Copy For MyCounter =...

Can I convert columns to rows?
I need to convert address information, which is on word and listed vertically, to an excel spreadsheet, and have the information list out horizontally. Is this possible to do? I am familiar with the basics of each program, but using "range,formula,value" options in excel confuses me. Here's an example: (My data in word) Mel's Tire Store, Inc. 300 Culbertson Avenue Worland, WY 82401 (307) 347-3601 Need to copy and paste so data spreads horizontally in Excel, under these colum headings: "Business Name" "Address" "City&qu...

Row and column headings
Is it possible to change the lettertype of the rowheadings (1, 2, ...) and the columnheadings (A, B, ...)? And to change their alignment, for example left alligned for the columnheading? If a row is for example 3 times as high as the normal lettertype, then Excel allignes the rownumbers at the bottom of the row. I would like to position it at the top of the row. Joris. (Netherlands) Other than changing the Font there is nothing you can do with the row and column headings except hide them and use column A and row 1 for your own titles. To change Font go to Format>Style and work fr...

Problem with hidden rows automatically unhiding
When I'm linking a cell in worksheet 1 to a cell in worksheet 2, all of the previously hidden rows in worksheet 1 automatically unhide. Can I do something to keep the rows in worksheet 1 hidden until I manually unhide them? Thanks! Fwiw I'm not able to replicate your scenario. When I link a cell (presumably this cell would be visible of course) in Sheet1 to another in Sheet2, other rows originally hidden in Sheet1 stays hidden. -- Max Singapore xde --- "scarlet" wrote: > When I'm linking a cell in worksheet 1 to a cell in worksheet 2, all of t...

Need to insert items / rows above the last item in sale po workord
It would be very helpful if one could insert items wherever desired in a sale, a workorder, or a purchase order. This could be some rows in a workorder Item1 - Basic Bike Tuneup Item2 - Upgrade to deluxe brake job Item3 - Upgrade to deluxe shifter job Item4- Upgrade to detailed clean & polish painted surfaces Often, I need to put Item 99 (brake pads) in the workorder, and I would like to position it between Item2 and Item 3. It's a workflow issue. After the initial creation of the workorder, a user will realize that new brake pads are needed. I would like to have the brake pad ...

can i wrap rows to form multiple rows per row to fit on 1 sheet?
i have a worksheet with very long rows (a to cf). For printing i would like to wrap the rows to fit on a single page, and then seperate each set with a space. I would also like to wrap the headers too, of course. is this possible? Say you're creating 10 columns per new row. So it kind of looks like this: $A$1 $B$1 $C$1 $D$1 $E$1 $F$1 $G$1 $H$1 $I$1 $J$1 $K$1 $L$1 $M$1 $N$1 $O$1 $P$1 $Q$1 $R$1 $S$1 $T$1 $U$1 $V$1 $W$1 $X$1 $Y$1 $Z$1 $AA$1 $AB$1 $AC$1 $AD$1 $AE$1 $AF$1 $AG$1 $AH$1 $AI$1 $AJ$1 $AK$1 $AL$1 $AM$1 $AN$1 $AO$1 $AP$1 $AQ$1 $AR$1 $AS$1 $AT$1 $AU$1 $AV$1 $AW$1 $AX$1 $AY$1 $...