format cell to show zero if not a positive number
How can I format a column of figures to show that if the result is
zero or negative amount it will just show a zero or be left blank.
I am attempting to show the difference between figures that have been
invoiced to figures that have been added and thus need to be invoiced.
Use a custom format like
MS Excel MVP
"TechnoGram" <email@example.com> wrote in message
> How can I format a column of figures to show that if the result i...Looking for cells containing numbers
in a column with cells containing either text or numbers I have to exclude
those with text in an arithmetic formula as is runs through the whole column,
hence avoiding an error as a result when reading text cells.
A cloumn holding an intermediate result of the reading as "0" or "1" or
something will be just fine.
You might be thinking of something like this ?
Assuming the col with text/numbers is col A, A1 down
Put in B1: =IF(ISTEXT(A1),0,A1)
Copy B1 down
Then use col B instead of col A for downstream calcs
GMT+8, ...Count with Hidden cells
Is there a way to count the number of text filled cells excluding the cells
that I have hidden?
Depends on the version of excel, excel 2003 works like
won't count hidden cells
(No private emails please)
"Stretch" <Stretch@discussions.microsoft.com> wrote in message
> Is there a way to count the number of text filled cells excluding the
> that I have hidden?
What does the 103 stand for?
"Peo Sjoblom" wrote:
> Depends on the v...How do I insert Roman Numerals?
New to Word 2007 would someone please explain to me how to insert Roman
Numerlas in my document?
"ckj" <firstname.lastname@example.org> wrote in message
> New to Word 2007 would someone please explain to me how to insert Roman
> Numerlas in my document?
Just type them...
Type a number e.g. 21, select it and run the following macro
Dim oRng As Range
Set oRng = Selection.Range
oRng.Fields.Add oRng, 34, oRng.Text & " \*Roman"...Copying and pasting a series of cells
When I copy and paste the following cell
to the remainder of the days of the month, what I get is
But what I want to get is
I'd even settle for
whereafter I could just modify the fo...Spliiting a cell
I have a column and its contents is a question which states...
What is the Cost of Superbills? Quantity of:
I would like to split this lone cell and have the quantity right next to the
question. Can I do this???
Make sure the column to the right is empty, then do data>text to columns,
select fixed width, click next and click where you want the split. If there
can be different strings with different lenght you can use a formula to find
for instance the question mark
> I have a column and it...Not plot a data series in a chart but still display in data table
MS Excel 2007
I've somehow managed to display only two data series on my line chart. They
still display in the data table. How did this happen? More importantly, how
do I reverse it. I don't want to recreate chart.
I see the data series not plotted on chart in the Chart Elements and when I
select the series, the source data is highlighted; it's in the data table.
How do I make it visible?
HAve you perhaps simply formatted the data series as "no line, no marker"?
If so, you can format series, add some color.
Looking for some advice on SQL 2005. I have a table that will usually be
populated by an SSIS package. I want to set the "loaddate" column to the
current time after a record is inserted. Should i do this via trigger or
should i just build a step in the SSIS package to update the column after the
If trigger is the way to go, what is the syntax to create the after insert
Thanks in advance.
You can create a default constraint on the table set to CURRENT_TIMESTAMP. That will handle the automatic date
assignment without any need for coding.
-...inserting hrs and minutes
I have a cell in my time card that displays total weekly time -ex-
"40:15" is there a way to make it more like this...40hrs,15mins
Message posted from http://www.ExcelForum.com
Use a custom format hh"hrs",mm"mins"
"-Brian-H- >" <<Brian-Hemail@example.com> wrote in message
> I have a cell in my time card that displays total weekly time -ex-
> "40:15" is there a way to make it more like this...40hrs,15mins ?
> ...OWA 2003 Front End logout error
Only on my OWA 2003 front end server do I get this error:
If you click the Logout button in OWA I get the login box again. If you
click cancel it gives you a blank page with the error "Access is denied, but
if you use the Back button in your browser you are still logged in.
All three of me Exchange servers are 2003 on Windows 2003. If I logon to
OWA directly to non-Front End servers the logout button works correctly.
It's only on my front end server and happens using both http or https.
Thanks for any advice.
Make sure that on that FE server, you have anonymous set on the Exch...Need fast way to move from cell to home
What is the quickest way to move from a cell to home (A1) in Excel? I
thought you could click on something or hold down two keys in order to
accomplish this, just can't remember. Thanks.
Microsoft MVP - Excel
"Debbie" <Debbie@discussions.microsoft.com> wrote in message
> What is the quickest way to move from a cell to home (A1) in Excel? I
> thought you could click on something or hold down two keys in order to
> accomplish this, just can't remember. T...insert an interactive excel file into word web page
I'm trying to insert a excel file into a word document with text, and then
save it was as a web page, but I want to keep the excel part interactive.
...Worksheet name in cell formula
I asked this question a while ago and got a prompt answer which I thought
was what I wanted but alas its not...
I want to be able to change my worksheet names.....ie: from Sheet 1, Sheet
2, etc etc.....to something more meaningful.....eg Sales, Expenses....etc
etc.... and have these changes reflect on the worksheet.
For example, I might want Sheet 1 Cell A1 to say.....This is the Sales
worksheet (assuming I have renamed it to Sales).
The answer I was given some time back worked.....but it changed the formula
on every worksheet to reflect the name of the last changed sheet.
By this I mean.....Insert | File > Attachmnet-Button Drop Down ;What is the difference between Insert and Insert As Attachmnet
re: "Outlook2003, File-Insert-Options"
On making new-email with Attachment-File(s),
File Menu | Insert | File >>> (Brows and select File to insert )
then we can see the button "Insert", and write side
Drop Down Arrow lower-right side of Dialog Box;
If it clicked,
we can see three options as follows:
Insert as text
Insert as Attachment
I can not recognize/understand the difference
between "Insert" and "Insert as Attachment"
What is the difference between Insert and Insert As Attachment ?
I would appreciate y...Buying a mutual fund with a 5.75% front end load.
Nothing I'm doing seems to be giving me sensible answers. I assumed I'd take
the purchase price time 5.75% and put that in commission. Or, do I jack up
the purchase price by 5.75% and then take that amount back out in
Thanks in advance,
In microsoft.public.money, Harold Morgan wrote:
>Nothing I'm doing seems to be giving me sensible answers. I assumed I'd take
>the purchase price time 5.75% and put that in commission. Or, do I jack up
>the purchase price by 5.75% and then take that amount back out in
I would enter the numb...Inserting Hyperlinks in a Protected Sheet
I run Excel 2000 and I have a protected worksheet that I share wit
users in my organisation. I want to allow the insertion of a hyperlin
to a specific file type within a specified directory on our server.
1.Protection on disables the insert hyperlink command. Can this b
overcome with worksheet activate code?
2.Can I limit the types of files (preferably by requiring the file t
meet a mask format eg "z-*.xls")?
3.Can I limit the directory that can be linked, by referring to
pathname stored in a cell on the active sheet?
Would appreciate your suggestions.
Thanks S...VLOOKUP and cell color problem.
I have a spreadsheet to create a quotation/estimate and I use the
VLOOKUP function to retrieve the price of an item on another
worksheet. In that worksheet, I have several pricelists from various
suppliers but one column gives me the lowest price and changes the
cell color associated with the supplier so I know from which supplier
the price comes from.
Now my main quotation page displays the cheapest price but does not
pass along the cell color which I need so i can tell, by looking at
the quotation, which supplier i need to order each item from.
Is there a way to pass along not only the va...Split a cell
Am I able to split a cell, making it 2 as in Microsoft
Select a cell or entire column you want to split, then
select Data - Text to Columns. Make sure you have an empty
column on the right of the column you're spliting or there
won't be any room for the data you're spliting off.
>Am I able to split a cell, making it 2 as in Microsoft
mvpearl omitthisword at verizon period net
"Priscilla" <firstname.lastname@example.org...Not plot zero on line chart without using NA?
Is there any way to have a line chart ignore the zeros other than manipulate
the formula to show NA?
If there were, would we settle for using NA()? We've been asking for
something better for several versions of Excel.
There are two options: use NA() in the formula, or delete the formula
Peltier Technical Services, Inc.
> Is there any way to have a line chart ignore the zeros other than manipulate
> the formula to show NA?
That's what I thought, but I figured I'd take a shot at ...showing data values on chart
in excel when mouse stand on chart,excel is showing the data values of that
point.how can to do this action in access?
Exactly the same. When you hover the mouse over a data point for a little
amount of time, a yellow bow should come up describing what's in your data
> in excel when mouse stand on chart,excel is showing the data values of that
> point.how can to do this action in access?
Where you insert chart?i insert chart on report,and i can't to see data value
on data point.
"Steve"...How to copy specific columns until end of data
I'm trying to import data from 2 workbooks into a summary (combination)
workbook. The trouble I'm having, is I want to import only certain
columns and have excel know to stop at the last row with data.
For example, from Workbook1 I want to import columns A, B, C, F, and G
(from Sheet2). I want it to copy up to the last row containing any
data--right now that's row 1046, but next month it could be row 1267.
In workbook2 I'm fine with it copying the CurrentRegion--all columns
are important. BUT, it doesn't include the title/category heads in the
first row (Salesperson, ...Insert with a where condition
Hi, sql 2005
I have an insert statement that is ignoring the where condition. That is, I
want to insert records when they do not already exist in the destination
INSERT INTO dbo.tblmnuGroupPerm
FROM dbo.locmnuTabFunction AS ltf
ltf.mtfID NOT IN
FROM dbo.tblmnuGroupPerm AS gp
Any ideas or recommendations appreciated :-)
It's OK... <oops "redFace">I did not correctly se...Insert
I want to overtype in a Publisher text box. I find I can only insert. The
"insert" key doesn't do anything.
...how to insert data in a table
I am creating an access database in which I want to insert data in
already created table.
Vikky <email@example.com> wrote in news:1194124711.012302.269990
> Hi Exprets;
> I am creating an access database in which I want to insert data in
> already created table.
> Kindly help.
Data from where? Do you want to import it from excel, from a text
file, copy it from another table or type it in manually?
PA is y I've altere...Uploaded Checking acct data
Just successfully uploaded all 2004 (YTD) for main checking account.
That said, why does the calender not reflect the payments?
My calendar shows nothing just "Add New"
Is there some steps that I need to do or is the calendar information updated
only if I, step-by-step, pay bills in the system?
The data that you have uploaded will be in the account register, not in the
bills/deposits area which populates the calendar. To get it in the calendar,
you'll need to add it to the bills/deposits area. You can do this by either
entering it manually or by...