Date Formula Microsoft Excel 97 2000 2002 free tutorial format function workbook worksheet sum report printing pivot table forms templates template Date Data CountIf Count VBA Macro excel formula subtotal filtering chart graph

Microsoft Excel Sum and Summing Ranges / Data

ArticleBody
1 Said Shades in alt.destroy.microsoft on Sat, 17 Mar 2001 19:38:37 -0800; Adam Smith, one of the founders of economic stated: "It is not from the benevolence of the butcher, the brewer, or the baker that we expect our dinner, but from their regard to their own interests" in his great book "The Wealth of Nations". Maybe I am wrong but I get this impression that Linux people think making money is somehow "bad". That wealth is bad. Quite frankly I don't think Communism works and that good old Capitalism does (for better or worse). No, Linux people think that exploiting the work of others to make money is bad, because it is, and that wealth which comes from profiteering is bad, because it is. Capitalism is all well and good, but you need to pay much more attention to Mr. Smith's words if you want to use them. You can't trust the benevolence of the butcher, but in the real world, you don't have to, because he has competition. If software developers didn't get themselves so hung up trying to prevent others from competing by purposefully avoiding interoperability, then the Linux people, who consider the open source movement the champion of capitalism, and the profiteers the equivalent of communism, wouldn't have to piss on their parade. Monopolies are certainly bad for the economy, there is no arguing there. But is it bad to make a profit from software? No. Did I say anything else? The problem is, unless all the code is open source, you can't know whether your vendor is making a profit from software, or profiteering on software. Well, you can't be precisely sure where the line is, I should say; you can be sure they're profiteering. What doesn't matter? The answer to your question, why nobody "wipes MS's arse up", is pretty blatantly obvious to anyone with more than half a brain. Have you been asleep for the past five years, or what? My question was regarding a longer period of time than 5 years: Over the last 25 years many companies have had the opportunity to wipe out MS. Never once in the last 25 years has Microsoft ever done anything competitive. They started out anti-competitive, and they'll be destroyed when they can no longer continue to act anti-competitive. Lotus, Novell, IBM, etc... and no one has done it. Not a one has tried. There is no competitive reason to "wipe out" anyone. This speaks of your deep and fundamental disregard for the actual mechanism of capitalism which you supposedly are advocating. You can call it anti-competitive practices but MS was not always a monopoly (assuming it is). They have always monopolized. Since way back with the BASIC thing, which was, I believe, their first product. So if my question still makes you believe I have "half a brain" then please explain it to the half I do have. I hope I have done so. It all comes down to a rather anti-capitalist notion that there is no such thing as an honest profit. Give up this idea, and you can see how honest profit would be more than sufficient to capitalize software development, and profiteering is neither necessary nor acceptable. -- T. Max Devlin *** The best way to convince another is to state your case moderately and accurately. - Benjamin Franklin ***
6 Said Mike Margerum in alt.destroy.microsoft on Wed, 21 Mar 2001 22:07:51 BTW; my sig doesn't say I "stick to the point", nor that I argue moderately. Merely that this is the best way to convince another of your position. I doubt his first approach would have been calling someone a moron. I think that would depend on what the moron first said. He was wise, not beatific. Still, had you argued that MS products were "competitive" more accurately and moderately, you might have been more convincing. If you hadn't started the thread out by calling me a moron, I would have had some very convincing arguments. I've been involved in the technology sector for a long time. Oh, c'mon now. Do you honestly believe a thread "starts out" as soon as you post to it? If you wouldn't have responded to *my* comments with a moronic assumption, I wouldn't have pointed out that the assumption makes you a moron. You'll notice the sig is there for YOU to read; I already know what it says, and follow its advice as far as I'm able. Not too well. I think you ought to pick a new sig Fuck off. <-- accurate and moderate response how's this: I'll beat you over the head until you agree with me. How about "people with thin skins who get defensive when spanked for being morons should not post to Usenet." -- T. Max Devlin *** The best way to convince another is to state your case moderately and accurately. - Benjamin Franklin ***
8 As an aside, I wonder how your bosses down in Maryland feel about the likes of you representing them on the internet with tripe like this. If you are going to publicly offend people you ought to be a little more anonymous about it. On Thu, 22 Mar 2001 01:20:47 GMT, T. Max Devlin <tmax@commercelinks.net wrote: Said Mike Margerum in alt.destroy.microsoft on Wed, 21 Mar 2001 22:07:51 BTW; my sig doesn't say I "stick to the point", nor that I argue moderately. Merely that this is the best way to convince another of your position. I doubt his first approach would have been calling someone a moron. I think that would depend on what the moron first said. He was wise, not beatific. Still, had you argued that MS products were "competitive" more accurately and moderately, you might have been more convincing. If you hadn't started the thread out by calling me a moron, I would have had some very convincing arguments. I've been involved in the technology sector for a long time. Oh, c'mon now. Do you honestly believe a thread "starts out" as soon as you post to it? If you wouldn't have responded to *my* comments with a moronic assumption, I wouldn't have pointed out that the assumption makes you a moron. You'll notice the sig is there for YOU to read; I already know what it says, and follow its advice as far as I'm able. Not too well. I think you ought to pick a new sig Fuck off. <-- accurate and moderate response how's this: I'll beat you over the head until you agree with me. How about "people with thin skins who get defensive when spanked for being morons should not post to Usenet." -- T. Max Devlin *** The best way to convince another is to state your case moderately and accurately. - Benjamin Franklin ***
12 Put this code in the workbook where the lists will be created. It will add Sheets as it needs them. Select multiple files in the file open dialog It will prompt for the number of addresses per sheet. Sub GetData() Dim flist As Variant Dim i As Long, j As Long Dim rw As Long, shCnt As Long Dim FileNo As Long Dim sh As Worksheet Dim sName As String Dim sLine As String Dim NumAddresses As Variant Dim MailList() as String ChDrive "C" ChDir "C:\Data" flist = Application.GetOpenFilename( _ FileFilter:="Text Files (*.txt),*.txt", _ MultiSelect:=True) If TypeName(flist) = "Boolean" Then Exit Sub Do NumAddresses = InputBox("Enter the number of Addresses" _ & " per sheet") Loop Until IsNumeric(NumAddresses) And Len(Trim(NumAddresses)) 0 ReDim MailList(1 To NumAddresses, 1 To 1) Set sh = ThisWorkbook.Worksheets.Add( _ After:=ThisWorkbook.Worksheets( _ ThisWorkbook.Worksheets.Count)) sName = "Data" & 1 On Error Resume Next Application.DisplayAlerts = False Worksheets(sName).Delete Application.DisplayAlerts = True On Error GoTo 0 sh.Name = sName shCnt = 1 rw = 1 For i = LBound(flist) To UBound(flist) FileNo = FreeFile() Open flist(i) For Input As #FileNo j = 0 Do While Not EOF(FileNo) Line Input #FileNo, sLine j = j + 1 MailList(rw, 1) = Trim(sLine) rw = rw + 1 If rw NumAddresses Then sh.Cells(1, 1).Resize(NumAddresses, 1).Value _ = MailList ReDim MailList(1 To NumAddresses, 1 To 1) rw = 1 Set sh = ThisWorkbook.Worksheets.Add( _ After:=ThisWorkbook.Worksheets( _ ThisWorkbook.Worksheets.Count)) sName = "Data" & shCnt + 1 On Error Resume Next Application.DisplayAlerts = False Worksheets(sName).Delete Application.DisplayAlerts = True On Error GoTo 0 sh.Name = sName shCnt = shCnt + 1 End If Loop Debug.Print flist(i), j Next sh.Cells(1, 1).Resize(NumAddresses, 1).Value _ = MailList ReDim MailList(1 To NumAddresses, 1 To 1) End Sub Richard <richard@clarknet.com wrote in message news:d65101c214a2$d20278b0$37ef2ecf@TKMSFTNGXA13... Here's the deal: I have successfully created an Excel 2000 macro that imports a large amount of text into a worksheet. The file being imported is so large that I have to import the remainder into a second sheet. The content is email addresses only so only one column is used. I have many such files and need to import a lot of them. I would like help or the code to accomplish a simple task: 1. Create an automated means for importing the large text file specifying increments of 2500, 5000, 10000, and 20000 at a time with a new sheet being created based on the chosen number to extract. I want it to run until the end of the first file and automatically go to the next file. I'm not a VB programmer so I'm looking for either the scripting already done (a few modifications are ok) or very detailed and specific instructions on how to do it. Thanks.
18 I'm useing XP Excel 02, A1 has today's date =TODAY A2 to A10 has Text dates, june 16 02 etc B2 to B10 has data 12 hrs only if booking is required so some cell maybe blank B12 has the actual hrs allowed, say 100 Problem...How do I make the Date in A1 check the text date from A2 to A10 and check if B2 to B10 has data and return the sum in B13 of the hrs used after the the date has gone past. Thanks Ross
21 Many apologies for the multiple postings, was under a "tad" pressure to get results. Normally my manners are better than that. Thanks for the help - it works! -----Original Message----- =SUMPRODUCT((APR02!D2:D407=DATEVALUE("04/1/2002"))*(APR02! AC2:AC407="I")) or =SUMPRODUCT((APR02!D2:D407="04/1/2002"*1)*(APR02! AC2:AC407="I")) In article <3D0A4BC0.C4C9B8F5@schliferclinic.com, BSCC <general@schliferclinic.com wrote: I'm trying (and trying!) to get data in one column (D) that equals 4/1/2002 and data in another column (AC) that is equal to "I". Have tried this but no good: =SUMPRODUCT(APR02!D2:D407,"04/1/2002")*(APR02! AC2:AC407,"I") Any ideas? thanks .
40 Thanks Tom, I will try that first. However I think in the near future I will build an Acess database with user friendly inputs and easy form printouts. Jeff -----Original Message----- Usually there would be a database of daily values with date and amount. Then on another sheet you can have formulas that sum up the monthly and quarterly amounts. Since you say you would refer back to the particular day, it sounds like you have a separate workbook with each days data. To do what you want would require intentional circular references, but I am not sure how you would expect the formula to know what the current quarter and month are knowing when to restart the accumulated total. Do use intentional circular references, you would have to go to the calculate tab in tools=options and check mark iteration. Change max iterations to 1. Then you can have a formula refer to itself In A1 =if(A2<0,A1+A2,0) as an example. Of couse every time the sheet is calculated, the value in A1 would be incremented by the value in A2. Jeff Mouras <mouras@bellsouth.net wrote in message news:d6fe01c21523$f2c54b50$36ef2ecf@tkmsftngxa12... Tom, I want to be able to open up my excell file, change the numbers for the day (daily cell), then print the workbook displaying: Daily, monthly and quarterly numbers. I hadn't put much thought to saving the daily numbers because I could just refer back to the particular day. Another goal of mine was to make this as simple as possible so anyone could enter the data and get the correct print out. I am open for suggestions though... Thanks, Jeff -----Original Message----- What do your formulas look like now. Are you using intentional circular references. This seems like a good way to get the wrong answer to me. Why don't you not want to store your daily numbers and then use formulas to summarize them? Regards, Tom Ogilvy Jeff Mouras <mouras@bellsouth.net wrote in message news:eb1701c21468$c5f1aee0$b1e62ecf@tkmsftngxa04... Hello, my question is: Can I build a formula that I can input data into a single cell (Daily cell) and have that data totaled up and saved in a "Monthly" and Quarterly" cell? My trouble is every time I change the daily data the Monthly and Quarterly cells do not calculate from their previous total. Thanks in advance for any help with this. Jeff Mouras . .
58 One way (assumes a 4 digit year): =DATEVALUE(LEFT((MID(A1, FIND("/", A1) - 2, 255)), FIND("%", SUBSTITUTE(A1, "/", "%", 2)) - FIND("/", A1) + 2)) In article <#IrpunfFCHA.1260@tkmsftngp07, Dave B <david.bracknell@avmltd.com wrote: Anyone know a quick way to get the date out of a text string (in a single cell) such as "Prices last updated as of 6/17/2002. These are provided by xxx company"? A formula or vb solution would suffice. Thanks for the help !!!
66 This did not work for me - It looks like it should work but I get #Value as answer. "J.E. McGimpsey" <jemcgimpsey@mvps.org wrote in message news:170620020721318363%jemcgimpsey@mvps.org... One way (assumes a 4 digit year): =DATEVALUE(LEFT((MID(A1, FIND("/", A1) - 2, 255)), FIND("%", SUBSTITUTE(A1, "/", "%", 2)) - FIND("/", A1) + 2)) In article <#IrpunfFCHA.1260@tkmsftngp07, Dave B <david.bracknell@avmltd.com wrote: Anyone know a quick way to get the date out of a text string (in a single cell) such as "Prices last updated as of 6/17/2002. These are provided by xxx company"? A formula or vb solution would suffice. Thanks for the help !!!
80 Works for me with the test sentence you gave. If you'll always have a period or space after the year, use Peo's. Mine will accept other characters (like a comma), but is unecessarily complex if you won't have any other characters. In article <OnM2FagFCHA.2392@tkmsftngp04, Dave B <david.bracknell@avmltd.com wrote: This did not work for me - It looks like it should work but I get #Value as answer. "J.E. McGimpsey" <jemcgimpsey@mvps.org wrote in message news:170620020721318363%jemcgimpsey@mvps.org... One way (assumes a 4 digit year): =DATEVALUE(LEFT((MID(A1, FIND("/", A1) - 2, 255)), FIND("%", SUBSTITUTE(A1, "/", "%", 2)) - FIND("/", A1) + 2))
87 I had a typo when converting to my spreadsheet. This formula works well and will take into account the date format changing and changes to the text string. Thanx!!! "J.E. McGimpsey" <jemcgimpsey@mvps.org wrote in message news:170620020721318363%jemcgimpsey@mvps.org... One way (assumes a 4 digit year): =DATEVALUE(LEFT((MID(A1, FIND("/", A1) - 2, 255)), FIND("%", SUBSTITUTE(A1, "/", "%", 2)) - FIND("/", A1) + 2)) In article <#IrpunfFCHA.1260@tkmsftngp07, Dave B <david.bracknell@avmltd.com wrote: Anyone know a quick way to get the date out of a text string (in a single cell) such as "Prices last updated as of 6/17/2002. These are provided by xxx company"? A formula or vb solution would suffice. Thanks for the help !!!
157 first: hi all Second: if i go to menu Insert-Function and Choose the Function Sum at the bottom of the box appear (say me) a little help..... and too for each parameter, right? Third: i make a function named NtoW (for converter Number to Words) i write my own function or macro, i save these macro (Save As.... Complement) and i can use always when i open Excel, but my question is: How can i too write a little Description of my Function and too for each parameter? (the heading of my function is: Public Function NtoW(ByVal NumeroaConvertir As String, Optional MAY_min_Amb As String = "M", Optional Sexo As String = "M", Optional Unidades As String = "PESOS") As String) thanks
160 I have a set of about 500 worksheets from which I want to extract info in a summary sheet I am building. I have the names and locations of the workeets which are located all over our network and which are being constantly updated by people at other sites. I'm trying build a column of lookups that automatically changes whan I change the name of the file being looked into. I've created the first onebut because the name of the file is in brackets [*.wks], I can't get the value within the brackets to change as if I enter a cell refference to the file name string. Are there specical commands when dealing with bracketed data? What's a reasonable solution for this? Dan
169 Hi Manjo, Assumes the values are always the same as the example where the decimal and 0 are always in the same place. =MID(A1,2,4)&RIGHT(A1,2) If you want the value returned to be a number instead of text... =(MID(A2,2,4)&RIGHT(A2,2))*1 HTH Regards, Howard "Manoj" <mpillai@mecu.com wrote in message news:dffc01c216f7$27f38880$9ee62ecf@tkmsftngxa05... Is it possible to convert a code such as L1234.019 into 123419 I want to take the "L" and "0" out and combine the "1234" and the "19" Thanks for your help.
200 Hi, I want to get the maximum of a set of numbers if they match a criterion. Very similar to SUMIF, just instead of the SUM I want to get the maximum. Is there a way in Excel to do that.. ? Thanks a lot in advance.. debraj
206 One way (array enter: CTRL-SHIFT-ENTER or CMD-RETURN): =MAX(IF(A1:A100<50,A1:A100,"")) In article <e3ad01c21711$22f48bd0$37ef2ecf@TKMSFTNGXA13, Debraj <dbm5um@yahoo.com wrote: Hi, I want to get the maximum of a set of numbers if they match a criterion. Very similar to SUMIF, just instead of the SUM I want to get the maximum. Is there a way in Excel to do that.. ? Thanks a lot in advance.. debraj
211 This might work for you: Tools|Macros Type in the name of your function Hit the options button You have limited space to type in a short description. click ok (to go back one dialog) click Cancel (yep, cancel) With nice parameter names, it might be ok. Miguel Enriquez wrote: first: hi all Second: if i go to menu Insert-Function and Choose the Function Sum at the bottom of the box appear (say me) a little help..... and too for each parameter, right? Third: i make a function named NtoW (for converter Number to Words) i write my own function or macro, i save these macro (Save As.... Complement) and i can use always when i open Excel, but my question is: How can i too write a little Description of my Function and too for each parameter? (the heading of my function is: Public Function NtoW(ByVal NumeroaConvertir As String, Optional MAY_min_Amb As String = "M", Optional Sexo As String = "M", Optional Unidades As String = "PESOS") As String) thanks -- Dave Peterson ec35720@msn.com
225 Sometimes the number of records are displayed in the lower left hand corner of screen, and sometimes they are not. I know if I right click in that location, another drop down menu appears. One can select "count nums", "sum", etc. It doesn't seem to make any difference which is selected. I really use the number of records displayed by the autofilter selection but I can't get it to do it every time. Lately, it hasn't been working at all. It doesn't seem to necessarily be related to free RAM or file size or even the number of records included in the autofilter. If anyone knows how this works, please pass it on to me! Thanks! Grand Junction, CO
230 If you find it time consuming and difficult to spot the duplicates after sorting (e.g. xCompany versus xCompany, Inc.), try using Refinate for free to bring you to the duplicates quickly so you can review them (it is not necessary to sort first). It optionally leaves a comment tag on the cell so you can see all duplicates after you reach the end of the search. You can also make a print-out showing these comment tags. If you make changes to the duplicates, the change is logged in the comment tag. Delete any comment tag you do not need. --Brian Taylor Refinate, copyright 2001 www.adetaylor.com "lk" <lazark@precisemailing.com wrote in message news:e44301c21715$151d50b0$36ef2ecf@tkmsftngxa12... Create a column in for EACH list and call that column "sourcecode" assign a source code and record number: e.g. A00001 the A specifying that that record came from file "A" the next record would be A00002, and so on (use auto fill to do this quickly). Open a new worksheet - copy and past the records from all files into that sheet and sort by the field you are deduping, e.g. company name. You will then see the dupes next to each other, and based on the source code, you can manually delete the dupes. I would copy those dupes before deleting them onto another sheet just so you have a backup. Try it - allway copy your files and work with your copy files so if you screw it up you still have the untouched originals. -----Original Message----- when I have a "potential" customer list in excel, with companies name in column1, and street address in column 2, and city in column 3, and province in column 4, and postal code in column 5,and phone number in column 6, and fax number in column 7, and watts number in column 8, email, in 9, and web url in column 10. Now I would like to merge two different lists with the same layouts, but i am afraid that there will be some duplication (because some companies are listed under different categories in the yellow pages), and I want to know if there is a short cut to "pointing out", "eliminating" the doubles? .
232 This happens when your list has many formulas. There's an article in the MSKB that explains: XL2000: Excel AutoFilter Status Bar Message Shows "Filter Mode" Q213886) /default.aspx?scid=kb;EN-US;q213886 or XL: AutoFilter Status Bar Message Shows "Filter Mode" (Q189479) /default.aspx?scid=kb;en-us;Q189479 A code solution: XL: How to Count Rows Displayed After Data Has Been Filtered (Q148621) /default.aspx?scid=kb;en-us;Q148621 ***A workaround that I sometimes use: If you want to see a record count for the visible rows which contain data, you can use the Subtotal function in a formula in the same row as your headings. For example, if you want to count the visible entries in column C which contain numbers, you could use this formula: =SUBTOTAL(2,C:C) The 2 in the first argument tells Excel to use the COUNT function on the visible cells in the range. If you want to count rows that contain text, you could change the formula: =SUBTOTAL(3,D:D)-1 The 3 is for the COUNTA function, and the -1 removes one for the row which contains the column heading. NOTE: Blank cells will not be counted -- use a column with no blank cells. Celeste wrote: Sometimes the number of records are displayed in the lower left hand corner of screen, and sometimes they are not. I know if I right click in that location, another drop down menu appears. One can select "count nums", "sum", etc. It doesn't seem to make any difference which is selected. I really use the number of records displayed by the autofilter selection but I can't get it to do it every time. Lately, it hasn't been working at all. It doesn't seem to necessarily be related to free RAM or file size or even the number of records included in the autofilter. If anyone knows how this works, please pass it on to me! Thanks! Grand Junction, CO -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html
233 Thanks, I'll check these out. Typically, I do have lots of formulae. -----Original Message----- This happens when your list has many formulas. There's an article in the MSKB that explains: XL2000: Excel AutoFilter Status Bar Message Shows "Filter Mode" Q213886) /default.aspx?scid=kb;EN- US;q213886 or XL: AutoFilter Status Bar Message Shows "Filter Mode" (Q189479) /default.aspx?scid=kb;en- us;Q189479 A code solution: XL: How to Count Rows Displayed After Data Has Been Filtered (Q148621) /default.aspx?scid=kb;en- us;Q148621 ***A workaround that I sometimes use: If you want to see a record count for the visible rows which contain data, you can use the Subtotal function in a formula in the same row as your headings. For example, if you want to count the visible entries in column C which contain numbers, you could use this formula: =SUBTOTAL(2,C:C) The 2 in the first argument tells Excel to use the COUNT function on the visible cells in the range. If you want to count rows that contain text, you could change the formula: =SUBTOTAL(3,D:D)-1 The 3 is for the COUNTA function, and the -1 removes one for the row which contains the column heading. NOTE: Blank cells will not be counted -- use a column with no blank cells. Celeste wrote: Sometimes the number of records are displayed in the lower left hand corner of screen, and sometimes they are not. I know if I right click in that location, another drop down menu appears. One can select "count nums", "sum", etc. It doesn't seem to make any difference which is selected. I really use the number of records displayed by the autofilter selection but I can't get it to do it every time. Lately, it hasn't been working at all. It doesn't seem to necessarily be related to free RAM or file size or even the number of records included in the autofilter. If anyone knows how this works, please pass it on to me! Thanks! Grand Junction, CO -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html .
246 Hello, I'm trying to have a empty cell if the cells listed below are zero. mine seems to make sence to me but not excel =IF(B16:D160,SUM(E15,+B16,-C16,-D1)) Hope to get help!
252 Hi Lynn This will create an empty cell if any of the three cells <= 0 and only sum the cells if there is a positive value in each of the three. =IF(OR(B16<=0,C16<=0,D16<=0),"",SUM(E15,+B16,-C16,-D1)) Hope this is what you wanted Jon Lynn wrote: Hello, I'm trying to have a empty cell if the cells listed below are zero. mine seems to make sence to me but not excel =IF(B16:D160,SUM(E15,+B16,-C16,-D1)) Hope to get help!
257 In article <ddb301c21653$64edfc10$36ef2ecf@tkmsftngxa12, "Bay" <jebn60@msn.com wrote: This 1997 or greater would need to be an upgrade version - correct? The tech that recovered the files did say he couldn't guarantee me that if he retrieved and resaved, it might not work - 2 out of 2. I better go shopping. He needed to "save as" the file as a previous version (XL 5 for instance). Even in the current XP based version of xl we have, there is an option to do that. I suggest the tech hasn't finished the recovery job you asked them to do :) Go back and ask them to do it properly ... there is no reason to upgrade xl 5 if you only want to use xl 5 files ... always assuming you can still run xl 5 :) Bruce -----Original Message----- If I understand correctly, you have files that xl97 (or greater) based. But all you have on your new pc is xl95. Do you know someone with xl97 (or greater)? Maybe you could share a copy of your files with them. They could open each one and save it back down to the xl95 version. (Be careful though. If you used anything that was added in xl97, xl2000, it might not be "downgradeable".) I _think_ that xl97 is a valid starting point to upgrade to xl2002. Maybe you can find a (cheap) version on Ebay. Then you can load that version and then upgrade. Bay wrote: Thanks so much for posting. What would you do with the same problem. Can the tech reverse my data from his 2000 Excel back to the original V5. Is there a valid upgrade..... Excel 2000 / 2001? I need to be able to use existing files. Again, thanks for taking the time to help. Janet "Bay" -----Original Message----- Excel 5 ==== Excel 2002 is not a valid upgrade.. -- Regards, Peo Sjoblom "Bay" <jebn60@msn.com wrote in message news:dc8f01c21636 $2790eed0$a4e62ecf@tkmsftngxa06... I'm using Excel Version 5.0 on a new Dell Windows XP system. Original data files were recovered from an old PC, which has harddrive problems by a tech using Excel 2000 and now I can't retrieve/open them with V5.0. It was suggested that I upgrade, which I have tried to load Excel 2002 Upgrade and I received error 1608 which means, could not find any previously installed compliant products on the machine for installing this product. I let Windows automatically locate Excel V5 (in C:\Documents & Settings\Janet\Desktop - even in this location I could double click the icon on my desktop and it would open up and I could open a file. I even moved (dragged within Windows Explorer) the Excel V5 folder to the Program Files directory and still I was unsuccessful in loading Excel 2002 Upgrade - received the same error code of 1608. I also installed Lotus SmartSuite V4, (1995) from CD and Excel V5 (Disk 1-9, 3.5")and they were both placed in the same directory automatically. C:\Document & Settings\Janet\Desktop and neither program appears under the Control Panel, Add/Remove Programs. But what does appear is all software that came preloaded and 1 other 2002 that I installed from a CD that is XP compatible. Software preloaded was only Windows XP that includes MS Works, etc. and Dell programs included in the January 2002 special. I was going to uninstall V5 and start over, but it doesn't show up in the Control panel Add/Remove Prog. Any and all help is deeply appreciated, at 58 this is about to get to me. Thanks, Bay . -- Dave Peterson ec35720@msn.com . -------------------------------------------------------------------- Oook ! NOTE remove the not_ from the address to reply. NO SPAM !
262 Yes, but as you say "IF(B16:D160" is ambiguous. -- "J.E. McGimpsey" <jemcgimpsey@mvps.org wrote in message news:180620022334451034%jemcgimpsey@mvps.org... | In article <%cUP8.20$BN4.41511@vicpull1.telstra.net, | <DavidH@OzGrid.com wrote: | | Hi Lynn | | You can give the appearance of the cell being empty, but it's not possible | for it to be truly empty. | | | =IF(SUM(B1:D16)=0,"",=SUM(E15,B16,-C16,-D1)) | | Not sure why you have +B16 as it wont make any difference and using -C16 etc | will return a negative number as a positive. | | | If you allow C16 to be negative, doesn't that mean that sum(B16:D16) | could be zero when B16:D16 are non-zero? (i.e., =SUM({5, -15, 10}) ) | | The OP is ambiguous, but if the intent is to sum E15, B16, -C16 and -D1 | if and only if B16:D16 are all non-zero, then this would be more | appropriate: | | =IF(B16*C16*D16, E15+B16-C16-D1, "") | | If the intent is to do the sum if and only if B16:D16 are greater than | zero, then perhaps this: | | | =IF((B160)*(C160)*(D160), E15+B16-C16-D1,"")
276 Chris, try =SUM('Patient 1:P10'!D38)/SUMPRODUCT(COUNTIF(INDIRECT("'"&{"Patient 1","P2","P3","P4","P5","P6","P7","P8","P9","P10"}&"'!D38"),"<0")) Btw, why are you not using the same naming system for your sheets? if the first sheet was named P1, then you could use this =SUM('Patient 1:P10'!D38)/SUMPRODUCT(COUNTIF(INDIRECT("'P"&ROW(INDIRECT("1:10"))&"'!D38"),"<0")) also note that this will return a #DIV/0 error if all cells are empty, you can prevent that by using wrap it in an IF function like =IF(SUMPRODUCT(COUNTIF(INDIRECT("'"&{"Patient 1","P2","P3","P4","P5","P6","P7","P8","P9","P10"}&"'!D38"),"<0"))=0,0,SUM('Patient 1:P10'!D38)/SUMPRODUCT(COUNTIF(INDIRECT("'"&{"Patient 1","P2","P3","P4","P5","P6","P7","P8","P9","P10"}&"'!D38"),"<0"))) if there are no negative numbers you could wrap with =if(sum('Patient 1:P10'!D38)=0,0,etc.. instead.. Also answered through email with sample attached -- Regards, Peo Sjoblom "Chris Bullock" <JohnCBullock@hotmail.com wrote in message news:e94701c217b5$6b9c73d0$36ef2ecf@tkmsftngxa12... Peo Sjoblom Thank You for your help with this, I did enter the formula you gave, although I did not understand what you meant by; entered with ctrl+shift&enter. I changed the cells referenced and this is the formula as I tried to use it =AVERAGE(IF('Patient 1:P10'! D38<0,'Patient 1:P10'!D38)) I am getting the #REF! error message, cell reference not valid. I do not understand why, Worksheet 1 thru 10 column D row 38 are all valid. If you are still willing to help with this I could definitely use the expert advice -----Original Message----- Try =AVERAGE(IF(A1:A100<0,A1:A100)) entered with ctrl+shift&enter -- Regards, Peo Sjoblom "Rob Fenn" <rob-fenn@maurice-phillips.co.uk wrote in message news:eBZb0h6FCHA.1360@tkmsftngp05... Chris Try the following. SUM(A1:A100)/COUNTIF(A1:A100,"0") but this will only work if there are no values less than 0. I am sure someone can think of an easier way but presumably this would work SUM(A1:A100)/(COUNT(A1:A100)-COUNTIF(A1:A100,"0")) A1:A100 is your range of data. HTH Rob "Chris Bullock" <JohnCBullock@hotmail.com wrote in message news:1027d01c217a7$48d06770$19ef2ecf@tkmsftngxa01... I am using Average functions accross worksheets and I need it to not use the cells that have a zero value in the average calculations. Is there a formula that will take care of this? The workbook is huge and deleting each cell with a zero value by hand will be very difficult. I have tried some If statements but have not been able to make any work because of limited knowledge. .
329 By quick fill, I assume you mean dragging the Fill Handle at the bottom right of the current selection. What doesn't work? What happens when you point to the Fill Handle? (the pointer should change to a black plus sign) What happens if you drag the Fill Handle? You can create custom lists, and then edit them: 1. Select the cells which contain your list 2. Choose ToolsOptions, go to the Custom Lists tab 3. Click the Import button. 4. Click OK To edit the list: 1. Choose ToolsOptions, go to the Custom Lists tab 2. Select your custom list on the left 3. Edit the entries list on the right 4. Click OK Lee wrote: My quick fill does not seem to work on some of the workbooks. Is there a way to correct this? Also, how can I edit a quick fill list? -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html
382 I have updated EveningStar's Summary of Microsoft Security Bulletins for MS02-031 which Microsoft published yesterday. The summary is available at /bwd/securitybulletins.asp Bulletin MS02-031 applies to Excel and Word 2000 and 2002 (XP). A high-contrast version of the summary is available at /bwd/securitybulletins-bw.asp This summary is intended to provide easy access to Microsoft-published security hotfixes and related documents for their operating systems and applications. Comments, suggestions for improvements, and criticisms are always welcomed by this author. -- David Dickinson [MVP} EveningStar Information Services eis @ nospam softhome . net (Followup set to microsoft.public.security)
383 You could just leave the formats alone and sum, but you may find that your sum's give you a somewhat bizarre result: A1: 04:08:53 (4 days, 8 hours, 53 minutes) A2: 03:16:07 (3 days, 16 hours, 7 minutes) A3: =A1 + A2 === 07:25:00 (7 days, 25 hours) If you want to convert the numbers to regular XL Date/Time format (days as integers and times as fractional days, here's one way: Since XL will interpret 4:8:53 as 4 hours, 8 minutes, 53 seconds, you need to do a bit of manipulation: A1: 04:08:53 B1: =A1*60 - 1.5*INT(A1*24) === 4.370138889 A2: 03:16:07 B2: =A2*60 - 1.5*INT(A2*24) === 3.671527778 B3: =SUM(B1:B2) === 8.041666667 or 8 days 1 hour. In article <10aac01c218a2$776c9ad0$9be62ecf@tkmsftngxa03, M. E. <mcook@magellanhealth.com wrote: I am trying to be able to SUM and AVERAGE time for a month. The time the employee is available may be so many DAYS (24hours = 1 day) so many HOURS (60 minutes in an hour) and so many MINUTES. SO lets say someone worked 4 Days 8 Hours and 53 Minutes In the cell we would format this for TIME and enter it as 4:8:53 I can't think of a way to write the formula that allows us to truncate this number at the colons and multiply and or divide by the appropriate factor (24 for the first colon and 60 for the second colon.) Microsoft Excel 97 SR-2 ANY HELP? Thanks!
427 ADO assumes that the Excel data is in the format of a database table. That is, the first row in the referenced range contains the field names that you can use to reference each column in the range. If you want to get ADO to return the information in the first row, use something like the following, where rs is the object variable referring to your recordset: MsgBox rs.Fields(0).Name -- John Green - Excel MVP Sydney Australia "Grisha Golberg" <junta@komkon.org wrote in message news:uh5gn9rnaesm49@corp.supernews.com... Hi all, I have a very large (~25Mb) Excel spreadsheet that contains a bunch of data and formulas (duh :). The use of it is that a user needs to enter a limited number of parameters, and get a report based on the data and formulas in the sheet. This is a VB app, and talking to Excel via automation is a pain - it takes quite a while to load the spreadsheet. I'd prefer not to touch the spreadsheet itself, so I thought I'd be using ADO through OLE DB and xls driver to do the job. But I can't find the documentation on how to, say, get a value of a single cell? I realize that technically this approach works with sheets or ranges, but I can do something like select * from [sheet1$d1:d2] and get the value from cell d2. But I am puzzled as to how I can get at d1, for example?
455 Hello, I have a number of data files generated by a Vbasic program. The files are of identical format though with a varying number of records. I need to open all these data files into a single Excel worksheet and do some simple sums. I'm sure I'm missing something simple, but I can't see how to merge several excel files into a single worksheet, other than using copy / paste which seems a bit clumsy. With thanks KK
464 G14: =SUMPRODUCT((ISBLANK(I1:I8))*(G1:G8)) G15: =G13-G14 HTH Jeff -----Original Message----- For column I - cells 1 through 8, I want to sum corresponding cells in G - cells 1 through 8 for cells in column I that are blank into cell G14, and for those which are not blank into cell G15. G13 cell holds the total for column G. I want this to update automatically when text is added in column I
472 =Sumif($I$1:$I$8,"<",$G$1:$G$8) would get the sum of the non-blank cells. =SUMIF($I$1:$I$8,"=",$G$1:$G$8) Would get the sum of the blank cells. "Dot Appleman" <appleman@achamp.gsfc.nasa.gov wrote in message news:1119601c2191c$4d5cc7e0$39ef2ecf@TKMSFTNGXA08... For column I - cells 1 through 8, I want to sum corresponding cells in G - cells 1 through 8 for cells in column I that are blank into cell G14, and for those which are not blank into cell G15. G13 cell holds the total for column G. I want this to update automatically when text is added in column I
482 On sheet 4 I have a checkbook and on sheet 5 I have a report , which is printed each week. So I have 52 weeks of checkbooks and 52 reports. the checkbook repeats every 47 rows and the report repeats every 64 rows. I am using the indirect on some cells which works fine to return 1 value, from 1 cell on sheet 4, however I need to do a sum array also for some of the values. I am using =indirect("sheet4!C&((ROW(H49)+57)64)*47-38) This gets me to the cell where I want to start the Sum {=SUM(SHEET4!C7:C45=SHEET5!G22)*(SHEET4!G7:G45))} I have tried everything and cannot get them to nest and work properly The reason for nesting them is so I can copy and paste each of the 52 weeks, rather than enter the formula's in each cell for every week , I would probadly make a lot of mistakes. Thanks for any help Roger
488 One way: Assume A1 is top left of your table. 1) Insert a column between "ABC" and "100". 2) To right of "ABC" (in B2, to get date): =$C$1 and copy that down the list. 3) Insert two columns between "100" and "90" 4) To right of "100" (in D2, to get ID): =A2 5) To right of that (in E2, to get date): =$F$1 6) Copy those equations down the list. 7) Use Copy & PasteSpecialValues to convert equations to text, pasting the set of columns associated with new dates to bottom of list. Repeat this for as many month_columns as you have and delete the unneeded extra columns. Sort the records in the first columns. --Brian Taylor Refinate (C)2001, 20 free trials. Click to: Rearrange Cells or Cell Content, Find Duplicates, Count Items, Import, much more. www.adetaylor.com "z" <diggitydown@diggitydown.com wrote in message news:113bf01c21929$eea06dc0$3bef2ecf@TKMSFTNGXA10... I would like take a set of horizontal data (like data presented in a pivot table) and make it vertical. For example, I want to take the following horizontal data.... ------------------------ | |1/02 |2/02 | ------------------------ |ABC | 100| 90| ------------------------ |DEF | 500| 600| ------------------------ and make it look like... ------------------------ |ABC |1/02 | 100| ------------------------ |ABC |2/02 | 90| ------------------------ |DEF |1/02 | 500| ------------------------ |DEF |2/02 | 600| ------------------------ Is there a function out there to do this...or, does anybody have a macro that does this. Thanks!
491 Hi Roger, Click on the cell with the formula. In the formula box, with the mouse, you can select parts of your formula, press [F9] and Excel will calculate just the portion of the formula selected. WARNING! use the Esc key to finish or the selection will be replaced by the value. You must select a "full function" to get a result. Be off by 1 "(" and it will error out. This allows you to debug parts of a formula. Why go through this? The indirect function is looking for a cell address in different cell. Your formula looks like is returning a value higher than the number of rows. I got 318810 (c318810 doesn't exists) John "Roger" <rlm@donet.com wrote in message news:f46c01c2192f$56ee4b90$2ae2c90a@hosting.microsoft.com... On sheet 4 I have a checkbook and on sheet 5 I have a report , which is printed each week. So I have 52 weeks of checkbooks and 52 reports. the checkbook repeats every 47 rows and the report repeats every 64 rows. I am using the indirect on some cells which works fine to return 1 value, from 1 cell on sheet 4, however I need to do a sum array also for some of the values. I am using =indirect("sheet4!C&((ROW(H49)+57)64)*47-38) This gets me to the cell where I want to start the Sum {=SUM(SHEET4!C7:C45=SHEET5!G22)*(SHEET4!G7:G45))} I have tried everything and cannot get them to nest and work properly The reason for nesting them is so I can copy and paste each of the 52 weeks, rather than enter the formula's in each cell for every week , I would probadly make a lot of mistakes. Thanks for any help Roger
493 The following works, but is a little cumbersome: =MAX(IF(ISERR(C2),0,C2),IF(ISERR(D2),0,D2),IF(ISERR(E2),0,E2)) (This assumes that all your valid data is either zero or positive.) Greg "drwinterton" <daniel_winterton@hk.ml.com wrote: I am doing some financial calculations. I have excel linked to bloomberg, a financial database. Bloomberg populates cells C2, D2, and E2 with the 3-month, 6-month, and 1-year volatility of a stock. In cell F, I have: =max(c2,d2,e2) However, if a certain stock hasn't been around for long enough, then bloomberg spits a "Value#" in the corresponding cell (for example, a stock has been around 9 months. It will have a 3- and 6-month volatility, but no 1-year volatility) Apparently, the MAX function does not work if one of the cells is "value#". Any suggestions? Thanks, Dan Winterton -- Greg phobos@quik.com /phobos
500 Hello, Thanks for your reply. I did look into Excel help, and found the same advice you gave me, but it was not helpful. I think you misunderstand my question I don't have copies of a shared workbook, I have several data files of identical format but with different data and with different numbers of records. I am not trying to merge changes to a shared workbook, I am trying to merge/insert/join/combine these several files into a single worksheet so that I can do some sums on all the records therein. My apologies for the confusion KK "Brian Roberts" <meorelse@hotmail.com wrote in message news:e3FLlFYGCHA.2212@tkmsftngp12... Hi, From Excel help: Merge workbooks 1.. Make sure the copies of the shared workbook that you want to merge are all stored in the same folder. To do this, you must give each copy a different name. 2.. Open the copy into which you want to merge changes. 3.. On the Tools menu, click Compare and Merge Workbooks. 4.. If prompted, save the workbook. 5.. In the Select Files to Merge into Current Workbook dialog box, click a copy that has changes to be merged, and then click OK. To merge more than one copy at the same time, hold down CTRL or SHIFT and click the file names, and then click OK. "KK" <trebor@yeleek.nospam.freeserve.co.uk wrote in message news:aev43e$r$1@newsg3.svr.pol.co.uk... Hello, I have a number of data files generated by a Vbasic program. The files are of identical format though with a varying number of records. I need to open all these data files into a single Excel worksheet and do some simple sums. I'm sure I'm missing something simple, but I can't see how to merge several excel files into a single worksheet, other than using copy / paste which seems a bit clumsy. With thanks KK
503 Taking EarlK's lead, here is a way to get the quotes into the same cell without using equations or extra cells. Enter in FormatCellsNumberCustom: \"#\" Apply that format to any selection. --Brian Taylor Refinate (C)2001, 20 free trials. Click to: Rearrange Cells or Cell Content, Find Duplicates, Count Items, Import, much more. www.adetaylor.com "EarlK" <earlk@livenet.net wrote in message news:uhpeQCbGCHA.2520@tkmsftngp13... Matt, If you'd like to see the quote marks around your original numbers, and not use the extra (E) column, you can do it with number formatting. This is going to be tacky, as we have to use two apostrophes (which look pretty much like a quote mark anyway if you have a proportionally spaced font). The Custom number format does not seem to allow including a quote mark as text. :) Select the column, Format-Cells-Number-Custom. Type: "''" # "''" That's a quote mark ("), two apostrophes ('), a quote mark, etc. With a proportionally spaced, font, it looks as it should. This approach, if you need it, will also allow you to use the numbers as numbers, not text, in case you need to sum them, etc, and have your quote marks too. You won't need the extra column. Regards from Virginia Beach, EarlK ------------------------------------------------------ ------- "MattS" <mattschillerberg@106group.com wrote in message news:11bf701c21898$800ac400$35ef2ecf@TKMSFTNGXA11... I'm sure this is a fairly easy question...but I don't use excel much and could use the help. I need to add quotation marks to a large column of numbers. ie. "1" "2" "3" etc. Is there a simple formula that I can use? If column D has the numbers can I make column 5 = """&D1&""" or something like that. When I do this I get "&D1&"....however, when I use the formula "'"&D1&"'" then I get '1' which is close...but I need the double quotes. Any help would be very appreciated. Thanks!
504 One more option. You could type your number, then hit alt-enter, then type your letter. Then edit your cell. Highlight the number portion and then Format|Cells| and make the font size smaller. If you like this idea (try it first), you can play with this to make your data entry easier. I assumed that you'll have x number of different type boxes. empty Number only (up to 2 digits) letter only (only 1) number and letter (Up to 2 digits and exactly one letter) So type in some test data n a few cells. 1A 13B X 7 A Then select that range and try the following macro: Option Explicit Sub testme() Dim myCell As Range Dim lenLeadingNumbers As Long For Each myCell In Selection.Cells lenLeadingNumbers = stripNumbers(myCell.Value) If Len(myCell.Value) - lenLeadingNumbers 2 Then 'something is wrong MsgBox "something is wrong with cell: " & myCell.Address Exit Sub End If Select Case Len(myCell.Value) - lenLeadingNumbers Case Is = 0 'either nothing in cell, or just numbers If Len(myCell.Value) = 0 Then 'do nothing Else myCell.Value = myCell.Value & Chr(10) End If Case Is = 1 'either it's got an alt-enter already or needs one If InStr(1, myCell.Value, Chr(10)) = 0 Then 'needs alt-enter myCell.Value = Left(myCell.Value, lenLeadingNumbers) _ & Chr(10) & Right(myCell.Value, 1) Else 'do nothing, it's just numbers then alt-enter End If Case Is = 2 If InStr(1, myCell.Value, Chr(10)) = 0 Then '2 non-numbers and one isn't alt-enter--error! MsgBox "something is wrong with cell: " & myCell.Address Exit Sub End If End Select 'now all entries are similar (numbers, altenter, letter) myCell.Characters(1, lenLeadingNumbers).Font.Size = 10 myCell.Characters(lenLeadingNumbers + 1, 1).Font.Size = 3 myCell.Characters(lenLeadingNumbers + 2, 1).Font.Size = 15 Next myCell End Sub Function stripNumbers(myString As String) As Long Dim iCtr As Long Dim myTempString As String myTempString = "" For iCtr = 1 To Len(myString) If Mid(myString, iCtr, 1) Like "[0-9]" Then myTempString = myTempString & Mid(myString, iCtr, 1) End If Next iCtr stripNumbers = Len(myTempString) End Function If you're new to macros, then you can read more about them at 's web site: /dmcritchie/excel/getstarted.htm (my first overkill of the weekend!) ======= You may even want to try a crossword puzzle maker. I went to google and search for crossword puzzles and I got lots of hits. I searched for "crossword puzzles shareware" w/o the quotes. You could try , too. Michael Redbourn wrote: Hi, I just started putting together a crossword and am using Excel 2000 Wondered if there's a way to put a number in the top left third of a cell (a square of the crossword) whilst writing another larger letter in the bottom two thirds of the same cell. What I want to do is to have some of the answers already filled in. Any help would be appreciated, thanks, Mike "The only reason for time is so that everything doesn't happen at once." Albert Einstein -- Dave Peterson ec35720@msn.com
506 You could have a macro open each text(?) file individually and then have your macro do the copy and paste. But if you recall your old DOS commands, there was a way to merge text files into one. Let's say they are named MyFile0001.txt through myFile0999.txt. And they're in the same folder. Just shell to DOS (Windows Start button|Programs|MS DOS Prompt (for win98)) May be "command prompt" in NT (IIRC). Then traverse to that folder. when you're there, just type: copy myfile*.txt allfiles.txt All the files that start with myfile and have an extension of .txt will be copied into allfiles.txt. Then Exit from the dos prompt and open that one in excel. ======== There are a few warnings about the DOS copy command. You couldn't do this without getting into trouble. copy *.txt allfiles.txt It would try to add allfiles.txt to allfiles.txt. You could even do something like: copy c:\mydir\*.* c:\allfiles.txt by putting the "to" file into a separate folder, we can combine all the files using a wildcard. ======== If you still want a macro to open up each text file and do the copy and paste, post back with some more details. Are the files all in one folder? Are they the only files in that folder? If not is there a unique identifier for the group? (It's kind of the same answers you need to do the DOS copy solution, too.) good luck, KK wrote: Hello, Thanks for your reply. I did look into Excel help, and found the same advice you gave me, but it was not helpful. I think you misunderstand my question I don't have copies of a shared workbook, I have several data files of identical format but with different data and with different numbers of records. I am not trying to merge changes to a shared workbook, I am trying to merge/insert/join/combine these several files into a single worksheet so that I can do some sums on all the records therein. My apologies for the confusion KK "Brian Roberts" <meorelse@hotmail.com wrote in message news:e3FLlFYGCHA.2212@tkmsftngp12... Hi, From Excel help: Merge workbooks 1.. Make sure the copies of the shared workbook that you want to merge are all stored in the same folder. To do this, you must give each copy a different name. 2.. Open the copy into which you want to merge changes. 3.. On the Tools menu, click Compare and Merge Workbooks. 4.. If prompted, save the workbook. 5.. In the Select Files to Merge into Current Workbook dialog box, click a copy that has changes to be merged, and then click OK. To merge more than one copy at the same time, hold down CTRL or SHIFT and click the file names, and then click OK. "KK" <trebor@yeleek.nospam.freeserve.co.uk wrote in message news:aev43e$r$1@newsg3.svr.pol.co.uk... Hello, I have a number of data files generated by a Vbasic program. The files are of identical format though with a varying number of records. I need to open all these data files into a single Excel worksheet and do some simple sums. I'm sure I'm missing something simple, but I can't see how to merge several excel files into a single worksheet, other than using copy / paste which seems a bit clumsy. With thanks KK -- Dave Peterson ec35720@msn.com
520 Ralph, Assume the first date cell is A2. I'll tackle the first part. Click on the first date cell, press & hold Shift, Control and the Down Arrow to highlight all of your dates. Then, click Format, Conditional Formatting. Click the down arrow on Condition 1 and change it to "Formula Is", then, enter WEEKDAY(A2,2)=6 OR WEEKDAY(A2,2)=7, click the Format button, select the Patterns tab, and click on the desired Color then click OK and then click OK. In the second case, part 1, do the same thing but use this formula: =MAXA($B$1:$B$32)=B2; and, for part 2, use the formula =A2280 HTH, --- Ron McKenzie a proficient Excel MOUS Ralph Noble wrote: In Column "A" I have named the column DATE and entered dates for the entire summer ... I need to be able to identify the weekends (Saturdays and Sundays) and make them bold and red on the fly ... is this possible with a formula or some form of macro??? Second, I have Column "B" that is the average daily water usage for the city named in Column "B1" ... I know that maximum daily water usage for this city is 280 (in millions of gallons) and I want to be able to ... 1. Scan the entire column and mark in red and bold the highest daily value that exists anywhere in Column "B" 2. Scan the entire column and mark in blue and bold any records that were set (i.e. any figure that exceeds the 280) Can someone help guide me through how I'd do this??? I'll then apply the same formulas to the other columns, which have different maximums. I'm open to suggestions on how to better format the data, too. Thanks, Ralph Noble ralph_noble@hotmail.com DATE ATLANTA BALTIMORE CHICAGO 06/01/02 206 186 254 06/02/02 223 187 245 06/03/02 231 185 264 etc
521 Ralph, Assume the first date cell is A2. I'll tackle the first part. Click on the first date cell, press & hold Shift, Control and the Down Arrow to highlight all of your dates. Then, click Format, Conditional Formatting. Click the down arrow on Condition 1 and change it to "Formula Is", then, enter WEEKDAY(A2,2)=6 OR WEEKDAY(A2,2)=7, click the Format button, select the Patterns tab, and click on the desired Color then click OK and then click OK. In the second case, part 1, do the same thing but use this formula: =MAXA($B$1:$B$32)=B2; and, for part 2, use the formula =A2280 HTH, --- Ron McKenzie a proficient Excel MOUS Ralph Noble wrote: In Column "A" I have named the column DATE and entered dates for the entire summer ... I need to be able to identify the weekends (Saturdays and Sundays) and make them bold and red on the fly ... is this possible with a formula or some form of macro??? Second, I have Column "B" that is the average daily water usage for the city named in Column "B1" ... I know that maximum daily water usage for this city is 280 (in millions of gallons) and I want to be able to ... 1. Scan the entire column and mark in red and bold the highest daily value that exists anywhere in Column "B" 2. Scan the entire column and mark in blue and bold any records that were set (i.e. any figure that exceeds the 280) Can someone help guide me through how I'd do this??? I'll then apply the same formulas to the other columns, which have different maximums. I'm open to suggestions on how to better format the data, too. Thanks, Ralph Noble ralph_noble@hotmail.com DATE ATLANTA BALTIMORE CHICAGO 06/01/02 206 186 254 06/02/02 223 187 245 06/03/02 231 185 264 etc
535 ade, Backslash. Sheesh. I'd tried that, and it didn't work, but I was using a forward slash. And it's in the help, too. Matt, go with ade's solution. :) Regards from Virginia Beach, EarlK ------------------------------------------------------------- "adetaylor" <ngbtaylor@adetaylor.com wrote in message news:MW_Q8.14435$Fv1.1146092@newsread2.prod.itd.earthlink.net... Taking EarlK's lead, here is a way to get the quotes into the same cell without using equations or extra cells. Enter in FormatCellsNumberCustom: \"#\" Apply that format to any selection. --Brian Taylor Refinate (C)2001, 20 free trials. Click to: Rearrange Cells or Cell Content, Find Duplicates, Count Items, Import, much more. www.adetaylor.com "EarlK" <earlk@livenet.net wrote in message news:uhpeQCbGCHA.2520@tkmsftngp13... Matt, If you'd like to see the quote marks around your original numbers, and not use the extra (E) column, you can do it with number formatting. This is going to be tacky, as we have to use two apostrophes (which look pretty much like a quote mark anyway if you have a proportionally spaced font). The Custom number format does not seem to allow including a quote mark as text. :) Select the column, Format-Cells-Number-Custom. Type: "''" # "''" That's a quote mark ("), two apostrophes ('), a quote mark, etc. With a proportionally spaced, font, it looks as it should. This approach, if you need it, will also allow you to use the numbers as numbers, not text, in case you need to sum them, etc, and have your quote marks too. You won't need the extra column. Regards from Virginia Beach, EarlK ------------------------------------------------------ ------- "MattS" <mattschillerberg@106group.com wrote in message news:11bf701c21898$800ac400$35ef2ecf@TKMSFTNGXA11... I'm sure this is a fairly easy question...but I don't use excel much and could use the help. I need to add quotation marks to a large column of numbers. ie. "1" "2" "3" etc. Is there a simple formula that I can use? If column D has the numbers can I make column 5 = """&D1&""" or something like that. When I do this I get "&D1&"....however, when I use the formula "'"&D1&"'" then I get '1' which is close...but I need the double quotes. Any help would be very appreciated. Thanks!
546 Hi Tom, I don't know if a filtered list would work for your search requirement. A filtered list is treated like those are the only entries in the sheet for many purposes such as copy and paste, mail merge. To create labels with Excel as the database we generally use MS Word for the printing of labels since Excel does not have builtin capability for printing labels. See my page Using Mail Merge with data from Excel /dmcritchie/excel/mailmerg.htm I have included some summary label information to help you pick an actual label or that you can use to print same on plain paper. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel Macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Tom" <auctionid4me@yahoo.co.uk wrote in message news:aa4R8.20086$ZR3.1838683@news6-win.server.ntlworld.com... Could somebody tell me how to do the following please: 1. Create printable labels from the ITEM NUMBER (ROW NUMBER) COLUMN A COLUMN B COLUMN C 2. Incorporate a search option onto a worksheet Thanks Piers
553 Phil - 1/ You could look up Data Type Summary in VBA help, which leads to a description of the various data types. 2/ You could set up a moving average in your worksheet. Say you have values in B2:B100, and you want a moving average of six consecutive numbers. In C7, enter this formula =average(B2:B7) And drag this down to C100. - Jon ------- Jon Peltier, Microsoft Excel MVP /jonpeltier/Excel/index.html _______ In article <1075a01c21858$c6327760$9be62ecf@tkmsftngxa03, Phil said... Hi, I'm using excel to plot some experimentally determined data, however the resulting chart has glitches due to rounding errors. To what accuracy does excel store numbers? And, is there a smoothing type of function for plotted data, whereby say, 6 consecutive numbers, for example, are averaged to give a smoother curve? Thanks Phil
569 =SUM(K1+K2+K3+K4) I have a formula that adds these numbers 54.5 55.75 49 57.5 The correct answer should be 216.75, but it keeps saying 216.8.... Is it rounding up?? Why won't it say 216.75 ? Thanks Chuck
570 Hi Chuck: You probably need to format the cell for the right number of decimals. Look at <Format <Cells on the menu. -- Regards, Vasant. **No direct emails please--keep discussion in newsgroup.** "Chuck" <clm701@hotmail.com wrote in message news:uha917odp29k92@corp.supernews.com... =SUM(K1+K2+K3+K4) I have a formula that adds these numbers 54.5 55.75 49 57.5 The correct answer should be 216.75, but it keeps saying 216.8.... Is it rounding up?? Why won't it say 216.75 ? Thanks Chuck
583 Thanks Jon, didn't know that. I assumed that as a chart can read a dynamic range, it would accept other named ranges that uses the INDIRECT. -- "Jon Peltier" <jonpeltier@yahoo.com wrote in message news:#U$mtjmGCHA.2452@tkmsftngp08... | Hi Dave - | | I just responded to an earlier post by John. The problem with charting from | a named range, is that while it seems as though it should work, and while the | same named range works in a worksheet, Excel's charting engine chokes on | named ranges that use INDIRECT in their RefersTo formula. A few other | functions also cause problems. | | I suggested a slightly different approach using OFFSET, which works fine with | charts. | | - Jon | ------- | Jon Peltier, Microsoft Excel MVP | /jonpeltier/Excel/index.html | _______ | | In article <3O9R8.3$TG5.14797@vicpull1.telstra.net, said... | | Hi John | | | Try using a Named range to refer indirectly to the cells storing the | addresses (InsertNameDefine), should work. | | | -- | **Possible solution has NOT been tested** | | | | | | FREE EXCEL NEWSLETTER | /News/2home.htm | Get the OzGrid Add-in | /Services/AddinExamples.htm | | "John Vera" <emperorj@hotmail.com wrote in message | news:1130001c21a30$e59a6b10$9be62ecf@tkmsftngxa03... | | | | | | Hi again. I'm trying to automatize my graphs so they read | | from a cell the range in which they are supposed to get | | points. Say, I have X data in column B and Y data in | | colmun D. My spreadsheet locates the appropriate range and | | displays it as text in four cells (upper and lower x | | range, upper and lower y range), say H4 says "$B$10" H5 | | says "$B$15", I4 has "$D$10" and I5 "$D$15" . I want the | | ranges in the graph to "read" the text strings in H4, H5 | | as the range in X and I4,I5 as the range for Y. Help very | | much appreciated. Typing indirect in the graph range is | | invalid (Excel 2000). | | | | Thanks, | | John | | |
585 Hi Andonny If you are converting numeric text constants to numbers then a Loop is a pretty slow method, as loops are generally slow. Try the code below: Sub ConvertTextToNumberFaster() Dim rCell As Range, Rng1 As Range Set Rng1 = Selection.SpecialCells(xlCellTypeConstants) If Rng1 Is Nothing Then MsgBox "No constants" Exit Sub End If Range("IV65536").Copy Rng1.PasteSpecial , xlPasteSpecialOperationAdd Application.CutCopyMode = False End Sub Just select the entire Column and run the code. It assumes that cell IV65536 is empty. -- "Andonny" <wje@multiline.com.au wrote in message news:#5hmxioGCHA.2324@tkmsftngp09... | Hi, | I am trying to convert this macro that it works on the column selected | instead of column A:A. | I just like to highlight the column in question and then run the macro. | | Your help is very much appreciated | Andonny | | Sub ConvertTextToNumber() | Dim Cell As Range, Rng1 As Range | Set Rng1 = Intersect(ActiveSheet.UsedRange, ActiveSheet.Range("A:A")) | For Each Cell In Rng1 | If Not IsEmpty(Cell) And Not Cell.HasFormula And IsNumeric(Cell) Then | Cell = CDbl(Cell) | End If | Next Cell | End Sub | |
590 Hi Dave, Thanks a million for your reply. This is great. Even though the code I mentioned is slow it also does something which I need quite often. It places the minus from data extracted from JDEdwards to the front. Like 22- to -22 which is a good thing in my case. So if I could get the previous code to work with only selected columns would help me a great deal. Then I could use both in various situations. Thanks Andonny <DavidH@OzGrid.com wrote in message news:W5fR8.16$TG5.28441@vicpull1.telstra.net... Hi Andonny If you are converting numeric text constants to numbers then a Loop is a pretty slow method, as loops are generally slow. Try the code below: Sub ConvertTextToNumberFaster() Dim rCell As Range, Rng1 As Range Set Rng1 = Selection.SpecialCells(xlCellTypeConstants) If Rng1 Is Nothing Then MsgBox "No constants" Exit Sub End If Range("IV65536").Copy Rng1.PasteSpecial , xlPasteSpecialOperationAdd Application.CutCopyMode = False End Sub Just select the entire Column and run the code. It assumes that cell IV65536 is empty. -- FREE EXCEL NEWSLETTER /News/2home.htm Get the OzGrid Add-in /Services/AddinExamples.htm If it's Excel, then it's us! "Andonny" <wje@multiline.com.au wrote in message news:#5hmxioGCHA.2324@tkmsftngp09... | Hi, | I am trying to convert this macro that it works on the column selected | instead of column A:A. | I just like to highlight the column in question and then run the macro. | | Your help is very much appreciated | Andonny | | Sub ConvertTextToNumber() | Dim Cell As Range, Rng1 As Range | Set Rng1 = Intersect(ActiveSheet.UsedRange, ActiveSheet.Range("A:A")) | For Each Cell In Rng1 | If Not IsEmpty(Cell) And Not Cell.HasFormula And IsNumeric(Cell) Then | Cell = CDbl(Cell) | End If | Next Cell | End Sub | |
592 Hi Andonny I have modified my code to reverse any imported numbers like: 55- I have also modified your original code to work on only the selection. Sub ConvertTextToNumber() Dim Cell As Range, Rng1 As Range Set Rng1 = Intersect(ActiveSheet.UsedRange, Selection) For Each Cell In Rng1 If Not IsEmpty(Cell) And Not Cell.HasFormula And IsNumeric(Cell) Then Cell = CDbl(Cell) End If Next Cell End Sub ========================================= Sub ConvertTextToNumberFaster() Dim rCell As Range, Rng1 As Range Set Rng1 = Selection.SpecialCells(xlCellTypeConstants) If Rng1 Is Nothing Then MsgBox "No constants" Exit Sub End If Rng1.EntireColumn.Insert Rng1.Offset(0, -1).FormulaR1C1 = _ "=IF(RIGHT(RC[1])=""-"",SUBSTITUTE(RC[1],""-"","""")" _ & "*-1,RC[1]*1)" Rng1.Offset(0, -1) = Rng1.Offset(0, -1).Value 'OPTIONAL Rng1.EntireColumn.Delete Application.CutCopyMode = False End Sub I think you will find the second more effcient as it works on the range as a whole and not one cell at a time. -- "Andonny" <wje@multiline.com.au wrote in message news:OquvRTpGCHA.2808@tkmsftngp10... | Hi Dave, | Thanks a million for your reply. This is great. Even though the code I | mentioned is slow it also does something which I need quite often. It places | the minus from data extracted from JDEdwards to the front. Like 22- to -22 | which is a good thing in my case. So if I could get the previous code to | work with only selected columns would help me a great deal. Then I could use | both in various situations. | | Thanks | Andonny | | | <DavidH@OzGrid.com wrote in message | news:W5fR8.16$TG5.28441@vicpull1.telstra.net... | Hi Andonny | | If you are converting numeric text constants to numbers then a Loop is a | pretty slow method, as loops are generally slow. Try the code below: | | Sub ConvertTextToNumberFaster() | Dim rCell As Range, Rng1 As Range | | Set Rng1 = Selection.SpecialCells(xlCellTypeConstants) | | If Rng1 Is Nothing Then | MsgBox "No constants" | Exit Sub | End If | | Range("IV65536").Copy | Rng1.PasteSpecial , xlPasteSpecialOperationAdd | Application.CutCopyMode = False | End Sub | | | Just select the entire Column and run the code. It assumes that cell | IV65536 | is empty. | | | -- | | | | | FREE EXCEL NEWSLETTER | /News/2home.htm | Get the OzGrid Add-in | /Services/AddinExamples.htm | If it's Excel, then it's us! | | "Andonny" <wje@multiline.com.au wrote in message | news:#5hmxioGCHA.2324@tkmsftngp09... | | Hi, | | I am trying to convert this macro that it works on the column selected | | instead of column A:A. | | I just like to highlight the column in question and then run the macro. | | | | Your help is very much appreciated | | Andonny | | | | Sub ConvertTextToNumber() | | Dim Cell As Range, Rng1 As Range | | Set Rng1 = Intersect(ActiveSheet.UsedRange, ActiveSheet.Range("A:A")) | | For Each Cell In Rng1 | | If Not IsEmpty(Cell) And Not Cell.HasFormula And IsNumeric(Cell) Then | | Cell = CDbl(Cell) | | End If | | Next Cell | | End Sub | | | | | | | |
612 Just to add to this thread: SpecialCells requires error handling in case the range does not contain constants, e.g.: On Error Resume Next Set Rng1 = Selection.SpecialCells(xlCellTypeConstants) On Error GoTo 0 ' Rest of code... "" <DavidH@OzGrid.com wrote in message news:W5fR8.16$TG5.28441@vicpull1.telstra.net... Hi Andonny If you are converting numeric text constants to numbers then a Loop is a pretty slow method, as loops are generally slow. Try the code below: Sub ConvertTextToNumberFaster() Dim rCell As Range, Rng1 As Range Set Rng1 = Selection.SpecialCells(xlCellTypeConstants) If Rng1 Is Nothing Then MsgBox "No constants" Exit Sub End If Range("IV65536").Copy Rng1.PasteSpecial , xlPasteSpecialOperationAdd Application.CutCopyMode = False End Sub Just select the entire Column and run the code. It assumes that cell IV65536 is empty. -- FREE EXCEL NEWSLETTER /News/2home.htm Get the OzGrid Add-in /Services/AddinExamples.htm If it's Excel, then it's us! "Andonny" <wje@multiline.com.au wrote in message news:#5hmxioGCHA.2324@tkmsftngp09... | Hi, | I am trying to convert this macro that it works on the column selected | instead of column A:A. | I just like to highlight the column in question and then run the macro. | | Your help is very much appreciated | Andonny | | Sub ConvertTextToNumber() | Dim Cell As Range, Rng1 As Range | Set Rng1 = Intersect(ActiveSheet.UsedRange, ActiveSheet.Range("A:A")) | For Each Cell In Rng1 | If Not IsEmpty(Cell) And Not Cell.HasFormula And IsNumeric(Cell) Then | Cell = CDbl(Cell) | End If | Next Cell | End Sub | |
617 John - INDIRECT doesn't work, but OFFSET will. If you can spare an extra cell, use this approach. I'll assume you are working in Sheet1, and that the X data is in column A. AN19 contains 37 (the column number of column AK) AN20 contains 25 (the first row to plot) AN21 contains 38 (the last row to plot) Define two named ranges (a named formula, if you will): Ctrl-F3 to open the define names dialog Name: theYdata1 Refers To: =OFFSET(Sheet1!$A$1,Sheet1!$AN$20-1,Sheet1!$AN$19-1,Sheet1!$AN$21-Sheet1!$AN$2 0+1,1) (all one line, watch for newsreader word wrap) Name: theXdata1 Refers To: =OFFSET(theYdata1,0,1-Sheet1!$AN$19) Use theXdata2, theYdata2 for subsequent series. Now select the series and edit the chart series definition formula in the formula bar. It looks like: =SERIES(,Sheet1!$A$25:$A$38,Sheet1!$AN$25:$AN$38,1) change it to =SERIES(,Sheet1!theXdata1,Sheet1!theYdata1,1) Now when your cells update the values in AN19:AN21, the chart also will update. - Jon _______ In article <fb6601c21ae1$6b41bfb0$2ae2c90a@hosting.microsoft.com, emperorj@hotmail.com says... Hello again. I f anyone can help me it's infinitely appreciated. I'm making a "template" excel file with 8 sheets that I can use to organize data. I have data in columns, for which I have cells that pick the appropriate higher and lower values. These cells display the cell adresses in which the upper and lower values are (in one case, AN20 has "$AK$25" and AN21 "$AK$38" no quotes). Now, I need to automatize this so it will create a graph with the range $AK$25 to $AK$38 (in this case) without having to pick the range myself or having to use the sheet name (since each sheet name will change for every file I do). Indirect referencing doesn't work in the chart, neither does the address command (which I could have used to create the cell adress in the range). Help!! Thanks, John
631 One of my sales managers wants to be able to use a cell as a "tally" space, of sorts. In other words, she wants to be able to hit the number "1" key to just add one to whatever number or sum already exists in the cell. Is this possible? Normally, of course, hitting the number "1" key in a cell containing a number of formula would simply overwrite the existing contents with simply "1." This tally method, apparently, she prefers over having to edit the formula, like =1+1+1+1 Thanks in advance, Vinod
634 Hi Vinod Possible indeed (macro work). But there are so many probable accidents involved this... Anyway; rightclick the sheet Tab and Choose "view code". Paste this in: Dim strFrm As String Private Sub Worksheet_Change(ByVal Target As Range) If Left(strFrm, 1) = "=" Then Application.EnableEvents = False Target.Formula = strFrm & "+" & Target.Value Application.EnableEvents = True End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) strFrm = Target.Formula End Sub Enable a cell for this horrid idea by entering =1 instead of 1. Any cell starting With = will get the addition of +whateveryoutypedin. As stated, no error checking for text, drag&drop, multiple cell selections, current formula logic, copy-paste, no Undo,... you fix. HTH. Best wishes Harald "V x" <death@death.com wrote in message news:evt4YP7GCHA.404@tkmsftngp13... One of my sales managers wants to be able to use a cell as a "tally" space, of sorts. In other words, she wants to be able to hit the number "1" key to just add one to whatever number or sum already exists in the cell. Is this possible? Normally, of course, hitting the number "1" key in a cell containing a number of formula would simply overwrite the existing contents with simply "1." This tally method, apparently, she prefers over having to edit the formula, like =1+1+1+1 Thanks in advance, Vinod
636 Show her how to use a Spin Button from the Forms Toolbar. HTH Gord Dibben Excel MVP - XL97 SR2 On Mon, 24 Jun 2002 15:18:42 -0400, "V x" <death@death.com wrote: One of my sales managers wants to be able to use a cell as a "tally" space, of sorts. In other words, she wants to be able to hit the number "1" key to just add one to whatever number or sum already exists in the cell. Is this possible? Normally, of course, hitting the number "1" key in a cell containing a number of formula would simply overwrite the existing contents with simply "1." This tally method, apparently, she prefers over having to edit the formula, like =1+1+1+1 Thanks in advance, Vinod
637 Jan's method works with foreground colors. Dave's works with background colors. Both are great, and I can make use of this myself. The major problem with both methods is that when you change a color, the sum or the count does not change. For example, if I change a green color to a red, neither the green totals nor the red totals changes. Can either of you offer a solution to this? Many thanks, Paul "" <DavidH@OzGrid.com wrote in message news:<jiDR8.19$926.7591@vicpull1.telstra.net... Hi Debbie I have a UDF that does this here: /VBA/Sum.htm It looks remarkably similar to Jans. -- FREE EXCEL NEWSLETTER /News/2home.htm Get the OzGrid Add-in /Services/AddinExamples.htm If it's Excel, then it's us! "Jan Karel Pieterse" <pieterse_home@compuserve.com wrote in message news:1186301c21b6d$0194cf30$9be62ecf@tkmsftngxa03... | Hi, | | I assumed the text has the color, not the interior. | Try this user defined function: | | Option Explicit | | Function CountColour(rRange As Range, iColor As Integer) | Dim rCell As Range | Dim lTotal As Long | Application.Volatile | For Each rCell In rRange | If rCell.Font.ColorIndex = iColor Then | lTotal = lTotal + 1 | End If | Next | CountColour = lTotal | End Function | | Now to count red cells in the range A1:A5, use this | function: | | =CountColour(A1:A5,3) | | How to enter a macro (=VBA code): | | 1. Open the Excel file you want to add the code to. | | 2. Choose Tools, Macro, Visual Basic Editor to open the | Visual Basic Editor (or press Alt+F11). This is the | design environment that stores the VBA code. If this | is | the first time you have opened the editor, you will | probably see three windows: the Project window, the | Properties window, and the Code window. | | 3. In the Project window, select the name of the workbook | you want to add the sub to (one project is listed for | each open file). | | 4. After selecting the project icon, choose Insert, | Module. | This inserts a VBA code module into the project and | places the insertion point in the Code window of that | module. | | 5. You can either type the snippet code exactly as | written, | or you can copy the code directly from this message | and | paste it into the Code window. | | Regards, | | Jan Karel Pieterse | Excel TA/MVP | | -----Original Message----- | I am wanting to count dates in a range, but by colour. | Ie, all red dates to give a total number, all blue dates | to give a total number. | | Can anyone help?? | | Debbie. | . |
641 I would suggest building a button or a drawing object and tying it to a short macro such as this: Sub inc() Dim x As Long x = Range("A1").Value x = x + 1 Range("A1").Value = x End Sub It is possible to do what she wants but it requires 2 cells (one can be hidden or you can change the text to white so it can't be seen). Use the following code: Private Sub Worksheet_Change(ByVal Target As Range) Dim y As Long Dim z As Long If Target.Address = "$A$1" Then y = Range("B1").Value z = y + 1 Range("A1").Value = z Range("B1").Value = z End If End Sub HTH Jeff -----Original Message----- One of my sales managers wants to be able to use a cell as a "tally" space, of sorts. In other words, she wants to be able to hit the number "1" key to just add one to whatever number or sum already exists in the cell. Is this possible? Normally, of course, hitting the number "1" key in a cell containing a number of formula would simply overwrite the existing contents with simply "1." This tally method, apparently, she prefers over having to edit the formula, like =1+1+1+1 Thanks in advance, Vinod .
642 Unfortunately, changing the color doesn't generate an event. You could use an OnTime macro to check once per second (or more likely every few seconds) - see /excel/ontime.htm or perhaps you could call it from the Worksheet_SelectionChange() event macro so that the total would update when the user selected another cell. BTW - you can find a more efficient macro that also allows you to specify in the cell call whether to count background or font color at /excel/colors.htm In article <90bc4e73.0206241153.4b074f2@posting.google.com, Paul Simon <psimon@snet.net wrote: Jan's method works with foreground colors. Dave's works with background colors. Both are great, and I can make use of this myself. The major problem with both methods is that when you change a color, the sum or the count does not change. For example, if I change a green color to a red, neither the green totals nor the red totals changes. Can either of you offer a solution to this?
665 How about just using a macro assigned to some keys that can easily be pressed at once (e.g. Ctrl+Shift+z)? Try this: ToolsMacroRecord Press Shift+z to name the shortcut key. Click OK. Then stop the macro. do this: ToolsMacroMacrosEdit delete anything in the macro that was started and copy and paste the macro shown here. Sub Macro1() ActiveCell = ActiveCell + 1 End Sub Edit the macro if you ever want to change the increment value or else add another macro to do another value. --Brian Taylor Refinate (C)2001, 20 free trials. Click to: Rearrange Cells or Cell Content, Find Duplicates, Count Items, Import, much more. www.adetaylor.com "V x" <death@death.com wrote in message news:evt4YP7GCHA.404@tkmsftngp13... One of my sales managers wants to be able to use a cell as a "tally" space, of sorts. In other words, she wants to be able to hit the number "1" key to just add one to whatever number or sum already exists in the cell. Is this possible? Normally, of course, hitting the number "1" key in a cell containing a number of formula would simply overwrite the existing contents with simply "1." This tally method, apparently, she prefers over having to edit the formula, like =1+1+1+1 Thanks in advance, Vinod
671 No problem..we all have those days :) "" <DavidH@OzGrid.com wrote in message news:28yR8.29$FU5.40386@vicpull1.telstra.net... Sorry Tim. -- FREE EXCEL NEWSLETTER /News/2home.htm Get the OzGrid Add-in /Services/AddinExamples.htm If it's Excel, then it's us! "Tim Zych" <tzych@earthlink.net wrote in message news:OteXv7zGCHA.1932@tkmsftngp10... | "" <DavidH@OzGrid.com wrote in message | news:bKxR8.27$FU5.37950@vicpull1.telstra.net... | You took | the time to point out possible errors but offered no alternative. | | Yikes...are we both reading the same thread? | | Here's a copy of my first response. (Note the 'On Error Resume Next' I | inserted). | | | | "Tim Zych" <tzych@earthlink.net wrote in message | | news:#QxLrqtGCHA.1156@tkmsftngp13... | | | Just to add to this thread: | | | | | | SpecialCells requires error handling in case the range does not | contain | | | constants, e.g.: | | | | | | On Error Resume Next | | | Set Rng1 = Selection.SpecialCells(xlCellTypeConstants) | | | On Error GoTo 0 | | | ' Rest of code... | | | Contructive critisism is good, outright negativity is bad, don't you agree | ? | | Um, yeah. | | | "" <DavidH@OzGrid.com wrote in message | news:bKxR8.27$FU5.37950@vicpull1.telstra.net... | Bugger :o) Who removed the On Error Resume Next. | | It would have been more helpful to the OP if you guys demonstrated the use | of an error handler rather than just saying it doesn't have one. You took | the time to point out possible errors but offered no alternative. | Contructive critisism is good, outright negativity is bad, don't you agree | ? | | | | Sub ConvertTextToNumberFaster() | Dim rCell As Range, Rng1 As Range | | On error Resume next | Set Rng1 = Selection.SpecialCells(xlCellTypeConstants) | | If Rng1 Is Nothing Then | MsgBox "No constants" | On error GoTo 0 | Exit Sub | End If | | On error GoTo 0 | Rng1.EntireColumn.Insert | Rng1.Offset(0, -1).FormulaR1C1 = _ | "=IF(RIGHT(RC[1])=""-"",SUBSTITUTE(RC[1],""-"","""")" _ | & "*-1,RC[1]*1)" | Rng1.Offset(0, -1) = Rng1.Offset(0, -1).Value | 'OPTIONAL | Rng1.EntireColumn.Delete | Application.CutCopyMode = False | End Sub | | | | -- | | | | | FREE EXCEL NEWSLETTER | /News/2home.htm | Get the OzGrid Add-in | /Services/AddinExamples.htm | If it's Excel, then it's us! | | | | "Tim Zych" <tzych@earthlink.net wrote in message | news:#duQFnzGCHA.2448@tkmsftngp10... | | Hmmm, Excel returns an error (1004, no cells were found) for me if there | are | | no "special cells". I'm using XL2000 and I know the same occurs with 97. | | | | | | "" <DavidH@OzGrid.com wrote in message | | news:qRwR8.19$FU5.33944@vicpull1.telstra.net... | | I agree Tim, but my code already has this covered:o) | | | | | | | | -- | | | | | | | | | | FREE EXCEL NEWSLETTER | | /News/2home.htm | | Get the OzGrid Add-in | | /Services/AddinExamples.htm | | If it's Excel, then it's us! | | | | "Tim Zych" <tzych@earthlink.net wrote in message | | news:#QxLrqtGCHA.1156@tkmsftngp13... | | | Just to add to this thread: | | | | | | SpecialCells requires error handling in case the range does not | contain | | | constants, e.g.: | | | | | | On Error Resume Next | | | Set Rng1 = Selection.SpecialCells(xlCellTypeConstants) | | | On Error GoTo 0 | | | ' Rest of code... | | | | | | | | | "" <DavidH@OzGrid.com wrote in message | | | news:W5fR8.16$TG5.28441@vicpull1.telstra.net... | | | Hi Andonny | | | | | | If you are converting numeric text constants to numbers then a | Loop | | is | | a | | | pretty slow method, as loops are generally slow. Try the code | below: | | | | | | Sub ConvertTextToNumberFaster() | | | Dim rCell As Range, Rng1 As Range | | | | | | Set Rng1 = Selection.SpecialCells(xlCellTypeConstants) | | | | | | If Rng1 Is Nothing Then | | | MsgBox "No constants" | | | Exit Sub | | | End If | | | | | | Range("IV65536").Copy | | | Rng1.PasteSpecial , xlPasteSpecialOperationAdd | | | Application.CutCopyMode = False | | | End Sub | | | | | | | | | Just select the entire Column and run the code. It assumes that | cell | | | IV65536 | | | is empty. | | | | | | | | | -- | | | | | | | | | | | | | | | FREE EXCEL NEWSLETTER | | | /News/2home.htm | | | Get the OzGrid Add-in | | | /Services/AddinExamples.htm | | | If it's Excel, then it's us! | | | | | | "Andonny" <wje@multiline.com.au wrote in message | | | news:#5hmxioGCHA.2324@tkmsftngp09... | | | | Hi, | | | | I am trying to convert this macro that it works on the column | | selected | | | | instead of column A:A. | | | | I just like to highlight the column in question and then run the | | macro. | | | | | | | | Your help is very much appreciated | | | | Andonny | | | | | | | | Sub ConvertTextToNumber() | | | | Dim Cell As Range, Rng1 As Range | | | | Set Rng1 = Intersect(ActiveSheet.UsedRange, | | ActiveSheet.Range("A:A")) | | | | For Each Cell In Rng1 | | | | If Not IsEmpty(Cell) And Not Cell.HasFormula And | IsNumeric(Cell) | | Then | | | | Cell = CDbl(Cell) | | | | End If | | | | Next Cell | | | | End Sub | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
681 I have beat my head against that wall so many times. The ranges work fine in the worksheet, and either don't work at all in the chart, or only show one point, or other weird things happen. I stick to the OFFSETs now, because I know they will work. What can be done, of course, is define the named range with INDIRECT, then array enter it into a worksheet range, and then refer to this range in the chart, either directly through the address, or through another dynamic range, if the number of cells changes. - Jon ------- Jon Peltier, Microsoft Excel MVP /jonpeltier/Excel/index.html _______ In article <J9cR8.7$TG5.21637@vicpull1.telstra.net, said... Thanks Jon, didn't know that. I assumed that as a chart can read a dynamic range, it would accept other named ranges that uses the INDIRECT. -- "Jon Peltier" <jonpeltier@yahoo.com wrote in message news:#U$mtjmGCHA.2452@tkmsftngp08... | Hi Dave - | | I just responded to an earlier post by John. The problem with charting from | a named range, is that while it seems as though it should work, and while the | same named range works in a worksheet, Excel's charting engine chokes on | named ranges that use INDIRECT in their RefersTo formula. A few other | functions also cause problems. | | I suggested a slightly different approach using OFFSET, which works fine with | charts. | | - Jon | ------- | Jon Peltier, Microsoft Excel MVP | /jonpeltier/Excel/index.html | _______ | | In article <3O9R8.3$TG5.14797@vicpull1.telstra.net, said... | | Hi John | | | Try using a Named range to refer indirectly to the cells storing the | addresses (InsertNameDefine), should work. | | | -- | **Possible solution has NOT been tested** | | | | | | FREE EXCEL NEWSLETTER | /News/2home.htm | Get the OzGrid Add-in | /Services/AddinExamples.htm | | "John Vera" <emperorj@hotmail.com wrote in message | news:1130001c21a30$e59a6b10$9be62ecf@tkmsftngxa03... | | | | | | Hi again. I'm trying to automatize my graphs so they read | | from a cell the range in which they are supposed to get | | points. Say, I have X data in column B and Y data in | | colmun D. My spreadsheet locates the appropriate range and | | displays it as text in four cells (upper and lower x | | range, upper and lower y range), say H4 says "$B$10" H5 | | says "$B$15", I4 has "$D$10" and I5 "$D$15" . I want the | | ranges in the graph to "read" the text strings in H4, H5 | | as the range in X and I4,I5 as the range for Y. Help very | | much appreciated. Typing indirect in the graph range is | | invalid (Excel 2000). | | | | Thanks, | | John | | |
697 Hi Dave, Not sure if I agree on the design problem Dave. I have a not-too-large workbook, but since it contains lots of array formula's it's recalc is very time consuming. When I need to do some data entry in that workbook I am glad I set calc to manual... Regards, Jan Karel Pieterse Excel TA/MVP -----Original Message----- Hi Mr NDP Not sure whether you set your Workbook to manual calculation on purpose or not, but if you do be very careful that you do not feed yourself erroneous results. It's all too easy to forget to force a calculation when not in Automatic. If you have a Workbook that needs to run in manual calculation it normally means you have a design problem. See the links below for spreadsheet designs: /News/CalculationRangeIndirect.htm /News/ConFormatCheckFind.htm /News/GoodVsBadDesignSpeedUpEvents.ht m -- "Mr NDP" <mr_ndp@yahoo.com wrote in message news:1245801c21bfe$a8004bb0$3bef2ecf@TKMSFTNGXA10... | That was it. I had my calculations set to manual. | | Thanks for the help... | | | -----Original Message----- | Hi: | | Do you have the calculation mode set to manual? If so, | hitting F9 should | update the formulas. | -- | Regards, | | Vasant. | | **No direct emails please--keep discussion in | newsgroup.** | | | "Mr NDP" <mr_ndp@yahoo.com wrote in message | news:12ed401c21bfb$928d7300$35ef2ecf@TKMSFTNGXA11... | All, | | I've noticed in my spreadsheets where I use the countif | function that the cells will not always update. Saving | the document seems to refresh the sheet and the | calculations are emplemented. Is there a way to | refresh | the formulas in a worksheet without having to save it? | | Thanks, | | Mr NDP | | | . | .
698 Hi, Some things to do: Options to try and open a corrupt file - Set calculation to manual - open the file, but disable macros (assuming you've set macro security to medium: Tools, Macro, security) - As soon as you've clicked the disable macros button, press control-pageup or control-pagedown, thus changing sheets. If that does not work, try creating a link to the file: ='c:\my documents\[MyFileName.xls]Sheet1'!A1 and copy right and down. This at least gets you the worksheets values. Sometimes the Excel viewer (or Word) enables you to open the file and copy information out of it. Also, Excel XP can sometimes repair XL files with trouble. Lastly: Download the office suite from www dot sun dot com slash staroffice (awkward spelling to hopefully avoid another autodeletion of posting...) it's a killer app for file recovery. Regards, Jan Karel Pieterse Excel TA/MVP -----Original Message----- Dear MS Support Team, I am having problem with one of the excel s/sheet I used until Friday evening. I am using MS-Excel 97 SR-1 and this s/sheet worked perfectly until Friday evening. It is a very simple data-only s/sheet..no macros/ scripts/ program at all. I saved this successfully on my 'C' drive on Friday night and tried to open it this morning but got a message box (attached in the file) with the following error - "Error Message : Address: excel.exe - Application Error" It doesn't even allow me to open the s/sheet. I have checked this file for virus and there are none. Would appreciate if you can help me in this matter as I spent considerable amount of time preparing this s/sheet. Regards, Ash .
699 Hi Jan If you read the links you will see that I say array formulae are the main cause of sloooowww calculations. Arrays (IMO) are a quick fix superficial approach to a possible complex problem. The use of Excels database functions can often be used in place of arrays as can Pivot Tables etc I still hold firm that any spreadsheet that requires manual calculation has a spreadsheet design problem. You run the risk of reading uncalculated results. It's a bit like you car brakes are rubbing (and slowing you down) so you disconnect them and rely on the handbrake. -- "Jan Karel Pieterse" <pieterse_home@compuserve.com wrote in message news:ff2101c21c0e$9c93ffa0$9ee62ecf@tkmsftngxa05... | Hi Dave, | | Not sure if I agree on the design problem Dave. | I have a not-too-large workbook, but since it contains | lots of array formula's it's recalc is very time | consuming. When I need to do some data entry in that | workbook I am glad I set calc to manual... | | Regards, | | Jan Karel Pieterse | Excel TA/MVP | | -----Original Message----- | Hi Mr NDP | | Not sure whether you set your Workbook to manual | calculation on purpose or | not, but if you do be very careful that you do not feed | yourself erroneous | results. It's all too easy to forget to force a | calculation when not in | Automatic. If you have a Workbook that needs to run in | manual calculation it | normally means you have a design problem. See the links | below for | spreadsheet designs: | | /News/CalculationRangeIndirect.htm | /News/ConFormatCheckFind.htm | /News/GoodVsBadDesignSpeedUpEvents.ht | m | | | -- | | | | | FREE EXCEL NEWSLETTER | /News/2home.htm | Get the OzGrid Add-in | /Services/AddinExamples.htm | If it's Excel, then it's us! | | "Mr NDP" <mr_ndp@yahoo.com wrote in message | news:1245801c21bfe$a8004bb0$3bef2ecf@TKMSFTNGXA10... | | That was it. I had my calculations set to manual. | | | | Thanks for the help... | | | | | | -----Original Message----- | | Hi: | | | | Do you have the calculation mode set to manual? If so, | | hitting F9 should | | update the formulas. | | -- | | Regards, | | | | Vasant. | | | | **No direct emails please--keep discussion in | | newsgroup.** | | | | | | "Mr NDP" <mr_ndp@yahoo.com wrote in message | | news:12ed401c21bfb$928d7300$35ef2ecf@TKMSFTNGXA11... | | All, | | | | I've noticed in my spreadsheets where I use the | countif | | function that the cells will not always update. | Saving | | the document seems to refresh the sheet and the | | calculations are emplemented. Is there a way to | | refresh | | the formulas in a worksheet without having to save | it? | | | | Thanks, | | | | Mr NDP | | | | | | . | | | | | . |
707 Dear Tom Hope you remember me whom you helped to write the macro below. This really helpful to my job, however when I apply it to an excel worksheet of 15 columns, 40 thousand records I find it has to work for several hours, which is impractical. I don't really know which part of the marco waste most of the time. So I write here and want to know if the marco can be trimmed so as to make it run more faster. Your help is very much appreciated! Best regards Jason ----- Original Message ----- From: "Tom Ogilvy" <twogilvy@msn.com Newsgroups: microsoft.public.excel.misc Sent: Monday, May 13, 2002 5:46 AM Subject: Re: How to handle worksheet and save it as xls.file? That revision was based on an earlier, slightly different version of the original - it had hard coded paths for the file operations. This has the sPath variable (like the original) to make it easier to specify the location for the new files: Sub StartSplitout() Dim sh As Worksheet Dim sh1 As Worksheet Dim varr As Variant Dim rng As Range Set rng = Range(Cells(1, 1), Cells(1, "IV").End(xlToLeft)) varr = rng.Value Cells(1, 1).EntireRow.Delete ActiveSheet.Copy Set sh = ActiveSheet Splitout sh, varr ThisWorkbook.Activate Cells(1, 1).EntireRow.Insert Cells(1, 1).Resize(, UBound(varr, 2)).Value = varr End Sub Sub Splitout(sh As Worksheet, varr As Variant) Dim bContinue As Boolean Dim rng As Range, Cell As Range Dim rw As Long, sh1 As Worksheet Dim sPath As String sPath = "C:\Data\" bContinue = False Set rng = sh.Range(sh.Cells(1, 1), _ sh.Cells(1, 1).End(xlDown)) For Each Cell In rng If Cell.Row < 1 Then If Cell.Value < _ Cell.Offset(-1, 0).Value Then bContinue = True rw = Cell.Row Exit For End If End If Next If bContinue Then sh.Copy Set sh1 = ActiveSheet sh1.Range(sh1.Cells(1, 1), sh1.Cells(rw - 1, 1)). _ EntireRow.Delete sh.Range(sh.Cells(rw, 1), _ sh.Cells(Rows.Count, 1).End(xlUp)) _ .EntireRow.Delete sh.Cells(1, 1).EntireRow.Insert sh.Cells(1, 1).Resize(, UBound(varr, 2)).Value = varr On Error Resume Next Kill sPath & sh.Cells(2, 1).Value & _ ".xls" On Error GoTo 0 sh.Parent.SaveAs sPath & _ sh.Cells(2, 1).Value & ".xls" sh.Parent.Close SaveChanges:=False Splitout sh1, varr Else On Error Resume Next Kill sPath & sh.Cells(2, 1).Value & _ ".xls" On Error GoTo 0 sh.Cells(1, 1).EntireRow.Insert sh.Cells(1, 1).Resize(, UBound(varr, 2)).Value = varr sh.Parent.SaveAs sPath & _ sh.Cells(2, 1).Value & ".xls" sh.Parent.Close SaveChanges:=False End If End Sub Regards, Tom Ogilvy
709 Hi Jason I am sure Tom will be along soon to help you, but in the mean time you might find these links of use: /VBA/VBACode.htm /VBA/SpeedingUpVBACode.htm /VBA/VBALoops.htm Hope they help -- "ims" <kft_icq@myrealbox.com wrote in message news:#jjYydCHCHA.368@tkmsftngp13... | Dear Tom | | Hope you remember me whom you helped to write the macro below. | This really helpful to my job, however when I apply it to an excel worksheet | of 15 columns, 40 thousand records I find it has to work for several hours, | which is impractical. | I don't really know which part of the marco waste most of the time. | So I write here and want to know if the marco can be trimmed so as to make | it run more faster. | Your help is very much appreciated! | | Best regards | | Jason | | | | ----- Original Message ----- | From: "Tom Ogilvy" <twogilvy@msn.com | Newsgroups: microsoft.public.excel.misc | Sent: Monday, May 13, 2002 5:46 AM | Subject: Re: How to handle worksheet and save it as xls.file? | | | That revision was based on an earlier, slightly different version of the | original - it had hard coded paths for the file operations. This has the | sPath variable (like the original) to make it easier to specify the | location | for the new files: | | Sub StartSplitout() | Dim sh As Worksheet | Dim sh1 As Worksheet | Dim varr As Variant | Dim rng As Range | Set rng = Range(Cells(1, 1), Cells(1, "IV").End(xlToLeft)) | varr = rng.Value | Cells(1, 1).EntireRow.Delete | ActiveSheet.Copy | Set sh = ActiveSheet | Splitout sh, varr | ThisWorkbook.Activate | Cells(1, 1).EntireRow.Insert | Cells(1, 1).Resize(, UBound(varr, 2)).Value = varr | End Sub | | Sub Splitout(sh As Worksheet, varr As Variant) | Dim bContinue As Boolean | Dim rng As Range, Cell As Range | Dim rw As Long, sh1 As Worksheet | Dim sPath As String | sPath = "C:\Data\" | bContinue = False | Set rng = sh.Range(sh.Cells(1, 1), _ | sh.Cells(1, 1).End(xlDown)) | For Each Cell In rng | If Cell.Row < 1 Then | If Cell.Value < _ | Cell.Offset(-1, 0).Value Then | bContinue = True | rw = Cell.Row | Exit For | End If | End If | Next | If bContinue Then | sh.Copy | Set sh1 = ActiveSheet | sh1.Range(sh1.Cells(1, 1), sh1.Cells(rw - 1, 1)). _ | EntireRow.Delete | sh.Range(sh.Cells(rw, 1), _ | sh.Cells(Rows.Count, 1).End(xlUp)) _ | .EntireRow.Delete | sh.Cells(1, 1).EntireRow.Insert | sh.Cells(1, 1).Resize(, UBound(varr, 2)).Value = varr | On Error Resume Next | Kill sPath & sh.Cells(2, 1).Value & _ | ".xls" | On Error GoTo 0 | sh.Parent.SaveAs sPath & _ | sh.Cells(2, 1).Value & ".xls" | sh.Parent.Close SaveChanges:=False | Splitout sh1, varr | Else | On Error Resume Next | Kill sPath & sh.Cells(2, 1).Value & _ | ".xls" | On Error GoTo 0 | sh.Cells(1, 1).EntireRow.Insert | sh.Cells(1, 1).Resize(, UBound(varr, 2)).Value = varr | sh.Parent.SaveAs sPath & _ | sh.Cells(2, 1).Value & ".xls" | sh.Parent.Close SaveChanges:=False | End If | End Sub | | Regards, | Tom Ogilvy | | | |
713 Knowledge Seeker <AbsolutelyNoSpamAccepted@earthlink.net wrote in message news:<65jfhu4cnmc8kf1m14rij4lhmhs48m5303@4ax.com... I have a spreadsheet with about 1000 rows that has a column for name. The following are sample names: J. K. Rowling George R. R. Martin Robert J. Sawyer Ken MacLeod Lois McMaster Bujold Walter M. Miller, Jr. C. J. Cherryh Michael P. Kube-McDowell Jerry Pournelle and Larry Niven I need a formula that will take the name and create two new columns. The first new column will contain the last name and the second new column will contain the rest of the name. Can you help me?? As per my last post, here's my sample code: it doesn't do *exactly* what you want, but splits a full-name into First, Mid and Last names. You might be able to modify to your purposes. The meat is the F_M_L_from_F function. Then there are some helper functions, and a test subroutine to show an example. (BTW, if you are using Excel2000, you might not need to define your own Split function, but it doesn't exist in Excel97). This is 268 lines of convoluted horror, and should not be taken as an ideal of programming style... I'm definitely going to refactor/redesign this code at some point, but it might give you some sort of ideas... --------------------- Option Explicit Function F_M_L_from_F(fullname As String) As Collection ' ' Takes a string name, and returns a Collection of ' 3 items (First, Middle, Last) ' ' Horribly complicated because it has to be able to ' deal with quite a lot of complexity ' ' John Brown John Brown ' John M Brown John M Brown ' Hans van Straaten Hans van Straaten ' Hans-van Straaten Hans van Straaten ' Hans van-Straaten Hans van Straaten ' Anne Le Duc Anne Le Duc ' etc. ' (Having said that, it could probably do with being ' rewritten, possibly using something a bit more scalable ' and powerful, like regexes) ' Split the full name into a list (on spaces) Dim names As Collection Set names = split(" ", fullname) ' Create strings for first, mid, last, and various ' temporary variables ' (did I mention that this is a horrible Function) Dim First As String, Mid As String, Last As String Dim newfirst As String Dim mylast As String, myname As String ' Create lists for Middle and Last names Dim mids As New Collection Dim lasts As New Collection ' shift first name onto var 'first' If names.Count 0 Then First = names(1) names.Remove (1) ' Check firstnames for hyphenated suffixes Dim firstnames As New Collection Set firstnames = split("-", First) If firstnames.Count 1 Then newfirst = firstnames(1) firstnames.Remove (1) ' Capitalise first name properly If newfirst Like "[a-z]*" Then newfirst = TCase(newfirst) Dim nextfirst As String nextfirst = firstnames(1) ' If the next 'first name' looks like a surname ' prefix then push it onto the surnames list If nextfirst Like "[a-z]*" _ Or nextfirst Like "Von" Or nextfirst Like "Van" _ Or nextfirst Like "De" Or nextfirst Like "Der" _ Or nextfirst Like "Le" Or nextfirst Like "La" _ Then names.Add join(" ", firstnames), , names.Count First = newfirst Else ' Or, if it is like a middle initial (or two) ' the push explicitly onto the Middle Initials ' list If nextfirst Like "[A-Z]" Or nextfirst Like "[A-Z][A-Z]" Then mids.Add nextfirst firstnames.Remove (1) names.Add join(" ", firstnames), , names.Count First = newfirst End If End If Else: If First Like "[a-z]*" Then First = TCase(First) End If ' Split into Mid & Last Dim is_mid As Boolean is_mid = True While names.Count 0 myname = names(1) names.Remove (1) If is_mid Then ' Some names are added to Middle names, and ' others to Last names. le and la are prepended ' to surnames, while Von, Van, De, Der etc. ' are treated as middle names. If myname Like "[A-Z]" Or myname Like "[A-Z][A-Z]" _ Or (myname Like "[a-z]*" _ And myname < "le" _ And myname < "la") _ Or myname Like "Von" Or myname Like "Van" _ Or myname = "De" Or myname = "Der" Then If myname Like "[a-z]" Then myname = UCase(myname) mids.Add myname Else is_mid = False lasts.Add myname End If Else lasts.Add myname End If Wend ' If we've been too enthusiastic, and not supplied a ' lastname... then get one, either from middle name or ' first name If lasts.Count = 0 Then If mids.Count 0 Then lasts.Add mids(mids.Count) lasts.Add (mylast) mids.Remove (mids.Count) Else lasts.Add (First) First = "" End If End If ' Check last names for hyphenated prefixes Dim newlast As New Collection Dim lastnames As New Collection Set lastnames = split("-", lasts(1)) If lastnames.Count 1 Then lasts.Remove (1) Dim in_prefix As Boolean in_prefix = True While (lastnames.Count 0) mylast = lastnames(1) lastnames.Remove (1) ' If the last name was actually a Middle name ' then push it back onto the middle names If in_prefix = True And (mylast Like "[A-Z]" _ Or mylast Like "[A-Z][A-Z]" _ Or mylast Like "Von" Or mylast Like "Van" _ Or mylast Like "De" Or mylast Like "Der" _ Or mylast Like "von" Or mylast Like "van" _ Or mylast Like "de" Or mylast Like "der") Then mids.Add mylast Else in_prefix = False If mylast Like "[a-z]" Then mylast = TCase(mylast) newlast.Add mylast End If Wend If Not newlast Is Nothing Then Dim pref_last As String Dim mytoken As Variant Dim mytoken1 As String pref_last = join("-", map_TCase(newlast)) If lasts.Count 0 Then lasts.Add pref_last, , 1 Else lasts.Add pref_last End If End If End If ' Generate the complete First, Mid, and Last names ' from the lists we've created Mid = join(" ", mids) Last = join(" ", map_TCase(lasts)) ' Create a new list to put them in Dim res As New Collection res.Add First res.Add Mid res.Add Last ' The return value will be this list Set F_M_L_from_F = res Else ' return a list with 3 empty values... names.Add "" names.Add "" names.Add "" Set F_M_L_from_F = names End If End Function Function join(jstring As String, coll As Collection) As String ' a helper sub. Returns the list supplied interspersed with ' the join string. ' e.g. ' join("-", "Hello", "World", 1,2,3) - Hello-World-1-2-3 Dim token As Variant For Each token In coll If join < "" And token < "" Then join = join & jstring join = join & token Next End Function Function split(split_string As String, orig_string As String) As Collection ' Helper function to return a list from a string ' e.g. ' split(" ", "I like banana splits") ' - "I", "like", "banana", "splits" ' preare the new list Dim coll As New Collection Dim pos pos = 1 Dim token As String Dim my_string As String my_string = orig_string 'copied so as not to modify the original string ' As long as there's some string left to scan, look for the ' split value in it, then add everything to the left of it ' (