I am trying to sort my sales table, it doen't seem to work for me. A
comment comes up: this operation requires the merged cells to be idendically
sized. Can you help? Thanks
Easiest way is to *unmerge* the cells, and then use "Center Across
<Format> <Cells> <Alignment> tab,
And expand the "Horizontal" window, and choose,
"Center Across Selection"
Please keep all correspondence within the Group, so all may benefit!
==============================...deleting cells from a calculation...
If I had 32 cells, which represented control values, and I wanted to
execute some QC on them if individuals were too far from the mean value,
how could I calculate the average value in one cell and yet exclude
cells where the values were too different?
I hope I have explained this well enough!
If not I will try again following feed back.
Best wishes to you
Take a look at the function TRIMMEAN.
(It migth be a function from the Analysis Toolpak)
* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
<...Moving an active cell
I need a snippet of code that will (when you use ctrl-d) look at the active
cell, move the cursor down 9 cells (same column) and make that cell the
active cell, no matter where you start on the spreadsheet.
Thanks to everyone.
Hope this helps. Otto
"Howard" <Howard@discussions.microsoft.com> wrote in message
> I need a snippet of code that will (when you use ctrl-d) look at the
> cell, move the cursor down 9 cells (same column) and ...Too many formatted cells
When i try to open a saved excel workbook it will not let me open it.It just
says too many formatted cells.
XL: Error Message: Too Many Different Cell Formats
A few people have said that OpenOffice.Org has been able to open the file. Then
they clean it up and save it there. Then excel can open that cleaned up
http://www.openoffice.org, a 60-65 meg download or a CD
> When i try to open a saved excel workbook it will not let me open it.It just
> says too many formatted cells.
Da...Stopping cell reference updates
I have one table in one worksheet with 100 rows of data, 4 columns.
Worksheet 2 has a table with 2 columns with 1000 rows, all referencing
two of the columns in the first table. So essentially I have one
table that's a subset of the larger table. However, when I insert
rows in the first table, the second table's references change too,
which is undesired. If cell B3 in table 2 points to cell B3 in table
1, and I insert a row above row 3, then cell B3 in both tables become
B4.. I'd rather have B3 in the second table still point to B3, and B4
in the second table ...Conditional Formating for Formula Cells
Hi to all fantastic Excel champions,
How do I get conditional formatting to change the format of any cell that
contains a formula?
Andrew 'The Ox' Heath
You can use a user defined function (see my formula.htm page):
But it must be in the same workbook not your personal.xls to work
in Conditional Formatting, though there is no harm in having it in both places.
HasFormula = cell.HasFormula
use the above in your conditional formatting
http://www.mvps.org/dmcritchie/exc...Changing cell colors
I am trying to change a cell color to either red, yellow or green if the data
in the cell is greater than lets say 90 it would turn red. Less than 90 but
greater than 80 yellow and less than 80 turn to red. Without using
Unless there is a way to conditional format the cell and have the
conditional formatting move with the sorting of high to low.
I can do the conditional formatting just fine but when I sort the
conditional formatting stays with the cell it doesn't move with the sorting.
That process is very simple. Microsoft Office 2007 has mad...Changing colors of cells to establish value.
Can I program my cells to establish a color of the cell
and/or the color of the font according to the value of the
cell. ie: If I have a formula that has a product being
sold at 5000.00 with a cost of 4000.00, in another 5000.00
sale with a 4500.00 cost, and in another a 5000.00 sale
with a 6000.00 cost. Could I show these with the 1000.00
profit showing in one color automatically, if the cost
changed to the second, could I show the 500.00 profit as
another. If it changed to the 1000.00 loss, could it
change to another color?
You want CONDITIONAL FORMATING....addition, subtraction in a single cell formula
I need help updating a single cell formula.
G4 has a formula of *=12500-A7
I would like to update this so that A7, A8, A9 are added together then
subtracted from 12500.
Any help would be greatly appreciated.
=12500 - A7 - A8 - A9
12500 - (A7 + A8 + A9)
Microsoft Excel MVP
"mePenny" <mePenny@discussions.microsoft.com> wrote in message
> I need help updating a single cell formula.
> G...Cell formatting from Access
When I export an Access report to Excel, one of the fields, which is a time
field, e.g., 15:10.9 in Access becomes "0.63205" in Excel. Some kind of
decimal formatted cell. How can I export to Excel without this format
After the import is done, simply format those cells as time, with the custom
mm:ss.s format - though you will have to first divide all the values by 60.
The 15 minutes and 10 seconds from Access is becoming 15 hours and 10
minutes, since Excel requires the entry to be 0:15:10.9 to treat the value
as minutes and seconds.
Ber...Copy data into next avaiable cell #2
I will give it a go and see what happens.
Chisel's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=935
View this thread: http://www.excelforum.com/showthread.php?threadid=26840
...when i write in cell 2-2 or 1-2 whatever it is change to date whi.
Whenever i enter 2-1 or 3-1 etc into a cell, Excel automatically changes it
to a date, which i don't want to change to date i want it as 2-2 or
2-1wahtever .when i simply want to display 2-1 etc. However if i change the
formatting in that cell back to general, it interprets that as a 5 digit
number, e.g. 38408. How do i turn this auto-edit feature off?
Format the cell as text before you enter the data. Or precede the data with
a single quote; it will not show but it will cause the data to be
interpreted as text.
Microsoft MVP - Excel
"haniht&qu...Past Link then copy
I am doing a log sheet which calculates usage of gas and steam production on
a daily basis.
The readings are taken daily and the usage/production is calculated.
I want to automatically transfer the previous day's readings to the next
day's sheet so that I don't have to retype the readings twice.
I have done a "paste link" to the next worksheet and would like to find an
easy way to copy the sheets so that the "paste link' will refer to the last
days sheet rather than refer all the way back to the original sheet.
I there a way to copy sheets which will increme...Copy range based on input
Based on the value of one input cell, I want to copy a certain other range of
cells, pasting them into another area on the worksheet. When I change the
value in the input cell, I want to copy a different range of cells into the
same target range. I have several different values of the input cell that I
would like to use for defining the copy range. Ideally, I would pick the
value of the input cell from a list and use that to determine the copy range.
I've been doing manual copy/paste and am tiring of it. Please help.
If desired, send your file to my address below. I wil...Cell Format Not Available
We have an old spreadsheet that was originally created by Excel 2000.
It is a complex workbook with many formulas. It does not have locked
cells, nor is it protected.
The problem, is that a bunch of cells will not allow for formatting.
We can't figure out why. If one right clicks while on the cell, the
short-cut menu appears, but if you click on "Format Cells" nothing
happens (it doesn't take you to the next menu). The same happens if
you go through the toolbar and try to click FORMAT then CELLS.
I've searched a couple of databases and I can't find any reference ...Why does Custom Format not control Cell Display?
Have a cell which shows in the formula bar:
06/10/2005 10:10:32 AM
In the worksheet (same cell but unselected) the display is:
No matter what I do with cell formating i.e. general to
text to custom (BTW which is mm/dd/yyyy h:mm) the cell
displays 38513.4239814815 UNLESS I select the cell - then
it displays 06/10/2005 10:10:32 AM?
When I de-select the cell it reverts back to 38513.4239814815
I thought that I knew better! What is the very obvious thing that I am
Maybe you're looking at formulas...
Uncheck Form...Serious sorting problems with cells containing references
Hi, I'm running into some serious bugs when I try to sort some
cells on a sheet. If all the cells contain simple text or
numbers, all is fine. But if any of the cells contains a
reference to another cell (outside of the sort range), the sort
is messed up badly. On some occasions the reference in question
has been deleted and the cell's previous contents restored.
The range in question is three columns wide and about 12 rows
long. It has a header row. Everything is very simple.
MS Office '03 Basic on Win XP
Select your cells with the references, and run the ...recalculation not recalculating some cells containing user functio
I have the following situation:
1) workbook contains User Function macros in some cells.
2) #VALUE appears in some of the cells
3) if i select the cell and use "F2" to edit then the value is correctly
4) pressing "F9" will not cause the cells to recalculate nor will
5) running a macro containing the following
what must i do to force the cells to recalculate without having to
#3 above on each cell.
at the start of the UDF will make the fu...Excel VBA: How to select only rows containing data?
I'm writing a macro to automate formatting of a commonly used report.
The number of rows containing data will vary on any given day. I'd
like to select only the rows that contain data, set the heights for
those rows to 63.75, and leave the other rows untouched.
Does anyone have a suggestion on how to select only rows containing
data? I'm running Excel 2000 on Windows 2000
For Each c In Selection
If c <> "" Then c.RowHeight = 63.75
"Russel...Excel 2003 -- copy/paste error
I'm using XP and Excel 2003...everything is current.
I'm copying a cell's formula using ctrl-c, then pasting
to many cells using ctrl-v after selecting the target
Upon hitting ctrl-v to paste, I get the following error
message: "Selection too large." I get 1 option: "OK".
Nothing else...no help, no nothing. Very cryptic error
Has anyone else come across this problem? If so, how did
you solve the problem?
I don't think I've seen this error--I still use xl2002.
You may want to give more details to see if anyo...copy selected accounts to CRM-Test
I have CRM Test and CRM Production running on two different servers.
I wish to copy some accounts, which are unique from the others by
relationship type. I want to copy these to the CRM test.
1.) Is there a way I could do it in teh Web client itself.
2.) If not, where do they exist in the SQL database.
I located the Account Base in the CRM Production, but do not see a column
named' Relationship Type'.
3.) How do I copy the selected data in one Database to the other database.
...looking up a cell
I have the row() function returning one number and the column() functioning
returning another number, but how do I look up the value in the cell this
refers to? I know without modifying the numbers it would be the same cell,
but I add something to the row() number.
What are you trying to do?
davesexcel's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=31708
View this thread: http://www.excelforum.com/showthread.php?threadid=522118
J3: =OFFS...Creating two linked copies of one workbook
Hello. I would like to create two spreadsheets that are exactly the same in
two different places. Essentially, I would like to use a spreadsheet on my
local drive that updates a spreadsheet on a network drive that is accessible
to other people. This way when I save to my local spreadsheet it
automatically updates to the network document. Can this be done and how?
You can use SaveCopyAs
Copy this in the Thisworkbook module to save the file also in C:\ (change that)
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ThisWorkbook.Sa...Can we update links automatically somehow after making a copy of a sheet with links?
Is there a way to do this that is not manual? Knowing the power of XL2K, it
seemed wrong to me, somehow, that each time my supervisor makes a copy of a
sheet, she has to fix the links manually. Any help would be greatly
appreciated. I have a colleague who is working in this massive workbook at
present, and she's daunted at all the new sheets to add that will require
all this manual editing.
Thank you! :oD
You don't indicate exactly what you have to change,
sheetnames, workbook names. Formulas or links
that get changed would help.
If you are copying more than one sheet that ...how do i combine a date cell and a time cell in excel?
how do i combine the date(A1) and time(B1) to show up in one cell and have
the correct format (C1).
A1 B1 C1
5/25/05 13:00 5/25/05 13:00
Just add 'em up; in C1: =a1+b1, then format it appropriately (Edit >
Format, on the numbers tab select Date, and scroll down til you see some
formats that include both date and time).
> how do i combine the date(A1) and time(B1) to show up in one cell and have
> the correct format (C1).
> A1 B1 C1
> 5/25/05 13:00 5/25/05 13:00