How do i remove the hyphens between numbers without having to edit without having to use edit manually. I have a column of 3000 numbers similar to this 0-7643-2300-5, which neds to read 0764323005 Any ideas Regards Ian First, format the whole range of entries as Text. Second, highlight the cells and press Ctrl-H to bring up the Search & Replace dialog box. Search for the hyphen and replace it with nothing. "ian78" wrote: > How do i remove the hyphens between numbers without having to edit without > having to use edit manually. I have a column of 3000 numbers s...

how do i print, say, even-numbered pages in a Publisher newsletter that I have mail merged with Outlook Thank you. What version of Publisher do you have? If you have Adobe Acrobat full version, you can make a PDF file which will give you that option. If you have a quality printer like a Kyocera or some model Canon printers, they have that option in the printer driver. -- The US should free all those illegally held prisoners they are torturing, abusing and denying human rights being held at Guantanamo Bay. ...

I downloaded a Microsoft template called General Ledger (Green, multi-sheet/one per account code plus YTD Budget Summary and Monthly Expenses sheets). The workbook uses the name manager and some other field names that I can't find definitions for. The document worked fabulously until 1/1/10. Now, when I add a line to any of the individual account sheets, the linked data result for the formulas on the Monthly Expenses table disappear. The formulas are there but the result, and the "Accounting" format just go away. No matter what I do, the Monthly Expense...

HI, I have the following formula : =SUMIF(DOME!E170:E173,B20,DOME!F170:F173) I would like to put my cursor over the cell of this formula an click/drag/copy it down the column. In that process I would like onl the middle number to change incrementally by 1 (i.e. B21, B22, etc..) How do I do this? And , if I later do a sort will it screw up the middle number? So far when I've tried it, the outer numbers keep changing. ugh! thanks Regards BAr -- Message posted from http://www.ExcelForum.com Hi Barb! Change the formula to: =SUMIF(DOME!$E$170:$E$173,B20,DOME!$F$170:$F$173) -- Regards...

How can I average certain number of lowest values in a column? The following array formula will average the lowest 5 numbers in A1:A10. =AVERAGE(SMALL(A1:A10,ROW(INDIRECT("1:5")))) Change the 1:5 to 1:N where N is the number of values you want to average. Since this is an array formula, you must press Ctrl+Shift+Enter rather than just Enter when you first enter the formula and whenever you edit it later. If you do this properly, Excel will display the formula enclosed in curly braces {}. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www....

Hi All, I need to reduce a figure by 5% and increase one by £1.50 how do I put this into a forulae. Thanks, kfh If I understand your questions correctly: =YourFigure*.95 for 95% of a number. Example =100*.95 will return 95. For the second part you want to add 1.50 pounds? =YourNumber+1.5 Example: =3+1.5 will return 4.5 The pound symbol should probably be aplied as formatting rather than typed. Format | Cells | Number. Pick either Currency or Accounting and set the appropriate monetary symbol. tj "k f h" wrote: > > Hi All, > > I need to reduce a figure b...

Hi All I'm having trouble with a couple of formulas and I think I've written them wrong?? First Formula: In cell CB183 the formula looks at range($B$37:$H$165) to finds any occurrences of "production"or "installation" or "non commission" within any STRING of text (hence the "*XX*") and then adds those rows in range(CB37:CM165) that match. {=SUMPRODUCT(--ISNUMBER(SEARCH({"*production*";"*installation*";"*non commission*"},$B$37:$H$165)))*CB37:CM165)} The formula works when I type "production&...

I want to make a snap shot of publisher 2003 web page that i created and paste it or import it into a power point slide. In PowerPoint, File, open, files of type, scroll down to "all web pages". -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "jeremiah" <jeremiah@discussions.microsoft.com> wrote in message news:88A46CAD-B3C3-4AE9-BB06-00904545E035@microsoft.com... >I want to make a snap shot of publisher 2003 web page that i created and > paste it or import it into a power point slide. jeremiah <...

