Top row of excel stays even in sortingI 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 sortI 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 rowHi 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 datahiii 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 RowsI 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 selecteI 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 labelsI 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 rowI 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 basicHi,
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 ANOTHERI 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 RowsI 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 topWe 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 wiseI 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 rightSometimes 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 columnsI 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/RowVersion: 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 naGood 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+ rowsI 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 headingsIs 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 unhidingWhen 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 workordIt 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 $...