cell automatically turns colour??
Dear Excel users,
I have a spreadsheet containing names and dates......i would like to
know if there;s any formula for the below:
Let's say.......2 weeks before a due date....the cell with a name on it
will turn "orange" colour.....2 week after.... on the due date....the cell
will auto change to "red" colour.....
we would need this notification very urgently ......
Pls advice me....Thank you very much....
see conditional formatting - set up a formual comapring today() to the due date
"Kelly Lim" wrote:
> Dear Excel users,
> ...How to create a scatter chart with 2 "X" values with common "Y"s
I Need to create a chart with lots of horizontal curves at different Y values.
there are 128 to be precise and I would prefer not to create each as an
individual series unless ... is there a way to copy a series mapping it tonew
(There are also a few diagonal curves so a bar chart wont work.)
Just copy the cells from your spreadsheet that contain your data series,
then select the chart, Edit/ Paste Special, and choose the relevant options,
including "new series".
"M_LeDuc" <M_LeDuc@discussions.microsoft.com> wrote in message...Open form based on combo box value w/error message for blank combo box
I am working in an Access 2000 database that I've inherited. I'm
trying to clean up some of the non-working functions.
I am trying to open a form using the OnClick property for a button. On
a job order form, there is an unbound combo box [CmbContactID] that
gets its list from querying Table!ContactInformation.[CustomerCodes].
When populated, [CmbContactID] should provide the value to open
FrmContactInformation when [CmdOpenContactForm] (a button) is clicked.
I want an error message to display if the field is blank otherwise it
will open the form for the customer code that is displaye...determine mail flow problem!!!!!!!!
Our mailflow from 2 backend 2003 exchange servers keeps queuing up in the
outbound queue (which is set to always run delivery). Messages stay in there
from 10 to 120 minutes before being sent to our gateway MTA and then sent
off site (to hotmail.com for example). However when I telnet from a backend
server to the gateway over port 25 and send a test message it goes right
I can't for the life of me figure out why.
One example when viewed in message tracking history says:
10:16am - smtp: store driver, message submitted from store
10:16am - smtp: message submitted to ...cell with only a single quote
I need to delete rows with cells containing only single
quotation marks. How can I replace the single quotation
mark with a blank so FIND/GO TO/SPECIAL/BLANKS can find
them? Or is there a better way?
P.S.: The single quote is displayed in the Formula Bar but
not in the cell.
>I need to delete rows with cells containing only single
>quotation marks. How can I replace the single quotation
>mark with a blank so FIND/GO TO/SPECIAL/BLANKS can find
>them? Or is there a better way?
Assuming you want to delete al...Determine columns used
I have 100+ spreadsheets which i have to edit into a certain format. The
spreadsheets currently have 100 columns (from 1-100). From there i have to
reduce the number of columns set to 8 columns.
1 2 3 4 5 6 7 8
0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 (from column 9 onwards, it
goes to the
0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 next rows' column 1)
0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
How do i do that without altering the order of the value of the cell?
Let me make sure we understand the question.
You have ...Chart Axes Linked to cell values
Hello Everyone. I have some sort of a problem with linking chart axe
(min and max values) to cell values in a worksheet. I am using th
macro that I found in Mr Peltier' site:
It worked just fine for a simple test I was doing.. BUT, I came up wit
the idea of assigning to the cells (the cells that contain the scal
params) some worksheet functions, to determine the values from a set o
data. When I did this, the macro didn't work anymore. It didn't pop u
any errors or such things, it just didn't adjust the chart anymo...MsQuery returning one record + one empty record
In the same excel file, i have one sheet containing data and one running a
MSQuery to retrive some of the data with a parameter. The problem is that
when only one record should be returned, I have that record and a blank
record. Because I have formulas that are auto-extended, I have two lines of
formula. In MsQuery UI, there's only one record.
I hope this is clear,
...! removing initials and titles from name cell !
I'm sure what I'm asking must have a simple solution, but I can't work out
how to achieve it so thought I'd ask the experts.
I have an addess list. It has names and some with initials, and titles.
I'm looking for a macro, or some simple way to remove these characters from
each name cell.
Bob A Lastnam & Jenny B secondname Jr
Fred Blogs & John J Ashton ii
What I want as the result is:
Bob Lastname & Jenny secondname
Fred Blogs & John Ashton
Any suggestions on an elegant way to acheive this.
I note that doing a replace on " "A&...if i select a cell then i want row containing that cell selected
i want to copy all rows that contain a cell with certain value.
Do a Data > Filter > Autofilter on the key col,
filter out that certain value, then copy the filtered rows,
and paste special as values/formats elsewhere as required
> i want to copy all rows that contain a cell with certain value.
...Fill the below blank cells
I just copied an pivot data and pasted it in different Sheet.
I needed to fill the cells that was blank with the data above each set
of blank cells..
Day VendorName VehicleType VehicleNo StartTime
1 ATL B 1246 18:00
I can solve this by dragging the data down but I have do it at least
Is there any formula to this...?!
Thanks in advance
Highlight the columns A to E by clicking ...hide a section depending on bit value...
2 sections on a form: 'pers' and 'pers_kat'
On the 'pers' section I have a bit (yes/no) radio button that I would
like to be the trigger to hide/show the 'pers_kat' section incl. the
On the pers_kat there's 6 different attributes - the attributes name
won't work....conditional formatting #value!
Is it possible to create a conditional formatting condition that
can detect the condition of #VALUE! ? That is NOT the text string but
the "error condition". What I am trying to highlight is that certain
calculated values have not been correctly calculated on the sheet and
need further investigation.
Use the 'Formula Is' option on the conditional formating dialog and
enter the following, assuming the cells is A1.
You can also use the Go To dialog (CTRL+G), Special... to locate cells
Clive Long...keep getting zero instead of empty cell
This is the formula that I've been using to pull the matching value i
the column "Drawing Number" from TABLE.xls, based on what I type i
cell B1. This all works fine until the corresponding cell for Drawin
Number is blank. I want to be able to specify what shows up in th
cell in my new sheet when the other cell is blank. Even if it's just
blank space that's what I want to show up.
So I did some research on this board and am using the following formu...leave a cell blank
My spreadsheet serves 2 purposes. Printed and manually filled out plus data
The cells with formulas show a 0 (zero) if the referenced cells are empty.
The problem is the user has the option to ignore the formula and hard code a
If a formula = 0, is there a way to hide that zero so when I print the form
it's blank for the user?
You could turn off viewing zeros at Tools>Options>View.
Or you could trap for the 0 and leave cell looking blank.
If you are interested in keeping the user from over-writing a formula, see hel...Cannot see all characters in cell
I have workbook that contains column for text/comments. I am unable to see all of the text in the cell no matter how I size it. All the text is displayed in the formula bar when the cell is selected, however not all of the text is displayed in the cell itself
I have Excel 97 and I believe there is a 256 character limit. Is there a way to get around this limit, or can I do something to enable all text to be seen
I am having the same problem on a form that I use every month. At the top there are 4 words that show up in the formula box, but don't show up in the cell or when I p...coping with empty excel cells in the xml document #4
I have an xml document that has been created from an excel spreadsheet. In
the s/s there are some empty cells however these are not captured in the xml
file. All that is added is for the cell after where the empty cell should
be, it has an extra attribute: "ss:Index="6", where it is the 6th cell in
the row, and cell 5 had no entry.
I'm wanting to pick out a particular cell from a particular column however
just iterating through the cells is going to give me invalid values where
there should have been empty cells.
Does anyone know how to handle this?
I've tried ch...Not able to access format settings for cell
I have a spreadsheet in 2003 that I have been using for quite awhile. The
other day I think I hit some key combination that now does not allow me to
access the format capabilities of the cells. If I trying clearing contents,
still no luck. If I copy the format from some other cell that does allow it
then Im OK. I thought maybe I accidently protected some cells, but when I
check the protection tab under tools it does not show anything. Anyone have
You seem to have the cure to your problem:- "copy the format from some
=?Utf-8?B?RGV2bw==?= <Dev...Counting Cells #3
I want to be able to count the number of cells that have coloured text from a
range of cells. Is their a way to do this, I tried "countif" but was unable
to get it working.
See http://www.xldynamic.com/source/xld.ColourCounter.html for a solution.
(remove nothere from email address if mailing direct)
"Missile" <Missile@discussions.microsoft.com> wrote in message
> I want to be able to count the number of cells that have coloured text
> range of cells. Is their ...Move cells down to match cells
A B C D E
1 000801025-8 009601025-3 2007 000801025-8 2007
2 000801026-9 009601026-4 2004 000801026-9 2004
3 000801028-1 009601028-6 2006 000801028-1 2006
4 000801036-8 009601036-3 2008 000801036-8 2008
5 000801044-5 009601044-0 2007 000801044-5 2007
6 000801044-5 009601055-0 2004 000801055-5 2004
7 000801055-5 009601064-8 2001 000801064-3 2001
8 000801055-5 009601091-2 2006 000801091-7 2006
9 000801064-3 009601095-6 2002 000801095-1 2002
10 000801091-7 009601097-8 1998 000801097-3 1998
11 000801091-7...how to determine the size of the sheet
I had a collegue who told me once a way to dtermine the number of the rows
in a sheet of the workbook I am working at.
It is known that when you open a new workbook each sheet has maybe 65365 or
something like that rows.
MY QUESTION IS: HOW TO MAKE THE SHEET 1000 ROWS OR WHATEVER NUMBERS OF
ROWS I ONLY NEED?
Thanks in Advance,
The number of rows and columns are fixed. Your workaround would be to hide
the ones you consider unneeded.
HTH. Best wishes Harald
"Ahmed SHEBL" <firstname.lastname@example.org> skrev i melding
Is it possible to change the background color of a column of cells depending
on the value
of the cell at the top of the column.
I have written a macro to do this but can't find a statement to do this:
worksheets(:Sheet1").cells(cl,rw).backcolor = QBColor(14)
Error message method not supported and nowhere in the help can I find an
Suggestions would be appreciated
Why not just use format>formula is>conditional formatting
=a2=$a$1 and format as desired>copy format using format painter.
email@example.com...Macro/keyboard shortcut to increment a cell value
I would like to set up a keyboard shortcut (e.g., Ctrl+I) that would
increment the value of the selected cell by 1.
Can someone get me started by pointing me in the right direction? I
have some experience creating simple macros in Word, if that is what's
PC: HP Omnibook 6000
OS: Win 2K SP-4 (5.00.2195)
LAN: P2P with an HP Vectra workstation
Email: Usenet-20031220 at spamex.com
One way is to make a macro and assign the shortcut key to it:
If IsNumeric(.Value) Then
.Value = .Val...Excel automatically changes the formatting of the cell to "Time"
I have a long column of numbers [dates in the YY:DD format]. I wanted to
":61" to ":59". Even though the cells are initially formatted as Text, as
soon as I make the change, Excel changes the formatting to Time, and the cell
with the change now has text ":59:00" in it.
Is it possible to force Excel to keep the cells formatted as Text?
If the cells are truly formatted as text (Format - Cells - Number - Text),
the formatting should never change, and you should always see exactly what
you've typed. Give us an example...Reversing name values in a field
I have a field called CustomerName that has values that are last name,
first name. So there is a value of Smith, John which is John Smith. I want
to preserve the value in this field but create a new field called
CustomerName1 that has John Smith in it. It would need to somehow reverse
the values based on the comma delimeter and then get rid of the comma. Can
LN = Left$(CustomerName, InStr(CustomerName, ",") - 1)
FN = Right$(CustomerName, Len(CustomerName) - InStr(CustomerName, ","))