Why CELL("format",A1) doesn't return something like "#.##"? ...and other rants
I'm trying to compare the displayed value of two formatted cells. The
idea is to detect the equality between 1.2345 and 1.23 when the format
is "#.##" without resorting to VBA. This is Excel 2000, SP3.
So I decided that this might work (may be wrapped):
Unfortunately, CELL("format",A1) returns "F2", which is incompatible
with the format string required by the TEXT() worksheet function. The
above conditional actually evaluates to "equa...Formatting Borders
Why, when I click the double-underline border button, do I get a
single-underline border? If I go to the borders menu I can select the
double-underline and click the bottom border to make it work, but the button
isn't functioning properly. I've tried customizing my toolbar and removing
the button and putting it back, but I get the same result. Any thoughts?
This is driving me nuts.
You should have a down arrow next to the button, select double underline from
there. It will stay that way until you close excel, once you open it again it
will default to single underline
Rega...Strange Result using custom number format
I am trying to apply a custom number format that returns the following
If the number is less than -1 it is formatted as (?#)
If the number is greather than +1 is is formatted as +?#
If it is between -1 and 1 the result is text saying "Unchanged"
But when I apply the following custom format--> [>1]+?#;[<-1] (?#); "Unch"
I get a strange result for numbers between -1 and 1. I get -Unch. I can't
figure out why it puts the - in front of the text.
...Formatting fields with decimal places
I have a table that has field name of Total_Tax with a data type of currency.
The data in the field can range from being blank to 999999999.99. Maximum is
9 numbers before the decimal place and 2 afterward.
My problem is that the data was imported from a text file into access as
400. However that should be formatted to 4.00 or 1234 should be 12.34.
Also a number that is 123456789 should be 1234567.89.
I further want to format it to currency for easier reading and manipulation.
So I would like to insert $ signs and delimit them with a comma. So I need
to go from 12345678 to $123...How do I change date format?
I have just upgraded to Excel 2003 from 2000. I have a date field which
looks like this: 03/05/05. The new 2003 file looks like thie: 03/05/2005
00:00AM. When I select Format>Cell, I get nothing. Unable to clear the
field and start over. Can not get the format screen to appear. When I try
with an unused field, I get the Format>Cell> screen with the various tabs.
It is controlled in your short date format in your system regional settings.
Windows Start, Settings, Control Panel, Regional options, date(tab),
Short date format: mm/dd/yy
if th...Report Format changes after leaving design mode
I've just about finished setting up a new report, except for one field. It is
meant to be located between two thick vertical lines, and under another
label/text set. No matter what I do, the preview version puts this data and
label (a date) at the bottom of that record's section, near the bottom line
of data. I have seavel text boxes set up to to display data from seavel
different fields below one another, without leaving gaps, one of which is
large enough to change the vertical size of the record. the problem seems to
be related to this text, but if that is true or how it is related, ...Thread Race condition here?
I've thought up this way to prioritise threads accessing a limited resource
( perhaps there's a better way?).
Anyway, is there an opportunity for a race condition here to screw things
Particularly where I test on the counters could other threads change these
values and screw things up?
Even if I do a ::InterlockedIncrement this doesn't really help, also I can't
put everything in a critical section as I have a WaitForSingleObject in
there and would lock all the other threads out.
I don't think there's a "logic" problem in the code - but I haven't tri...Conditional formating 02-23-10
I wonder if anyone can help me please
I'm trying to set up conditional formatting for a cell based on data in
range of cells
for example: cell D9 should turn red if E9:Q9 contains letter a
I have been trying using find but no luck so far.
Use the option FORMULA IS in Conditional Formatting and enter the formula
> I wonder if anyone can help me please
> I'm trying to set up conditional formatting for a cell based on data in
> range of cells
> for example: cell D9 sh...change number format
I have a column with figures in the 123,45 format and want to change it to
123.45. How do I do this?
Thank you very much.
The characters used for number formatting are determined by your Windows
Regional Settings, so you'd have to change them there.
"Jim" <jim6651@nospamTRTnet.nl> wrote in message
> I have a column with figures in the 123,45 format and want to change it to
> 123.45. How do I do this?
> Thank you very much.
I know...questions about formating to labels
how do i transfer contact info in my excel file to
labels, to print?
It could be done in Excel only, but........
Investigate the use of Word's mailmerge feature to get Excel data to labels
Doing it strictly in Excel is difficult and involves much trial and error
Gord Dibben Excel MVP
On Wed, 25 Feb 2004 13:25:48 -0800, "exodus"
<email@example.com...Seed date formats to different year in different cells
Workbook was created in Excel 2003 (PC) and forwarded to another person who
opened it with another PC using either Excel 2000 or 2003 (unconfirmed which
version). The dates showed up as about 4 years off. My first thought? Maybe
the machine was set with a different system clock. But here is where it gets
the receipient returned the workbook via email, and the wrong dates still
show up even on the originator's PC. When he enters new dates, they show up
as correct, but the cells that had the original dates (or anywhere they are
cut/paste from those original cells, includi...Something strange is going on
Just installed OFF2004 and tried online registration.
First, a message came up (Safari) that the Security Certificate for the
site could not be confirmed and that it might be fake (or something to
that effect.) I elected to continue.
Then I arrive at first registration page which requires that I sign-in
to Microsoft Passport (with which I am not familiar.) In the e-mail
address field is a default e-mail address; not mine but a that of
friend of mine with a @hotmail address. Why is her address there, and
not mine or anyone else on my address book? I tried several times and
the same ad...Signature formatting problem
I have a HTML file that I include in my WLM signature file. When I open the
file in IE8, everything looks perfect. But, when I make that file my
signature file, all the text lines are double spaced. My contact data
normally measures about 1" in height (when viewed with IE) but when it gets
plugged into my Signature file, it's 2" and double spaced.
Any help appreciated,
Are you using <p> tags?
Can you post the code? x out private information.
MS MVP- Windows Live Mail
"Tom Brown" <Burner2_DELETE_NOSPAM@att.net> ...conditional printing? macro?
here's my situation, i have a workbook with many sheets in it, each sheet if
the default print shortcut button was used would print to 4 pages of A4
the data on these sheets varies everyday, someday's only 1 page per sheet
would be full of data others 2, 3, or 4 etc. how could i write a macro that
would be able to print only the needed pages?, each page, if it contains any
data would have a value in a totals cell.
so i would need code along these lines
IF(OR(total1="",total1=0),"don't print","don't print"...Automatically format
I have a workbook for personal banking purposes. When I log on to my bank, I
copy the latest lines of my statement and paste into my workbook.
I use Paste Special | Unicode text and my columns are filled appropriately.
The only problem is that the last column, i.e. the balance, does not format
to two decimal places as I had already set up.
How can I automatically paste in the right format, please? Do I need a
After pasting your data:
Select the entire column (click the column heading)
Find the button on the "Home" tab (xl2007) or on a toolbar (xl2003) that looks somew...Am I missing something on manual duplex?
I have used Word 2003 to produce an A5 booklet....and in the print
dialogue there is a "manual duplex" option. Using that, Word kindly
prints one side of the page, then it tells me when to remove the
paper, turn it over and it prints the reverse. Nice and easy.
I can't seem to find the same in Publisher....of course, Publisher
being part of the same Office Suite would mean that the dialogues
are different to Word!
I have downloaded and installed the add-in for duplex printing but
that doesn't seem to work half the time....there's no help file
anywhere and no walk-throu...Conditional Formatting in Excel 97
I am trying to set-up a conditonal formatting for a date
and my guess is that I will have to use a formula rather
than a cell value, however, I don't know how to do that.
I have a start date e.g. 01/01/03 and if that exceeds 1
year (on 02/01/04) than I want the date to change let's
say in red.
Is this possible without having to add further columns to
calculate the days left (e.g. =DAYS360(TODAY(),D4) first?
"Nicole" <Nicole_Szalasi@hotmail.com> wrote in message
> I am trying to set-up a conditona...something more about it
thanks for your help.
sorry to let me ask something more about it.
Function getval(v, chara, chara1, chara2, chara3, chara4)
getval = v
v = 123
c = "ok"
c1 = "ok"
c2 = "ok"
c3 = "ok"
c4 = "ok"
c5 = "ok"
ActiveCell.FormulaR1C1 = "=getval(" & v & "," & c & "," & c1 & "," & c2 &
"," & c3 & "," & c4 & ")"
'ActiveCell = "=getval(" + v + "," + c + ")"
End Su...Conditional Format Question
Suppose I have a cell or range of cells (formula or constant) formatted for
numeric appearance, say two digit decimal. How would I apply conditional
formatting to show the cell(s) as blank when its value = zero?
Using Conditional Formatting:
CF1: Cell value is equal to 0
Format1: <font>/<same color as background>
Use Custom Formatting: Format/Cells/Number/Custom
In article <eoC0EtTiHHA.4872@TK2MSFTNGP03.phx.gbl>, "PT" <firstname.lastname@example.org>
> Suppose I have a cell or range of cells (formula or co...saving format
i have a spreadsheet that i backup & copy to a new folder but the cell
formulas are replaced with the cell values. how do i keep the formula
Save two copies???
One with the formulas and one after the formulas have been converted to values.
> i have a spreadsheet that i backup & copy to a new folder but the cell
> formulas are replaced with the cell values. how do i keep the formula
Or are you saying that when you make the backup copy the formulas are somehow
changed to values...something accessing my HD once per second
Sometimes something accesses my HD once every second for long period
of time. Is there an easy way to find out what this is?
Replace you know what by j to email
Jan Philips wrote:
> Sometimes something accesses my HD once every second for long period
> of time. Is there an easy way to find out what this is?
Easy is relative...
CurrPorts: Monitoring TCP/IP network connections on Windows
TCPView for Windows
Wireshark � Go deep.
Jo...Formatting Dates in a sheet imported from ACCESS
I have imported a report from ACCESS 97 into EXCEL 97 and this worked fine.
However, when I tried to format the dates in EXCEL, nothing happens. I can
format other cells, just the existing date cells won't change unless I
retype the information. Seems almost like the format is locked??
Please advise how I can format these cells.
The dates are being treated as text. There are some techniques here:
that you could try, on a copy of your workbook, to convert the text
dates to real dates.
Orlando Omizzolo wrote:
>...Conditional Format #9
I have a 5-character sting in all of the cells in row H. I would like to
conditional format the sheet to highlight those cells where the third
character in the text string equals "D". Is there a way to do this?
Any help will be appreciated.
I meant to say column H; not row H. Sorry.
Jerry <Jerry@nospam.com> wrote in
> I have a 5-character sting in all of the cells in row H. I would like
> to conditional format the sheet to highlight those cells where the
> third character in the text string equals &quo...Date Format 11-19-09
I work for a computer software company. We export data to Excel. We have a
client in South Africa where the date is getting muddled when it is exported
For example, if the date read 19/11/2009, when exported to Excel it would
read 2019/11/09 (as though it is changing the format from DD/MM/YYYY ti
I have tried changing the Regional settings and the language/date formats in
Excel but this does not seem to rectify the issue.
Has anybody ever experienced this before? Any help would be gratefully
(Just to confirm, this is not an issue...Help is needed with Conditional Formatting drudgery
I am trying desperately to reduce mouse clicks (and
carpal tunnel syndrome risk!) with the following problem:
I have 90 SETS of three price calculations, each trio
equipped with its own drop down list which determines
whether certain cells should be blacked out. The
calculations are distributed among 30 tabs, an average of
3 sets of calculations per each tab.
I have been copying the blank forms over the different
tabs and I have been manually adjusting (a) the cell
links for each drop down list and (b) all the conditional
format formulas (I have been using the format painter to