Sort does not recognize the header row
I encounter this problem in only one of my worksheets. Others are fine.
When I select the upper left data cell (a formatted date cell) the sort
does not recognize the header row 1, even though the sort window is set
to recognize a header row. The 3 options for the sort order hierachy
show the contents of the header row cells, not the column letters. It is
as if Excel is selecting the column headers as the header row, rather
that the first row of data.
I can get around it by inserting a blank row 2 beneath the header row,
but I would prefer not to have to do this.
The header row is 5...lookup not in row but in matrix #3
a nice job! That's what I wanted.
How do I
a. include your functions in an existing excel file?
b. add my own functions
frens's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1505
View this thread: http://www.excelforum.com/showthread.php?threadid=26682
> Hello Beban
> a nice job! That's what I wanted.
> How do I
> a. include your functions in an existing excel file?
> b. add my own functions
> b...repeat text in same exact cells in multi worksheets
I have a workbook and have 20 worksheets in the book.
example of what I would like to do.
on the worksheet in the same exact numbered cells I want to put the
text in once and then put a forumla to make it copy the info to 2-19 pages
of the workbook.
like 1 of 20 pages, 2 of 20 pages - I only want to put the "of 20 pages" once
then have it when it's changed it would automatically change the other work
books if I had 21 pages one time then it would change the others. I hope
This needs to be done once only, select the 2nd to the 20th she...Formatting a Row Question
Is it possible to conditionally format an entire row based
on input into one cell in that row, or am I "rowing"
All help on this appreciated,
Yes, perhaps a simple example to illustrate ..
Select row1 (click on the row header)
Click Format >Conditional Formatting
Under Condition 1, make the settings:
Formula Is | =$B1=1
Click Format button > Patterns tab > gray > OK
Click OK at the main dialog
Now key in a "1" into B1, press Enter
Row1 will be coloured gray
Clear B1, the colour disappears
(Note that the $ sign in the formula is import...How to amalgamate 1000s of rows of data into a usable chart
I have the following data in colums A - D with the headings below.
(There are 1000s of rows of data, but only the 4 columns)
9588_____________Delivered to Answ Mach_____15_____________
0060_____________Delivered to Answ Mach_____23_____________
3868_____________Delivered to Answ Mach_____35_____________
2342_____________Delivered to Voice__________1204__...one cell in a row contains a number
I have a fixed row of 7 cells, any one of which might contain a number. the
cell with the number is the relevant one and it will determine my formula; i
will want to multiply the cell value by a number in another cel which is
chosen according to the number of the cell in the original row.
eg A1:A7- A3 contains 21
formula in A50 will wish to determine 21 x value of cell in column 3 of a
range of columns because the number 21 is in cell 3 in the row.
I was planning to use V or HLookup I think but cant fathom how to get the
cell number form the original row.
=SU...How to get notified when inserting cells, rows, or columns in Exce
I'm making a client to Excel that needs to know if the user inserts cells,
rows, or columns in a worksheet. The SheetChange event isn't informative
enough. How do I find a proper notification?
...pasting non-contiguous range of cells to new row, same cell locati
I need a paste macro that will paste a non-contiguous range of cells (ie
to the current row in the same column locations (ie "A5:B5,G5"), assuming
current row is 5th row.
Does anyone have code for this need that you can share?
Range("A1:B1").Copy Cells(ActiveCell.Row, "A")
Range("G1").Copy Cells(ActiveCell.Row, "G")
Regards Ron de Bruin
"Not excelling at macros" <Not excelling at firstname.lastname@example.org> wrote in message
news:349879...Create rows from a lookup
I have 2 worksheets containing data that I need to combine, by creating rows
WS1 has product data in the range of:
Product_ID Name price Options_Ref
1 Product 1 10.00 18
2 Product 2 15.00 25
3 Product 3 5.00 18
4 Product 4 20.00 7
WS2 has the option list data such as:
25 ...Inserting & deleting a row
Can someone help me write a macro that would insert a row
of cells above the selected ones on the worksheet? Also
one that would delete a selected row of cells.
Try this Change
For the activecell
For the selcetion
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
"Chance" <email@example.com> wrote in message news:114760...How can I put a formula into every other row tia sal2
I'm trying to get excel to calculate a formula for every other row is this
Column A1-A9 has 1 thru 9 in it.
Column C1 has the formula A1+3 and I would like that formula to be in every
other row of C. So C1 would be A1+3 ,C2 would be blank, C3 would be A2+3,
C4 would be blank, C5 would be A3+3 and so on.
Is this possible?
"firstname.lastname@example.org" <email@example.com> wrote in message
> Greetings all
> I'm try...Excel 2007: UDF can't reference row below 65536
In Excel 2007 (I'm on SP2), if I enter this formula:
It works fine. But if I enter this formula:
Excel turns it into this:
That isn't the UDF's fault' Excel is doing that before the UDF has the
opportunity to do anything at all. Is there a workaround or fix?
It sounds like you are working in Compatibility Mode. How many rows does your
workbook have on a sheet? I created a simple MyUDF function, and it accepts
any range up to 1048576 unless the workbook calling it was saved in an
earlier ...How can i stop data being repeated within a column?
I have a list of contract numbers relating to application numbers or
payments. they are in the format nnnnnnan or nnnnnnpn. The columns are fixed
to this format only. If they are entered with the a or p in the wrong place
or if they have been left out completely an error message will appear to
alert the user. I want to know how to alert the user if they enter an
application or a payment number that has already been entered.
ie if they enter 022079a4 but that same application has been entered else
where in the column.
If anyone has a step by step suggestion that would be very helpful. And...Hide Rows #2
I've been working on this for a few hours and am finding this one to b
a difficult one.
How can I write a formula or some code that will hide rows based o
If in Row 1, cells F1 and J1 contained zero, then hide the whole row
(This would need to be done for multiple rows i.e all rows where F an
J columns contained zeros)
Message posted from http://www.ExcelForum.com
You can't do it with formulas, you need VBA code. This example will do it as
the worksheet changes, not a macro to run to hide all rows at once:
right-click the sheet...Alternating Row Formats When Cell Value Changes
I have a list of schools with the school names in column B and the school's
district number in column C. I'd like to alternate the row shading each time
the district number changes. Is there a formula that can be used for
conditional formatting, VB code or some other method to do this?
Thanks for any help or advice,
My Excel add-in "Shade Data Rows" does that.
It shades groups of like valued rows or will shade by every nth row.
Choice of shade colors and an option to skip hidden rows.
Comes with a one page Word.doc install/use file.
It is free upon direct req...Showing rows & columns
I am trying to use Excell as a itemized list for a
contract how do you get the printer to print with the
rows & columns like it apears on the screen? Also is
there a book or program that teaches this program?
File | Page Setup | Sheet tab | Row and column headings
"Patrick" <firstname.lastname@example.org> wrote in message
> I am trying to use Excell as a itemized list for a
> contract how do you get the printer to print with the
> rows & columns like it apears on the screen? ...Number of rows and columns
I have a spreadsheet that has only 85 rows and columns to AH. After
that the spreadsheet is gray. I checked, but there are no hidden rows
and the VBA code does not have anything apparent to hide or limit the
rows/columns. Could somebody explain how that is done?
Thanks in advance.
You are in page break preview, click on the view drop down and select
normal to see the whole speardsheet
> I have a spreadsheet that has only 85 rows and columns to AH. After
> that the spreadsheet is gray. I checked, but there are no hidden rows
> and the...How do I check for repeated values in three distinct columns?
If anyone can help me!
I have three distinct ranges that are not connected.
I want to be able to choose a number from 1 to 300 and place it in any
of these cells but not allowing it to repeat, giving an error message
if it does.
I know how to use this formula in the data validation to not allow a
repeat in one column:
=COUNTIF($A$2:$A$20,A2) = 1
But, when I tried different ways to include the other columns, Excel
wouldn't allow me to do it. I have Excel 2000.
Please Help me!
~~ Message posted from http:...Format spreadsheet so that it only has a few rows and columns
I have seen an EXCEL 2007 spreadsheet that only has rows 2 - 6 and columns A
If you use the arrows it doesn't go anywhere at all except within those
boundaries. The other parts of the screen past these boundaries are in the
default MS window colour of blue and there are no gridlines.
How can I duplicate this effect?
Hide the rows and columns you don't want to use.
To select columns select the one to right of your desired used range.
CTRL + SHIFT + RightArrow selects all.
Right-click and "Hide"
Same for rows below your desired range.
CTRL + S...need to know how to delete duplicate rows
This is what I'm looking at:
A B C
5 8143 UNIT BRAKE TEMP MONITORING UNIT 351H51002
124832 5273 VOLT METER 124832
124834 6771 AMMETER METER 124834
124835 5272 FREQUENCY METER 124835
124838 34 CSD TEMP INDICATOR 124838
124838 44 IND, CSD OIL TEMP 124838
124838 217 IND, CSD OIL TEMP 124838
124838 4306 CSD TEMP IND 124838
1652 3918 VALVE PRESS RELIEF 1652
I need the description in column C. BUT I only need 1
description per part number(column A). So if i delete the
duplicate part numbers in column A, then I would be left
with only 1 description in column C, lining up...Mail Merge
I would like to create a Form Letter, importing data from an Excel Spread
Sheet. On the first few rows of the Excel Spread Sheet is some data not to
be moved, and several rows below the first row is the header row. Of course,
when I go to merge into the document, the fields are not there because it is
reading from Row 1. Is there a way to specify that the header row is Row
"x", thus ignoring the rows above?
AFAIK not possible. Any chance you can use a separate sheet with the
"MATT" <MATT@discu...Rearranging rows & Cloumn( Please help me)
I have a data in this format
Date Day Time Confirmed Home Page UV's Search Page UV's Ratio of H.S & H.P
conversion this hour UV's
1-Feb Fri 9:30 AM 424 8456 4047 0.481 0.1088
1-Feb Fri 10:30 AM 615 13689 6749 0.5163 0.0707
1-Feb Fri 11:30 AM 926 19872 10104 0.5426 0.0927
1-Feb Fri 12:30 PM 1247 27366 13842 0.4988 0.0859
1-Feb Fri 1:30 PM 1584 34334 17307 0.4973 0.0973
1-Feb Fri 2:30 PM 1895 41185 20761 0.5042 0.09
& I want to re arrange this data like below
Day Date Time Activity Value Data Type
Monday 13-Aug 10:30AM Confirmed 256 Daily
Monday 13-Aug 10:30AM Home Page N...how do I put rows in excel to seperate, one addresse from another.
I have a list of addresses that I would like to seperate by rows,
example:Golden Bay Federal Credit Union
PO Box 127 Bldg 556
Moffet Field, CA
Golden Valley Federal Credit Union
Manteca, CA 95336
I would like to seperate each address with a blank row, and where there are
one or two lines of address, with 2-3 blank line to make the format of four
line in the address including the blank.
Thanks in advance
Hi, Betty. Mail merge with Word!
**** Hope it helps! ****
Excel VBA Certification Coming Soon!
"Betty F"...delete rows
i would like to run a macro that does the following.
start at row 2
if L2 and m2 and n2 are all zeros then delete row
if not then go to next row and do the same check.
the macro should end once the "L" field is blank.
let me know if more info is need
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim wks As Worksheet
Dim DelRng As Range
Set wks = ActiveSheet
FirstRow = 2
LastRow = .Cells(.Rows.Count, "L").End(xlUp).Row
For iR...Too many rows highlighted
When I click on a row, multiple rows are automatically selected. This
happens to all of my sheets in my workbook. Do I have a bug? How do I fix
You could try tapping F8.
if that doesn't work and you have E2007 then there is a reported bug that
causes this and if you alter the zoom level up and down again it should clear
When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
> When I click on a row, ...