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 "4000". How can I explicitly specify formatting in my "=" statement above? Thanks! ="The value is " & TEXT(A1,"#,##0.00") & "." Forgot: add the $ sign like this: ="The value is " & TEXT(A1,"$#,##0.00") & "." Use text ="The value is "&TEXT(A6,...

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? ...

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?? Range("A1").Select Sheets("atl").Select Range("A1").Select Selection.AutoFilter Field:=9 Selection.AutoFilter Field:=8 Selection.AutoFilter Field:=11 Selection.AutoFilter Field:=9, Criteria1:="1" Selection.AutoFilter Field:=13, Criteria1:="3" Columns("J:J").Select Range("J:J,B:B").Select Selection.Copy Sh...

I have created several Excel files ranging between 2Mb & 4Mb i filesize. 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 ...

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. Choose Tools>Options Select the General tab Remove the check mark from 'Function tooltips' Click OK NKH.UofW wrote: > 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...

Hi, 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...

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? ...

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 equals. Any pointers to an explanation? Thanks JJ is it += or =+? When I type formulas I always use +, like +if(a1, etc) and they change to =+if(a1, etc) "JJDuffin" wrote: > 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 > equals. > > Any pointers to an explanation? > > Thanks > >...

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,"...

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. -- Thank you Investigate the help files for VLOOKUP. I think that's what you need. "Tomwireless" <Tomwireless@discussions.microsoft.com> wrote in message news:DA29DC49-0E8D-4A74-B61E-B448BDF067CD@microsoft.com... >I Have 2 sheets, a database of items sheet and a form with a combo bo...

This formula works: =SUMPRODUCT(--((E3:E1000=6)+(E3:E1000=6.5))*(M3:M1000="w")) I tried a lot of different ways, but I couldn't find a way to shorten this part: (E3:E1000=6)+(E3:E1000=6.5) I'm sure there is a way to make that more efficient. Thanks 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. either =SUMPRODUCT(((E3:E1000=6)+(E3:E1000=6.5))*(M3:M1000="w")) or =SUMPRODUCT(--((E3:E1000=6)+...

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. Thanks Vince, 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...

CAN YOU COPY A SUBREPORT AND CHANGE THE DATASOURCE? -- phm HISTORY Phm question: Hello, 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. Please help! phm - darlyS answer: 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. Daryl S Phm ...

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? Thanks 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? > > Thanks ...

Hi, 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 rack. 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 correct? I should mention that I was pretty much the only person on the LAN at the time. I'm afraid I don't know what I'm talking about here... Cheers, James Hello JimLad, See here about slow copy problem...

Hi: 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. jcasilio@comcast.net. -- jcasilio -------------------------------------------...

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 ...

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" <earthling@planet.earth> wrote: >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...

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. ...

Hi 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". eg. 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...

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. (Array) =SUM(IF(MONTH(IF(B1:B5="",A1:A5,B1:B5))=1,1,0)) 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 One way ...

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 appreciated. Thanks! "Newby07" wrote: > I...

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...

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: =LOOKUP(9.99999999999999E+307,Sheet4!E3:E30) 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? Hi, You are going to need VBA, the Excel spreadsheet can't determine if a cell is ...

Hello there, 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, Kind regards, H.G. Lamy Hi look at the following examples http://peltiertech.com/Excel/Charts/ChartByControl.html http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/index.html ...