How to specify numeric format in formula?
I have a cell with the following:
="The value is $" & A6 & "."
Cell A6 is an integer and it's formatting is set to look like
"4,000.00". In the cell above that references A6 the value looks like
How can I explicitly specify formatting in my "=" statement above?
="The value is " & TEXT(A1,"#,##0.00") & "."
Forgot: add the $ sign like this:
="The value is " & TEXT(A1,"$#,##0.00") & "."
="The value is "&TEXT(A6,...Locked for Editing 03-12-10
There is an excel workbook that is locked for editing and it has at this
state for over a week. I need to be able to forcibly delete this file,
renaming as a different file name is not an option. Is there anyway that I
can forcibly delete this file?
...Copying and pasting values error 1004
Whenever i run this piece of code i get the error
"paste method of worksheet class failed" runtime error 1004
This is in excel 2003 and i did not have this problem in 2000. Coul
anyone shed any light on this??
Selection.AutoFilter Field:=9, Criteria1:="1"
Selection.AutoFilter Field:=13, Criteria1:="3"
Sh...Long Delay copying xls files in Windows Explorer
I have created several Excel files ranging between 2Mb & 4Mb i
Why does Windows Explorer treat xls files differently from every othe
file type, in that when I single click on one of those xls files, o
right click on one of them, it seems to take forever (up to over
minute) before I can do anything else.
For instance, I want to make a backup copy of one of my xls files, so
go to the folder using Windows Explorer, then I right click on an xl
file, I then have to wait for up to a minute or so, before the dro
down list appears, then I can choose "Copy", and the drop down ...Formula pop-up box
Using Excel 2002, when I start to enter a formula in a cell, for example,
=sum( I get this little box that pops up right in my way, showing me how I
might like to complete it the formula. Does anyone know how to turn this
thing off so it doesn't pop up all the time? Thanks very much.
Select the General tab
Remove the check mark from 'Function tooltips'
> Using Excel 2002, when I start to enter a formula in a cell, for example,
> =sum( I get this little box that pops up right in my way, showing me how I
> might lik...Copy paste code for a #REF
I am looking for a workbook code that runs when the file is opened. In col
H I have a formula (=INDIRECT(G8)) which searches data from another series of
folders. If the source folder is open it returns either a value or 0. If
the relevant source file is closed it returns a #REF.
What I would like is a code that looks through all the cells in Col H, if it
finds a numerical value or 0 it does a copy/paste values on the cells, if it
finds a #REF it does nothing.
The file is a update folder so the REFs will be in the future and the values
in the current/past which i nee...Formula #24
I have a workbook that is divided by dates. I want to create or use an
existing formula that updates my workbook. When I choose a date, I want the
formula to link certain columns of information and transfer the data to the
corresponding sheet for that date. The column in question is a series of
dates in a drop down box. Is there any hope of doing this?
...+= prefix to formula
I've received an Excel sheet which has many cells with formulae prefixed by +=.
I cannot find any explanation of the significance of putting plus before
Any pointers to an explanation?
is it += or =+? When I type formulas I always use +, like +if(a1, etc) and
they change to =+if(a1, etc)
> I've received an Excel sheet which has many cells with formulae prefixed by +=.
> I cannot find any explanation of the significance of putting plus before
> Any pointers to an explanation?
I am trying to create a scheduling formula which alerts me if there are
scheduling conflicts. I have 4 different dates which a 5th date cannot
match. In addition, it cannot conflict with the 4th date and including 35
days prior. For example:
B2 = Inventory Date (cannot conflict with this date and 35 days prior)
C2 = Reset 1 Date - (cannot match this date)
D2 = Reset 2 Date - (cannot match this date)
E2 = Reset 3 Date - (cannot match this date).
The formula I have is:
=IF(F2="","",IF(F2=C2,"Reset 1 Conflict",IF(F2=D2,"Reset 2
Conflict",IF(F2=E2,"...copy data from one sheet to another using a combo box
I Have 2 sheets, a database of items sheet and a form with a combo box to put
the record I get from the database. I want to pick a record from the database
sheet and add it to my form and have it bring the whole record with it to
the form.both sheets have the same fields.
Investigate the help files for VLOOKUP. I think that's what you need.
"Tomwireless" <Tomwireless@discussions.microsoft.com> wrote in message
>I Have 2 sheets, a database of items sheet and a form with a combo bo...Shortening the Formula
This formula works:
I tried a lot of different ways, but I couldn't find a way to shorten
I'm sure there is a way to make that more efficient.
You do not really need the double negation. It is used to transform
TRUE/FALSE to 1/0 - Excel does this whenever a math operation is performed
on a Boolean value. But you are multiplying so the double neg is redundant.
or =SUMPRODUCT(--((E3:E1000=6)+...Half way there, deleting numbers but keeping the formula
Thanks Paul and Niek...I need to clarify. I have some blocks with numbers and some with numbers and formulas. I want to keep the formulas, and remove the numbers, so I and just go in and add this months numbers and start with a clean sheet....but not have to replace the formula. I hope I am explaining this correctly. When I did the edit...., special...., it only removed either or but would not leave the formula.
There's no way to do what you're asking, per se.
The values that you see are the result of the formulas.
One way around this is to rework all of your for...Copying Subreports
CAN YOU COPY A SUBREPORT AND CHANGE THE DATASOURCE?
I have eight regional subreports to developed. I would like to develop one;
and, for the others, just copy the original one and change the datasource.
Is this possible? So far, all I see on the copied, pasted report is a
blank, subreport control.
It would be easier to create one report that will work for all regions, and
include a parameter to choose which region to run the report for. Only one
report to maintain that way.
Phm ...Formula question in a report
I have a formula to add travel hrs and another to add work hrs.
Can you give me the formula to add the two together?
Try this --
=Sum(Nz([TRAVELREG], 0) + Nz([WRKREG], 0))
Build a little, test a little.
"Fly Boy 5" wrote:
> I have a formula to add travel hrs and another to add work hrs.
> =Sum([TRAVELREG]) =Sum([WRKREG])
> Can you give me the formula to add the two together?
I've been trying to copy about 60GB of small files (tens of thousands
of them) in a deep file structure from a NAS box to a Virtual Server
2008 using both Robocopy and Explorer. Both servers are in the same
The speed varies from 3MB/s to 15MB/s, but averaging under 10MB/s. To
an ignorant person like me that seems a bit low. Does that seem
I should mention that I was pretty much the only person on the LAN at
I'm afraid I don't know what I'm talking about here...
See here about slow copy problem...complicated IF THEN formulas
I am a newbie that has been struggling to try to figure out how to
create some more complicated IF THEN formulas in Excel.
For example; I want to get a value of 2 for 1<A30 <= 200, 3 if
200<A30<=300, and 4 if 300<A30<=500.
I have spent hours trying to figure it out. I can't understand what I
am doing wrong. I have tried all of the Help files etc. etc. Maybe I am
just too dumb to get the syntax correct. Would a kind soul please send
me a message with the formula. Thank you very much.
-------------------------------------------...Rewriting a formula...
I have a spreadsheet that calculates totals and has formulas in certain
columns. Is is possible to have the formulas work, but not have any
numbers in the cells before I input them? For example, in column D, I
have the simple formula =D9+E9, and so on and so forth down to
=D25+E25. I may only put itmes in until D11. So in D12 thru D25, I
have $0.00. Can I rewrite this formula, possible an IF/THEN statement
to add the two columns, but not show the $0.00 in D12 thru D25, since I
did not have to do any addition? My main goal is to not have this info
in D12 thru D25 print to make the sheet ...Have you selected "Leave copy of message on server"?
Several times I've seen this asked in response to a request for help, but I
don't recall ever seeing advice on the matter. Which can cause the
problems... leaving a copy, or failing to do so?
On Sat, 20 Feb 2010 20:57:47 -0330, "J. Earthling"
>Several times I've seen this asked in response to a request for help, but I
>don't recall ever seeing advice on the matter. Which can cause the
>problems... leaving a copy, or failing to do so?
The main reason for leaving a copy on the server is to make it...Text in Cell as Formula #3
Peo + Julie Many thanks for your answer with the last query.
I have another similar question; I would like to use values form a
different spreadsheet, and this method [INDIRECT()] fails.
i.e. I type the link (as text) from a different spreadsheet in a cell in the
current spreadsheet and use this method to call a value in a cell.
Appreciate your help.
...if cell starts with characters formula
I need to count cells in a column starting with certain characters. each
cell's data varies in length. I have tried with @countif( but does not work
if the cell contains other characters after the "prefix".
row 20 cell 5 apples
row 21 cell 5 apples red
row 22 cell 5 apples green
row 23 cell 5 plums green
row 23 cell 5 plums purple
totals required for apples = 3 (regardless of colour)
total required for plums = 2 (regardless of colour)
@countif(C20:c30,"plums") gives answer of 1 require answer of 2
@countif(C20:c30,&quo...Help with Date/array formula
Have 2 columns that could contain dates. Column A and B.
Need to count the number of each month in both columns. If
column B is empty, use month in column A. If B not empty use B.
This formula works in every month except JANUARY.
Appears to read blank cells in range as 1(true). Have been fightin
this for way toolong and will appreciate any help
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com
I need the average for a set of survey questions and I cannot figure out how
to get the answers I need and write the formulas. I have 11 survey
questions, 5 choices for each question(1 is bad--5 is best), a combined # of
students(30) for 3 classes...1st class=11 students, 2nd class=9 students, 3rd
class=10 students. My boss says he wants the averages to be in the 4%-5%
range, but my calculations came up with ranges more like 10%, 11%, etc. And
I am no mathemetician or a "formula girl", so any help would be greatly
> I...Slow Performance on Site-to-Site using Folder Redirection 12-23-09
I have a situation that is forcing me to stop using Folder Redirection
on the remote site due to poor performance, mostly slowness. I, on the
other hand have set up a laptop to use offline files on the same domain.
At this point it seems to me that offline files will be the best option
here. The domain consist of an SBS2008 Premium with a remote Standard
Server domain controller. The site to site is using a Sonicwall
appliance. I love using FR, but cannot sacrifice performance and will
hate to think that it was only meant to be used for authentication
purposes. Any ideas? Thank you f...Formula #35
I need to get the value of the cell in the last populated row in a column on
another sheet, and I use a formula like this to get it:
My problem is that I now need to calculate the value from the E column, so
until it is calculated, Excel shows the value to be $0.00 (the result of the
formula before any other values have been entered), so I'm picking up $0.00
as the last value.
How do I get the last value that has been calculated or greater that 0?
You are going to need VBA, the Excel spreadsheet can't determine if a cell
is ...If-formula in series-range of chart-datasource
depending on the "True" or "False"-value of a checkbox in a worksheet,
I need to show one or the other dataseries for the X-axis in an XL-chart.
However, when I try to integrate an If-formula into the series-definition
in the datasource-dialogbox, Excel doesn't accept that.
Is there a way to make a dataseries shown dependent on a condition ?
Thank you in advance,
look at the following examples