Conditionally format another cell?
Is there a way to change the background color of a cell based on the
contents of a different cell?
Just use a formula that refers to the other cell.
If I want X99 to change depending on what's in A1.
I select X99 and then format|conditional formatting
> Is there a way to change the background color of a cell based on the
> contents of a different cell?
...Can't get 24 hour format to change to 12 hour in calendar
Operating System: Mac OS X 10.5 (Leopard)
Email Client: Exchange
I have tried several times to change the International format setting to have a 12 hour am/pm set up in Entourage but the format still stays 24 hour in the Calendar display itself. Although the actual entries are in 12 hour format and Entourage uses the 12 hour format for everything else except how it shows the actual calendars. <br>
Is there something else I need to do to have this display correctly in the calendars? <br>
I'm using 2008 Web Services Edition. Love this produ...Time Format.
I have a column with numbers that represent seconds, for example 109.
How can I change this number (109) to this format, 0:01:49
and format it as h:mm:ss
"GEM" <GEM@discussions.microsoft.com> wrote in message
>I have a column with numbers that represent seconds, for example 109.
> How can I change this number (109) to this format, 0:01:49
=A1/(24*60*60) and format as [h]:mm:ss
"GEM" <GEM@dis...International date formats
When performing an Edit, replace on the cell value
10.09.03 (a system genereated date vale of 10th Sep 03)
replaceing the . with /, Excel (and in VBA when recorded
as a macro) sometimes changes the cell value to 09/10/03
(9th October 03). This is driving me potty and have spent
a vast amount of time on it trying to work out what Excel
My Regional settings are set fro the UK.
Any ideas anyone?
"Mike" <firstname.lastname@example.org> wrote in message
> When performing an Edit, replace on the cell value
> 10.09.0...convert from .xls format to .csv format
Can anyone help me to convert .xls to .csv without changing the
content. I want the leading zeros to remain in the csv.
Thanks in advance
m_ravindran's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28668
View this thread: http://www.excelforum.com/showthread.php?threadid=483430
take a look at the Text Write facility at
>...YYYY format displaying wrong date
I admit I'm confused by this. I'm using Excel '97 (Yes, yes, I know. Upgrade.
But this is a client's system over which I have no control.)
The client supplied me with a spreadsheet containing a lot of data. I need
to make the data consistent for it to be usable. There is a column containing
only the year which is currently defined as Text. I want the data to be Date
type with a "YYYY" format.
I managed to do this somehow in another spreadsheet of similar data. In the
spreadsheet I'm currently working on, when I change the column to Date type
with "YYY...Area chart date format
I have an area chart that plots quarterly data. I want to show only years on
the x-axis, so I changed the interval to 1 year and the number format to
'Custom yyyy'. However this is shortening my dates, so 2020 becomes 202.
Bizarrely, 2021 and 2031 show up in full, but other dates are only shown by
their first three digits.
I suspect the space allotted for each label isn't quite long enough, so the
last character is truncated. I know all digits are the same width, but
sometimes, especially when the font size is small, "1" is narrower than the
rest, whic...Formatting Formulas
I have a series of formulas that creates English Language labels out
of a variety of VLOOKUP's and the ever helpful & sign.
Basically, I used the VLOOKUPS to get "Big", "Bag", "Delicious", and
"Apples" in cells A1-D1, respectively.
Then, in E1, I've got
=A1&" "&B1&" of "&C1&" "&D1
so that it comes out
Big Bag of Delicious Apples
In E2 it says Small Bundle of Tart Grapes and E3 says Big Bundle of
Tart Apples, etc.
What I'd like to have, though, is some sort of formatting set u...Format a single digit like "007"?
In the query result, I need to see (for example) a single digit number
(say that "7") as "007".
What is the function for this? I use FormatNumber function for other
Can you help me please?
try the following:
> In the query result, I need to see (for example) a single digit number
> (say that "7") as "007".
> What is the function for this? I use FormatNumber function for other
> Can you he...Protection Formatting
Is it possible to protect only the format of any
particular cell. Like border, shading, font etc.etc. Thus
even if any valus is entered/ pasted into this cell, the
original formatting will remain unaltered?
"Kedar J" <email@example.com> wrote in message
> Is it possible to protect only the format of any
> particular cell. Like border, shading, font etc.etc. Thus
> even if any valus is entered/ pasted into this cell, the
> original formatting will remain unaltered?
Does anyone know the process of receiving retainers and
then applying them to the invoice. I need thenm to show
up on the invoice.
Any help is appreciated.
In microsoft.public.money, bcarne wrote:
>Does anyone know the process of receiving retainers and
>then applying them to the invoice. I need thenm to show
>up on the invoice.
>Any help is appreciated.
Not many Business users provide answers.
...Pivot Table formatting #6
When I format my pivot tables I have "preserve formatting"
checked under "PivotTable Options", and "Autoformat Table"
unchecked. Even so, I either lose portions of my
formatting, or different formatting is applied when I
refresh. Does anyone know what I am doing incorrectly or
have any other suggestions?
Other things to try -- if they don't work, you could record a macro as
you refresh and reformat the pivot table. Then, run that when you want
--Instead of selecting the cells to format the numbers, right-click the
field but...Format Cell #5
I have a spreadsheet that is used by multiple users. I need to see if there
is a way to separate floor numbers and room numbers.
In column "D" there will be:
The floor number is the first number in the column. I need to see if this
can be separated to 5th floor, 6th floor, ICU (which may also be listed as
3014, 3012 etc. or ICU). I need to have a formula to count the number that
went to the 5th floor, 6th, etc.
I thought that if there is a formula to enter to separate the floors instead
of adding another column and having e...Upgrading an .mdw to 2003 format?
I have upgraded my frontend & backend .mdb from 2000 to 2003. Do I also
upgrade the .mdw in the same way?
Thanks so much.
On Thu, 27 May 2010 16:38:01 -0700, sweeneysmsm
>I have upgraded my frontend & backend .mdb from 2000 to 2003. Do I also
>upgrade the .mdw in the same way?
Nope. Just use it. I'm using an Access97 MDW with no problems whatsoever.
John W. Vinson [MVP]
Thanks so much!
"John W. Vinson" wrote:
> On Thu, 27 May 2010 16:38:01 -0700, swee...Formatting
I am working in Microcsoft Publisher.I have typed in text and then inserted a picture. How do I get the text to wrap around the picture. When working in Word I could just insert a picture and the text could be wrapped around it. In publisher the text is confined to a text box. Whatever I seem to do the picture remains the same only the size of the text box alters and text will not wrap aound the picture.I have tried using edit points but have got in a real mudle
I am desperate now. Any help please?
What version Publisher?
Are you sending the image to the front? Click the dog icon, (picture t...Any way to set View format for all message views?
I have the standard Inbox folder plus about 20-30 other message
folders where I store messages by subject.
Is there any way that I can set up a custom View and apply it to ALL
message folders in such a way that if I change that View (add or
delete fields, change field width or order, etc.), all Views will be
Using Outlook 2000 on Windows 2000
LurfysMa <firstname.lastname@example.org> wrote:
> I have the standard Inbox folder plus about 20-30 other message
> folders where I store messages by subject.
> Is there any way that I can set up a custom View and a...Lost formatting in e-mails composed in Outlook
When I compose e-mails using Outlook--either in Word or not in Word--the recipient receives the e-mail with all formatting stripped from it. I can't use color, different fonts, italics, etc., because all disappear between "Send" and "Receive" even when I send it to myself. I finally found and chose the option of NOT removing formatting when sending, no matter what program it is sent to, but it removes all formatting anyway. Anybody know how to make it leave all the formatting in e-mails, either in Word or not in Word, when sent? Also, anybody know how to turn off AL...Save graph formatting
I have formatted my pivot graph but every time I refresh or change the pivot,
the graph reverts back to a different default. I have saved it as a default
Have you tried unclicking the checkbox for 'AutoFormat table' in the
PivotTable Options window?
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
> I have formatted my pivot graph but every time I refresh or change the pivot,
> the graph reverts back to a different default. I have saved it as a default
>...how to enter phone number in special format?
i created a form with a textbox where the user must enter his phone number,
bank account number etc .... Does it exist a way to make the textbox with a
specific format e.g. 'ddd/dddddd' (d = digit) for phone or 'ddd-dddddd-dd'
for bank account number?
Thanks for tips
> i created a form with a textbox where the user must enter his phone
> number, bank account number etc .... Does it exist a way to make the
> textbox with a specific format e.g. 'ddd/dddddd' (d = digit) for
> phone or 'ddd-dddddd-dd' for bank...Migrating from Goldmine
I'm migrating from Goldmine to CRM 4.0, and will ultimately have csv lists
of accounts, contacts, leads, activities etc.
In using either import method, it always fails when starting out, as if i
import accounts first, when it tries to import the primary contact, since
there are no contacts to lookup, it fails.
Vice versa if i start by importing contacts, i cant automatically assign a
parent customer, as the lookup fails because the accounts aren't imported yet.
If i import accounts, then import contacts with parent customers listed, it
works, but i've lost my ...Text format as percentage
I have a userform that I use multiple times. When this form loads the first
time, I use the following code to format the text to a percentage:
Private Sub TextBox21_afterupdate()
If TextBox21.Value <> "" Then
TextBox21 = TextBox21 / 100
.Text = Format(.Text, "0.00%")
however, if I ever focus back on that textbox, and then focus off, it
crashes the code. how would I add code that basically says "if the number is
already in percentage format, ignore the rest of ...Conditional Format-possible to show overridden formulas?
I've got a cell that has a formula in it and I'd like to set up a
conditional format so that it turns a different color if someone
instead overrides the formula and inputs a value - is this possible?
You could have a UDF that checks if it is a formula, and use that in the CF
Function IsFormula(rng As Range) As Boolean
IsFormula = rng.HasFormula
and use like =IsFormula(A1)
(remove nothere from the email address if mailing direct)
"GretOgrady" <email@example.com> wrote in message
news:1111425009.372156.107570@f14g2000cwb....Retained Earnings posting issue
We have a customer who closed their fiscal year as of Sep. 30, 2008. They
made some adjusting journal entries over the weekend and posted the batch.
While attempting to reuse the same batch ID for additional entries, they
found that one entry from the previous posting was still in the batch and the
batch was in Batch Recovery. The edit list showed the error message that the
Retained Earnings account was not a Balance Sheet account. We found that the
RE account had been changed in GL setup. The problem is that of the six
entries in this batch, 4 should not have posted due to this err...Formatting in a Formula
Does anyone know a way I can add a fill color to a cell if the value exceeds
a threshold (ie an alert)?
Yes, use conditional formatting
click in the cell in question
choose format / conditional formatting
cell value is
choose greater than (or whatever)
enter the value
click on the format button
set the formatting
click Ok, and Ok again
"Frank" <Frank@discussions.microsoft.com> wrote in message
> Does anyone know a way I can add a fill color to a cell if the value
> a ...formating comments
Can anyone tell me how to change the default format for comments?
You can change the font size and colour of new comments
(but not the font) by changing the settings in your Control Panel.
(Instructions for Windows XP)
1. Right-click on the desktop, and choose Properties
2. On the Appearance tab, click Advanced.
3. From the Item dropdown, choose Tooltip
4. Choose a font Size, click OK, click OK
Note -- this will also affect your Tooltips formatting
Or, you can use a macro to insert a comment with a specific font
'adds TimesNewRoman comment or posi...