Hi Does any one know how to count the number of used columns and rows in a sheet via the object model I know int m_columncount = m_CurrentWorkSheet.Columns.Count; gives the total columns, but what I need is a count of the used colums. Or do I need to write a manual count i.e. manually count how many are used by by checking each element? thanks Andy Hi Andy, The following will get the number of rows in your used range as you would observer using Ctrl+End dim rowCnt as long rowCnt = activesheet.Cells.SpecialCells(xlLastCell).row Take a look at http://www.mvps.or...

/* The following problem is an attempt to create a stored procedure that will act as a datasource for a crystal report. The scenario presented is a simplified version of the main problem. Using the example below, we have two customers which received two different items. The items are serialized and are always returned from the customer. Items sent are tracked in one table. Items received are tracked in another table. We need a list of each customer, the item they were sent, and the serial numbers we have not yet received from them. Now finding a list of customers, items, and seri...

I am setting up a formula that can have a possible 3 results. I was trying to use an "If, Then" statement but that will only cover two of the results. I have a cell that has a drop down menu with four possible coices: blank, "fixed", "rescheduled" and "pending." The problem I have is that I used the formula: =IF(I34="fixed", 3, 1) What I want is to assign a point value to each choice. Fixed = 3 Pending = 1 Rescheduled = 1 Blank = 0 The problem is that with the formula I have a blank cell will still give one point. Any suggestions? -- l...

Is it possible to filter based on the following example: colA Acalypha Acanthocalycium Acantholimon Acanthopanax Acanthus Acca Acer Acer Acer Acer Acer Aceras Aceriphyllum Achillea Achillea Achimenes result after filtering; Acalypha Acanthocalycium Acantholimon Acanthopanax Acanthus Acca Acer Aceras Aceriphyllum Achillea Achimenes Thankyou if you can help. Pat check advanced filter unique values in help "Pat" wrote: > Is it possible to filter based on the following example: > > colA > > Acalypha > Acanthocalycium > Acantholimon > Acanthopanax >...

Within my form, I have a auto number function in it to auto populate a number. The number that was populating and the record were in sync, but now they are not. The table were the data is store is formatted correctly, the numbers are not out of sequences. How do you fix this or what could be the problem?...

I am trying to create a mailing list sorted by zip codes. The file I am working with has zip codes in the "plus 4" format and I need to limit the zip codes in a column to just the basic 5 zip code numbers. For example, I need to limit zip code 123456789 to just number 12345. Any help would be appreciated. Hi Jack, Try: Data | Validation | Settings tab | Allow: Text length | Equal to: 5 Error Alert tab | Add a suitable error message --- Regards, Norman "jack du lin" <jack@lionpool.com> wrote in message news:ad2b829bcc632b5d672b44bf44cbfc55$1@www.dotxls.co...

I have a range with formulas on sheet2 called "database". I want to get this range and transfer it's contents to sheet1 called "work", but I want the formulas to update themselves to this new location, and not show the same exact formula that was found in the original range. I had tried : sheets("work").range("a1:b10").formula = sheets("database").range("d1:e10").formula with the following results Cells d1 to d10 on sheet database contain X values, and cells e1 to e10 contain the formula =d1*d1, =d2*d2, and so forth. Wha...

Addendum to Previous post: I realized I omitted one crucial element from my previous post: I want the fractional data label to be a non-simplified representation of the source data (e.g. - if the source data is 10/6, I want the data label to be 10/6). Previous post: I'm trying to create bar graphs containing fractional data labels. The catch is, I want each bar in the series to have a different denominator value. Excel allows you to preset the denominator (e.g. - ?/4), but that denominator affects all members of that series. The question is, is this do-able and if so what special...

I'm creating a report of only the changes that have been made in the database since the last publication of the report. I don't want to list all the information about a contact, only what has changed. I've created hidden text boxes corresponding with the contact information. (i.e. - txtAddress has a corresponding txtAddressChange.) I have in the txtAddress (and all the other text boxes) in the On Change, code to copy the data to the new text box. (Me.txtAddressChange = txtAddress). However, this copies the old information and I'm wanting it to copy the new information. I...

I need a column to display 18 numbers and currently it is rounding the last three with zeros. I am using Microsoft 2003 How many digits in the numbers More than 15? Then you will need to type then as text and will not be able to do math on them Either a) when typing, precede the entry with a single quote (will not show in cell or printout b) before typing, format cells as Text best wishes -- Bernard Liengme Microsoft Excel MVP people.stfx.ca/bliengme email address: remove uppercase characters "whamm24" <whamm24@discussions.microsoft.com> wrote in message ...

I would like to change the color of a cell automatically using a condition formula.....is this possible? Yes, go to Format | Conditional Formatting. -- Regards Juan Pablo Gonz�lez "Carlos" <Carlos@discussions.microsoft.com> wrote in message news:1330539A-8B3B-44DE-AF70-1E686D172379@microsoft.com... > I would like to change the color of a cell automatically using a condition > formula.....is this possible? Hi see 'Format - Conditional Format' -- Regards Frank Kabel Frankfurt, Germany Carlos wrote: > I would like to change the color of a cell automat...

I have a formula in cell G2 that reads: =round(F2,0). I'm using this to round the value in cell F2 and remove decimal places. The entire "F" column has values I want to round, and I am unable to "fill" the formula so that I can get the rounded values of each record in the range. What should I be looking for? So what *does* happen when you copy the formula in G2 to other cells in the column? Check to make sure Calculation is set to Automatic (Tools/Options/Calculation). In article <1933354D-C4AE-4051-87BD-29AF728A57DA@microsoft.com>, LindaO <Linda...

I want to drag the formula that exists in d3 (i.e. =d2/d130) to a column of rows. The problem is that by dragging the formula, it increments the d130 to d131, d132, etc. How can I make the formula stop incrementing the second variable? $d$130 -- Don Guillett SalesAid Software dguillett1@austin.rr.com "sdmccabe" <sdmccabe@discussions.microsoft.com> wrote in message news:77FE3F27-B67C-4057-855E-C7F2B84FC519@microsoft.com... >I want to drag the formula that exists in d3 (i.e. =d2/d130) to a column of > rows. The problem is that by dragging the formula, it inc...

Hi, Here is my scenario, I am attempting to export data from another program into excel (financial data) that changes daily making my cell location for totals change is there a formula that can combat that??? to eliminate data entry time!!! :P Hi Place your totals into table header, and calculate them using dynamic ranges. An example: Your data are on sheet MyData in range A5:E5 and down. In range A4:E4 are column headers. Rows 1:3 are empty. Column A contains some identifier (Name or Date etc.) and is never is empty, whenever there are any data in row. Columns C:E contain numeric data...

See attachment: How can i write the same IF formula if "x" is marked for 1.1, 1.4, an 1.9 also in the same cell?? The "x" will only be marked for one at th same time....but i never know witch of them. Therefore I want on formula that covers them all. Phoeni Attachment filename: if formula.xls Download attachment: http://www.excelforum.com/attachment.php?postid=46184 -- Message posted from http://www.ExcelForum.com Phoenix, =H4*I4*IF(ISNA(MATCH("x",J4:M4)),1,INDEX(J3:M3,MATCH("x",J4:M4))) (I'm not sure whether the , ...

I am currently having two copies of every email that is addressed to me appear in my inbox. My ISP indicates that at their end when testing only one copy appears. Anyone know what setting I have that might be incorrect or any other suggestions. I can't seem to find a good answer. Thanks Paul If you're using Outlook 2002, see if http://support.microsoft.com/default.aspx?scid=kb;en-us;Q284404 applies. -- Jeff Stephenson Outlook Development This posting is provided "AS IS" with no warranties, and confers no rights "Paul Bernard" <cpbernardjr@awesomenet....

I have the following problem: I need a formula wherein a summation happens of different IF's. When I make the formula in the edit box (function arguments ('Fx' in formula bar)) I see the result of my formula. But in the cell the result is #VALUE! . I tried something similar but easier and the same happens, I give the example formula: =SUM((A1:D1)*(A2:D2)) What can I do about it or is it just happening to me? Hope somebody van help me, thanks a lot! Jan Read help on SUMPRODUCT -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Jan" <jdridd...