| Article | Body |
| 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 ' (using the Left operator) to our new list. ' Then Redefine the string as the bit to the right of that ' (using the Mid operator) While my_string < "" And pos < 0 pos = InStr(1, my_string, split_string) If pos 0 Then token = Left(my_string, pos - 1) If token < "" Then coll.Add token my_string = Mid(my_string, pos + Len(split_string)) End If Wend ' By default, only add the string if it's not blank. ' this means that multiple delimiters don't create ' multiple fields. ' e.g. ' split("-", "Hello-----World") ' - "Hello", "World" ' ' This might not be what you want ("Hello","","","","","World") ' but it's the correct default for this particular application. If my_string < "" Then coll.Add my_string Set split = coll End Function Function TCase(orig As String) As String ' Set the string to title case ' e.g. Debug.Print TCase("fRED") - Fred ' ' (Have a feeling that there might be a VBA function ' to do this. If so, then d'oh!) TCase = UCase(Left(orig, 1)) & LCase(Mid(orig, 2)) End Function Function map_TCase(coll As Collection) As Collection ' Set a whole list to Title Case Dim coll2 As New Collection Dim toke As Variant Dim toke2 As String For Each toke In coll ' This kludge with toke2 doesn't always seem ' to be necessary? Very odd! toke2 = toke ' Only modify it if it's all lower or all upper. ' (e.g. we assume that if it already has sOmE capitalization ' then it must be deliberate... e.g. we don't want to flatten ' McCarthy, JoAnn, or other capitalizations from other languages.) If toke2 = LCase(toke2) Or toke = UCase(toke2) Then toke2 = TCase(toke2) coll2.Add toke2 Next Set map_TCase = coll2 End Function Sub test() Dim x As Collection Set x = F_M_L_from_F("willem van der valk") Debug.Print "First: " & x(1) Debug.Print "Mid: " & x(2) Debug.Print "Last: " & x(3) ' Result ' ------ ' First: Willem ' Mid: van der ' Last: Valk End Sub --------------------- Cheerio, osfameron/hakim /chickenman/ |
| 728 | I am using Excel 97 and I have a list of hex numbers. I would like to load them into column A and have Excel recognize them as hex numbers. I would then like to add the entire column together as hex numbers in order to generate a checksum. Is there a way to do this? Thanks, Tony -- For replies, please remove the * from my email address. |
| 729 | Hi Tony: Look at the HEX2DEC and DEC2HEX functions. You need the Analysis Toolpak installed to use these. -- Regards, Vasant. **No direct emails please--keep discussion in newsgroup.** "Tony P." <principe@*.pop.cle.ab.com wrote in message news:OxJ2r9EHCHA.2544@tkmsftngp08... I am using Excel 97 and I have a list of hex numbers. I would like to load them into column A and have Excel recognize them as hex numbers. I would then like to add the entire column together as hex numbers in order to generate a checksum. Is there a way to do this? Thanks, Tony -- For replies, please remove the * from my email address. |
| 735 | A solution using VBA is fine, I just have a lot to learn in that area (writing them myself, I mean). The only hitch with your answer is that there are usually multiple (anywhere from 1 to 20) hidden columns throughout each worksheet (possible example: A, B, C, F, G, J, T, S, and AB are hidden). That would mean that I would have to check manually which columns are hidden and then type them each into the name box to delete them. I am looking for something a little more functional. Thanks! Laura -----Original Message----- Hi Laura I assume you mean without VBA, if so assume Column "C" is hidden, type C:C in the name box (left of the formula bar) and push enter. Now go to EditDelete. There is no doubt a shortcut key for deleting Columns, you should find it here: /Excel/ExcelKeyBoardShortcutKeys.htm -- "Laura Wilde" <lwilde@new.rr.com wrote in message news:105fc01c21caa$e493ba60$a5e62ecf@tkmsftngxa07... | Is it possible to delete hidden columns without unhiding | them? I get many workbooks sent to me with hidden | columns that contain data I do not need. Some are | formulas but I usually "copy" and "paste values" so there | is no worry of linking errors. | | Thanks for any help you can give me. | | Laura . |
| 737 | You could use a Pivot Table (instructions for Excel 2002 -- other versions will vary slightly). 1. Add a heading to each column (e.g. Letter, Number) 2. Select a cell in the list. 3. Choose DataPivotTable and PivotChart Report 4. Click Next, check the range, click Next 5. Click Layout. 6. Drag the Letter button to the Row area 7. Drag the Number button to the Data area, where it will become Sum of Number. 8. Click OK, click Finish Barry Bean wrote: I'm trying to set up a few templates in Excel to have the same functionality as my 123 spreadsheets. One function I haven't been able to find is aggregate. For instance, in lotus 123 I can convert the following list: A 7 B 6 c 1 A 4 B 2 To: A 11 B 8 c 1 How do I accomplish the same thing in Excel? -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 738 | Hi Laura: Sub DeleteHiddenRows() Dim c As Range For Each c In Rows(1).Cells If c.Width = 0 Then c.EntireColumn.Delete Next End Sub -- Regards, Vasant. **No direct emails please--keep discussion in newsgroup.** "Laura Wilde" <lwilde@new.rr.com wrote in message news:1278901c21cb4$bc02b420$3aef2ecf@TKMSFTNGXA09... A solution using VBA is fine, I just have a lot to learn in that area (writing them myself, I mean). The only hitch with your answer is that there are usually multiple (anywhere from 1 to 20) hidden columns throughout each worksheet (possible example: A, B, C, F, G, J, T, S, and AB are hidden). That would mean that I would have to check manually which columns are hidden and then type them each into the name box to delete them. I am looking for something a little more functional. Thanks! Laura -----Original Message----- Hi Laura I assume you mean without VBA, if so assume Column "C" is hidden, type C:C in the name box (left of the formula bar) and push enter. Now go to EditDelete. There is no doubt a shortcut key for deleting Columns, you should find it here: /Excel/ExcelKeyBoardShortcutKeys.htm -- FREE EXCEL NEWSLETTER /News/2home.htm Get the OzGrid Add-in /Services/AddinExamples.htm If it's Excel, then it's us! "Laura Wilde" <lwilde@new.rr.com wrote in message news:105fc01c21caa$e493ba60$a5e62ecf@tkmsftngxa07... | Is it possible to delete hidden columns without unhiding | them? I get many workbooks sent to me with hidden | columns that contain data I do not need. Some are | formulas but I usually "copy" and "paste values" so there | is no worry of linking errors. | | Thanks for any help you can give me. | | Laura . |
| 739 | Hi Laura Push Alt+F11, then go InsertModule and paste in the code below: Sub DeleteHiddenCols() Dim rCell As Range 'Runs on the active sheet Application.Calculation = xlCalculationManual For Each rCell In Rows(1) If rCell.EntireColumn.Hidden = True Then rCell.EntireColumn.Delete End If Next rCell Application.Calculation = xlCalculationAutomatic End Sub Psuh Alt+Q, then Alt+F8 click "DeleteHiddenCols" then Options and asign a shortcut key. -- Hope this helps. "Laura Wilde" <lwilde@new.rr.com wrote in message news:1278901c21cb4$bc02b420$3aef2ecf@TKMSFTNGXA09... | A solution using VBA is fine, I just have a lot to learn | in that area (writing them myself, I mean). | | The only hitch with your answer is that there are usually | multiple (anywhere from 1 to 20) hidden columns | throughout each worksheet (possible example: A, B, C, F, | G, J, T, S, and AB are hidden). That would mean that I | would have to check manually which columns are hidden and | then type them each into the name box to delete them. I | am looking for something a little more functional. | | Thanks! | | Laura | | | -----Original Message----- | Hi Laura | | | I assume you mean without VBA, if so assume Column "C" | is hidden, type C:C | in the name box (left of the formula bar) and push | enter. Now go to | EditDelete. There is no doubt a shortcut key for | deleting Columns, you | should find it here: | /Excel/ExcelKeyBoardShortcutKeys.htm | | | | -- | | | | | FREE EXCEL NEWSLETTER | /News/2home.htm | Get the OzGrid Add-in | /Services/AddinExamples.htm | If it's Excel, then it's us! | | | "Laura Wilde" <lwilde@new.rr.com wrote in message | news:105fc01c21caa$e493ba60$a5e62ecf@tkmsftngxa07... | | Is it possible to delete hidden columns without | unhiding | | them? I get many workbooks sent to me with hidden | | columns that contain data I do not need. Some are | | formulas but I usually "copy" and "paste values" so | there | | is no worry of linking errors. | | | | Thanks for any help you can give me. | | | | Laura | | | . | |
| 741 | As long as your careful with your formulas/values: Option Explicit Sub delhiddencols() Dim iCol As Long Dim deleteme As Range For iCol = 1 To 256 If Columns(iCol).Hidden = True Then If deleteme Is Nothing Then Set deleteme = Cells(1, iCol) Else Set deleteme = Union(Cells(1, iCol), deleteme) End If End If Next iCol If deleteme Is Nothing Then 'can't do anything Else deleteme.EntireColumn.Delete End If End Sub You may want to add some checks for protection/filters, etc. Things that will stop you from deleting columns. Maybe even a little check to ask "are you sure"? Laura Wilde wrote: A solution using VBA is fine, I just have a lot to learn in that area (writing them myself, I mean). The only hitch with your answer is that there are usually multiple (anywhere from 1 to 20) hidden columns throughout each worksheet (possible example: A, B, C, F, G, J, T, S, and AB are hidden). That would mean that I would have to check manually which columns are hidden and then type them each into the name box to delete them. I am looking for something a little more functional. Thanks! Laura -----Original Message----- Hi Laura I assume you mean without VBA, if so assume Column "C" is hidden, type C:C in the name box (left of the formula bar) and push enter. Now go to EditDelete. There is no doubt a shortcut key for deleting Columns, you should find it here: /Excel/ExcelKeyBoardShortcutKeys.htm -- FREE EXCEL NEWSLETTER /News/2home.htm Get the OzGrid Add-in /Services/AddinExamples.htm If it's Excel, then it's us! "Laura Wilde" <lwilde@new.rr.com wrote in message news:105fc01c21caa$e493ba60$a5e62ecf@tkmsftngxa07... | Is it possible to delete hidden columns without unhiding | them? I get many workbooks sent to me with hidden | columns that contain data I do not need. Some are | formulas but I usually "copy" and "paste values" so there | is no worry of linking errors. | | Thanks for any help you can give me. | | Laura . -- Dave Peterson ec35720@msn.com |
| 743 | Thanks everyone...all work perfectly! -----Original Message----- A solution using VBA is fine, I just have a lot to learn in that area (writing them myself, I mean). The only hitch with your answer is that there are usually multiple (anywhere from 1 to 20) hidden columns throughout each worksheet (possible example: A, B, C, F, G, J, T, S, and AB are hidden). That would mean that I would have to check manually which columns are hidden and then type them each into the name box to delete them. I am looking for something a little more functional. Thanks! Laura -----Original Message----- Hi Laura I assume you mean without VBA, if so assume Column "C" is hidden, type C:C in the name box (left of the formula bar) and push enter. Now go to EditDelete. There is no doubt a shortcut key for deleting Columns, you should find it here: /Excel/ExcelKeyBoardShortcutKeys.ht m -- "Laura Wilde" <lwilde@new.rr.com wrote in message news:105fc01c21caa$e493ba60$a5e62ecf@tkmsftngxa07... | Is it possible to delete hidden columns without unhiding | them? I get many workbooks sent to me with hidden | columns that contain data I do not need. Some are | formulas but I usually "copy" and "paste values" so there | is no worry of linking errors. | | Thanks for any help you can give me. | | Laura . . |
| 745 | Here is my dilema I have a simple formula to find minutes and the answer comes out to 80 minutes. In the long run i need that 80 minutes converted to 1h 20 min in any format i would be happy with 1.2 but what i am getting is a percentage such as 1.4. Im sorry if this sounds confusing. What I am doing is ex.=sum(e3/60) where e3 was equal to lets say 80 and it would give me 1.4 instead of what i was looking for in 1.2. Does anyone know the correct formula please. Bob |
| 746 | Here is my dilema I have a simple formula to find minutes and the answer comes out to 80 minutes. In the long run i need that 80 minutes converted to 1h 20 min in any format i would be happy with 1.2 but what i am getting is a percentage such as 1.4. Im sorry if this sounds confusing. What I am doing is ex.=sum(e3/60) where e3 was equal to lets say 80 and it would give me 1.4 instead of what i was looking for in 1.2. Does anyone know the correct formula please. Bob |
| 747 | Mel, What version of Excel are you using? In Excel 2002, you would go to Tools - Macro - Security and then on the Trusted Sources tab, check the box: Trust all installed add-ins and templates. Paul "Mel" <shakey@sonic.net wrote in message news:3D18F17A.5AC5C7CD@sonic.net... I do not know what I did but lately I must go thru three screens of marco warnings to open a blank or any excel sheet. First--- C:\whatever\library\sumif.xla then-----C:\whatever\analysis\analys32.xll then-----c:\whatever\start\personal.xls I can understand peresonal and then my requested sheet as they are medium security but what made the first two start requesting permission, they are of MS origion and should always be safe. Mel S. -- Nothing will ever be attempted if all possible objections must first be overcome. |
| 750 | In article <uqglCSIHCHA.2584@tkmsftngp08, "Bernie Deitrick" <deitbe@consumer.org wrote: Chefty, You can use a custom format "%"00.00 With custom formats, you can put just about anything you want in between double quotes and have it show up. HTH, Bernie Other option is to enter the number as .2323 (and similar) for the cells you want to show %. XL shows it as % of 1 ... kinda anti intuitive I know :) Bruce "Chefty"<erpy1234@yahoo.com wrote in message news:1273201c21c81$cc2e9900$39ef2ecf@TKMSFTNGXA08... How do i format cells to make 23.23 to %23.23 I don't want the percent to be %2323 whenever i click the percentage button it changes the # to %2323. I just want the symbol added in. -------------------------------------------------------------------- Oook ! NOTE remove the not_ from the address to reply. NO SPAM ! |
| 751 | Since XL stores times as fractional days, you can convert minutes to a time by dividing by 24*60, or 1440: A1: 80 A2: =A1/1440 === 1:20 when A2 is formatted as a time. In article <1273e01c21cc9$52668590$19ef2ecf@tkmsftngxa01, Bob <vbats2000@yahoo.com wrote: Here is my dilema I have a simple formula to find minutes and the answer comes out to 80 minutes. In the long run i need that 80 minutes converted to 1h 20 min in any format i would be happy with 1.2 but what i am getting is a percentage such as 1.4. Im sorry if this sounds confusing. What I am doing is ex.=sum(e3/60) where e3 was equal to lets say 80 and it would give me 1.4 instead of what i was looking for in 1.2. Does anyone know the correct formula please. Bob |
| 752 | Paul Watson wrote: Mel, What version of Excel are you using? In Excel 2002, you would go to Tools - Macro - Security and then on the Trusted Sources tab, check the box: Trust all installed add-ins and templates. Paul "Mel" <shakey@sonic.net wrote in message news:3D18F17A.5AC5C7CD@sonic.net... I do not know what I did but lately I must go thru three screens of marco warnings to open a blank or any excel sheet. First--- C:\whatever\library\sumif.xla then-----C:\whatever\analysis\analys32.xll then-----c:\whatever\start\personal.xls I can understand peresonal and then my requested sheet as they are medium security but what made the first two start requesting permission, they are of MS origion and should always be safe. Mel S. -- Nothing will ever be attempted if all possible objections must first be overcome. Thanks Paul, That works in 2000 version as well. Darned if I know how it got unchecked. Mel -- Nothing will ever be attempted if all possible objections must first be overcome. |
| 756 | Thanks Dave, However my problem is not calling it from within a VBA macro but I want an Excel developer who may know nothing about VBA to pass parameters to a VBA macro contained in a loaded Add-in from a button he has added to the spreadsheet. The syntax I used ('MyAddin.xla'!'Foo "param1"') from within the Assign Macro dialog of the excel button control seemed to be ignoring the 'MyAddin.xla' prefix and assuming it was in the workbook containing the button (Book1.xls). Steve What I was hoping for was "Dave Peterson" <ec35720@msn.com wrote in message news:3D18E8DF.4D4DEB54@msn.com... When I tried to pass a string to your Msg sub, I could do it like this: Application.Run "'book1.xla'!msg", "hi there!" I could also do it like this, if I did a Tools|references and set a reference to that other project. msg "hi there2" But I'm not sure if this was the answer to your question.... Steve Legg wrote: Hi, I am able to pass parameters to a VBA macro from the 'Assign Macro' dialog by enclosing in single quotes. e.g. Book1!'Msg "Hello"' to execute the macro in the open workbook 'Book1': Public Sub Msg(m As String) MsgBox m End Sub However I am unable to do the same for a macro in an open Add-in. If I try 'MyAddin.xla'!'Foo "param1"' I am getting an error saying The macro 'Book1'!'Foo "param1"' cannot be found. I can invoke Macros with no parameters in the Add-in. Anybody know why this is not working, and if there is a way to make it work? Thanks in advance. -- Dave Peterson ec35720@msn.com |
| 771 | Hi Jeffrey, Excel will not let you use DSUM as a function in a Pivot Table calculated field, you would have to create extra columns. Charles Williams ______________________ Decision Models The Excel Calculation Site www.DecisionModels.com "Jeffrey S. Pidel" <Jeffrey.Pidel@comcast.net wrote in message news:ORF#KsKHCHA.1604@tkmsftngp12... I am trying to create a Pivot tables with calculated fields that compare YTD and average per month per account of previous month versus YTD and actual per month per account of current month I am thinking of using dsum and daverage for the calculated fields. Do you think this will work Table layout Previous Month Current Month Accounts YTD Average YTD Actual 1000 2000 3000 4000 . . |
| 775 | Hi Jeffrey Just to add to what Charles has said, any Function which *requires* a range as it's argument(s) cannot be used in a Calculated field of a Pivot Table. That's about 90% of Excels functions :o( -- "Charles Williams" <Charles@DecisionModels.com wrote in message news:3d199c6b$0$27345$afc38c87@news.easynet.co.uk... | Hi Jeffrey, | | Excel will not let you use DSUM as a function in a Pivot Table calculated | field, you would have to create extra columns. | | Charles Williams | ______________________ | Decision Models | The Excel Calculation Site | www.DecisionModels.com | | "Jeffrey S. Pidel" <Jeffrey.Pidel@comcast.net wrote in message | news:ORF#KsKHCHA.1604@tkmsftngp12... | I am trying to create a Pivot tables with calculated fields | that compare | YTD and average per month per account | of previous month | versus | YTD and actual per month per account | of current month | I am thinking of using dsum and daverage for the | calculated fields. Do you think this will work | | Table layout | Previous Month Current Month | Accounts YTD Average YTD Actual | 1000 | 2000 | 3000 | 4000 | . | . | | | | | | | | |
| 785 | I'd suggest that you enter a formula like this somewhere: =CHOOSE(Current_Month,"Jan","Feb") extended for 12 months. I put it in cell K1 on Front. Then the formula in E11 on Marketing Exps can be changed to this: =SUMIF('Trial Balance'!$A$3:$A$891,A11,INDIRECT(Front!$K$1)) Also, I changed this formula to reference just column A. There is no need to reference the entire table and doing so just slows a calc down. -- Jim Rech Excel MVP |
| 791 | Otto, Nothing happens at all!! Thanks anyway, Rob. -----Original Message----- Rob I'm assuming that your workbook has only one sheet. If that is not the case, then add code to first select the pertinent sheet. I also added code to scroll the cell containing today's date to the top left of the display. Put this macro in your Workbook module. HTH Otto Private Sub Workbook_Open() Range("A:A").Find(What:=Date).Select With ActiveWindow .ScrollRow = ActiveCell.Row .ScrollColumn = ActiveCell.Column End With End Sub "Rob Pearce" <rob.pearce@consignia.com wrote in message news:123a201c21c5b$d54f7070$19ef2ecf@tkmsftngxa01... I have a worksheet that I use for a diary. In column A I have dates going on for the next 3 years. How can I get the workbook to open up on todays date everytime I open it up? I presume some kind of code in the auto_open sub. [Platform 2000] Thanks, Rob. . |
| 798 | Thanks It works a treat. David -----Original Message----- I'd suggest that you enter a formula like this somewhere: =CHOOSE(Current_Month,"Jan","Feb") extended for 12 months. I put it in cell K1 on Front. Then the formula in E11 on Marketing Exps can be changed to this: =SUMIF('Trial Balance'!$A$3:$A$891,A11,INDIRECT(Front! $K$1)) Also, I changed this formula to reference just column A. There is no need to reference the entire table and doing so just slows a calc down. -- Jim Rech Excel MVP . |
| 805 | I posted this through google, but it seemed to crash. So I thought I'd use CDO: I _think_ I understand: I dropped a button from the forms toolbar onto a worksheet. I have 3 examples of the .onaction stuff. Comment out two of them. Option Explicit Sub testme() Dim cb As Button Set cb = ActiveSheet.Buttons(1) Dim c As Range Set c = Range("A1") c.Value = "rangeA1value" Dim i As Long i = 8 With cb .OnAction = ThisWorkbook.Name _ & "!'testme1 " & Chr(34) & c.Value & Chr(34) & "'" .OnAction = ThisWorkbook.Name _ & "!'testme2 ""Hello"", 3'" .OnAction = Workbooks("book2.xls").Name _ & "!'testme3 " & Chr(34) _ & "different wkbk" & Chr(34) & "'" End With End Sub Sub testme1(myval As Variant) MsgBox "From Testme1: " & myval End Sub Sub testme2(mystr As String, myval As Long) MsgBox "From Testme2: " & mystr & " " & myval * 10 End Sub and from Book2.xls Option Explicit Sub testme3(mystr As String) MsgBox "From " & ThisWorkbook.Name & ": " & mystr End Sub I've never used the CDO before. Watch out for linewrap problems. -----Original Message----- Thanks Dave, However my problem is not calling it from within a VBA macro but I want an Excel developer who may know nothing about VBA to pass parameters to a VBA macro contained in a loaded Add-in from a button he has added to the spreadsheet. The syntax I used ('MyAddin.xla'!'Foo "param1"') from within the Assign Macro dialog of the excel button control seemed to be ignoring the 'MyAddin.xla' prefix and assuming it was in the workbook containing the button (Book1.xls). Steve What I was hoping for was "Dave Peterson" <ec35720@msn.com wrote in message news:3D18E8DF.4D4DEB54@msn.com... When I tried to pass a string to your Msg sub, I could do it like this: Application.Run "'book1.xla'!msg", "hi there!" I could also do it like this, if I did a Tools|references and set a reference to that other project. msg "hi there2" But I'm not sure if this was the answer to your question.... Steve Legg wrote: Hi, I am able to pass parameters to a VBA macro from the 'Assign Macro' dialog by enclosing in single quotes. e.g. Book1!'Msg "Hello"' to execute the macro in the open workbook 'Book1': Public Sub Msg(m As String) MsgBox m End Sub However I am unable to do the same for a macro in an open Add-in. If I try 'MyAddin.xla'!'Foo "param1"' I am getting an error saying The macro 'Book1'!'Foo "param1"' cannot be found. I can invoke Macros with no parameters in the Add-in. Anybody know why this is not working, and if there is a way to make it work? Thanks in advance. -- Dave Peterson ec35720@msn.com . |
| 810 | Hi Reg, Would help if you indicated how unwanted rows are to be distinguished. You would probably want to use a macro. Some non programming techniques that might be useful are numbering an unused column with constant numbers using the fill handle, and sorting and deleting. Not knowing what you really want to delete the following works without a loop but the cells in Column A must be empty (no spaces, no formulas). [Dana DeLouis] Sub DelRowsWithEmptyColumnA() On Error Resume Next ' In case there are no blanks Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete ActiveSheet.UsedRange 'Resets UsedRange for Excel 97 End Sub Some more examples of macros can be found in "Deleting Rows" about halfway down Delete Cells/Rows in Range, based on empty cells /dmcritchie/excel/delempty.htm#rows HTH, Assistance with macros on my getstarted.htm page. , Microsoft MVP - Excel [site changed Nov. 2001] My Excel Macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Reg Sinha" <reginald.sinha@opbu.xerox.com wrote in message news:3D19CD32.CD80B40B@opbu.xerox.com... Dear All, I have a Excel file converted from a Crystal Report and it has unwanted rows which I wish to delete. Any ideas would be appreciated. Thanks Reg |
| 812 | Thanks David. I will visit your website. The rows to be deleted are blank rows without any formulas or text. Is this useful? wrote: Hi Reg, Would help if you indicated how unwanted rows are to be distinguished. You would probably want to use a macro. Some non programming techniques that might be useful are numbering an unused column with constant numbers using the fill handle, and sorting and deleting. Not knowing what you really want to delete the following works without a loop but the cells in Column A must be empty (no spaces, no formulas). [Dana DeLouis] Sub DelRowsWithEmptyColumnA() On Error Resume Next ' In case there are no blanks Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete ActiveSheet.UsedRange 'Resets UsedRange for Excel 97 End Sub Some more examples of macros can be found in "Deleting Rows" about halfway down Delete Cells/Rows in Range, based on empty cells /dmcritchie/excel/delempty.htm#rows HTH, Assistance with macros on my getstarted.htm page. , Microsoft MVP - Excel [site changed Nov. 2001] My Excel Macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Reg Sinha" <reginald.sinha@opbu.xerox.com wrote in message news:3D19CD32.CD80B40B@opbu.xerox.com... Dear All, I have a Excel file converted from a Crystal Report and it has unwanted rows which I wish to delete. Any ideas would be appreciated. Thanks Reg |
| 823 | You will probably have to insert three additional columns to separate your data. Column "B" should be: =Left(A1,2) Column "C" should be: =Mid(A1,3,5) Column "D" should be: =Right(A1,3). After parsing the data in this manner you can use the countif function. (If counting the number of cells with the string "JONES" you really don't need columns "B" and "D".) If you want to sum the values from columns "B" and/or "D", you can use the sumif function. -----Original Message----- I was wondering if anyone could help me out with what might be a simple issue. I need to uses the COUNTIF function (I think) to tally a unique serial number. For example, if column "A" contained the values listed below, I need to tally the all the numbers containing the name JONES. Any help would be greatly appreciated. A 19JONES110 17SMITH231 22SMITH321 64JONES332 32JONES884 11SMITH983 . |
| 834 | Thanks for the input. However, the asterisk trick seems to work just fine for our purpose. Example: =COUNTIF(A:A,"*jones*") <mpvoigt@cessna.textron.com wrote in message news:1262601c21d24$f58382e0$b1e62ecf@tkmsftngxa04... You will probably have to insert three additional columns to separate your data. Column "B" should be: =Left(A1,2) Column "C" should be: =Mid(A1,3,5) Column "D" should be: =Right(A1,3). After parsing the data in this manner you can use the countif function. (If counting the number of cells with the string "JONES" you really don't need columns "B" and "D".) If you want to sum the values from columns "B" and/or "D", you can use the sumif function. -----Original Message----- I was wondering if anyone could help me out with what might be a simple issue. I need to uses the COUNTIF function (I think) to tally a unique serial number. For example, if column "A" contained the values listed below, I need to tally the all the numbers containing the name JONES. Any help would be greatly appreciated. A 19JONES110 17SMITH231 22SMITH321 64JONES332 32JONES884 11SMITH983 . |
| 836 | Hi Dave, I think I may have made it unclear what I am trying achieve. I would like any old Excel developer to call an existing macro in an excel add-in I have written, passing parameters to it without the need to create a Macro themselves. The way I perceived it would work is by the developer dragging a button from the 'Forms' toolbar (which I wrongly called a control) and then right-button mouse clicking on the button and selecting the 'Assign Macro...' dialog, and from there just typing in something like: 'MyAddin.xla'!'MyMacro "param1","param2"' without worrying about any VBA code. Sorry I confused things with the example I gave.. Thanks again, Steve "Dave Peterson" <ec35720@msn.com wrote in message news:106f001c21d1d$b90eb0c0$9ee62ecf@tkmsftngxa05... I posted this through google, but it seemed to crash. So I thought I'd use CDO: I _think_ I understand: I dropped a button from the forms toolbar onto a worksheet. I have 3 examples of the .onaction stuff. Comment out two of them. Option Explicit Sub testme() Dim cb As Button Set cb = ActiveSheet.Buttons(1) Dim c As Range Set c = Range("A1") c.Value = "rangeA1value" Dim i As Long i = 8 With cb .OnAction = ThisWorkbook.Name _ & "!'testme1 " & Chr(34) & c.Value & Chr(34) & "'" .OnAction = ThisWorkbook.Name _ & "!'testme2 ""Hello"", 3'" .OnAction = Workbooks("book2.xls").Name _ & "!'testme3 " & Chr(34) _ & "different wkbk" & Chr(34) & "'" End With End Sub Sub testme1(myval As Variant) MsgBox "From Testme1: " & myval End Sub Sub testme2(mystr As String, myval As Long) MsgBox "From Testme2: " & mystr & " " & myval * 10 End Sub and from Book2.xls Option Explicit Sub testme3(mystr As String) MsgBox "From " & ThisWorkbook.Name & ": " & mystr End Sub I've never used the CDO before. Watch out for linewrap problems. -----Original Message----- Thanks Dave, However my problem is not calling it from within a VBA macro but I want an Excel developer who may know nothing about VBA to pass parameters to a VBA macro contained in a loaded Add-in from a button he has added to the spreadsheet. The syntax I used ('MyAddin.xla'!'Foo "param1"') from within the Assign Macro dialog of the excel button control seemed to be ignoring the 'MyAddin.xla' prefix and assuming it was in the workbook containing the button (Book1.xls). Steve What I was hoping for was "Dave Peterson" <ec35720@msn.com wrote in message news:3D18E8DF.4D4DEB54@msn.com... When I tried to pass a string to your Msg sub, I could do it like this: Application.Run "'book1.xla'!msg", "hi there!" I could also do it like this, if I did a Tools|references and set a reference to that other project. msg "hi there2" But I'm not sure if this was the answer to your question.... Steve Legg wrote: Hi, I am able to pass parameters to a VBA macro from the 'Assign Macro' dialog by enclosing in single quotes. e.g. Book1!'Msg "Hello"' to execute the macro in the open workbook 'Book1': Public Sub Msg(m As String) MsgBox m End Sub However I am unable to do the same for a macro in an open Add-in. If I try 'MyAddin.xla'!'Foo "param1"' I am getting an error saying The macro 'Book1'!'Foo "param1"' cannot be found. I can invoke Macros with no parameters in the Add-in. Anybody know why this is not working, and if there is a way to make it work? Thanks in advance. -- Dave Peterson ec35720@msn.com . |
| 840 | Bill, Two possibilities: 1) Create a "repeat" column of everything (you can then hide the original or something) containing the following (assuming B is repeat column, A is original): =IF(left(a1,1)=" ",right(a1,len(a1-1)),a1) 2) Create a Macro to sift through the data and look for leading spaces. You can either attach this to a button so that it runs whenever you click the button, or set it as an event procedure to run whenever the spreadsheet is updated. Something like: sub deletespaces() for each cell in worksheet data=cell.value if left(data,1)=" " then data=right(data,len(data-1)) cell.value=data end if next cell end sub Hope this helps, Steve -----Original Message----- Hi All, One more problem that I would really appreciate your help with. I do a lot of importing into a particular Excel 2000 database from all kinds of sources.I extract names, addresses, email, you name it. Included with the imports in some cells are leading spaces. They completely ruin sorts because all data sorted in these columns alphabetically, sorts a-z without the leading spaces, then continues sorting from a-z witht the leading spaces!!! How do I get rid of these leading spaces!!!! I tried using indent, left align, replace, nothing works. Do I have to go through all the cells with these leading spaces and delete them manually????!!!!!!!!!!!!!!!!! Thanks for your quick and concise response from my first question. You guys are great!!!! . |
| 846 | or, (not as "tight" a design, but...) you could use a conditional format so that if the cell value = 0, the color of the text for that cell is the same as the color of the background... ... that way the value would still be there (as a number), you just woulnd be able to see it. sometimes if you use the formula to insert "" instead of the result, and the cell is precedant to another formula, a calculation error (#VALUE!)occurs -----Original Message----- Hi Chris You could use =IF(SUMPRODUCT((Input!G3:G121<=90)*(Input!G3:G121=61)* (Input!M3:M121=R2)*(Input!L3:L121=P2))=0,"" ,SUMPRODUCT((Input!G3:G121<=90)*(Input!G3:G121=61)* (Input!M3:M121=R2)*(Input!L3:L121=P2))) Roger Govier Technology 4 U W98SR2 XL2K wrote: On Wed, 26 Jun 2002 09:16:00 -0700, "Chris Eilersen" <chrise@eilerc.com-- I am using SUMPRODUCT to calculate some columns on a worksheet. A sample of the usage would be: =SUMPRODUCT((Input!G3:G121<=90)*(Input!G3:G121=61)* (Input!M3:M121=R2)*(Inpu t!L3:L121=P2)) How can I add an if statement to leave the cell blank if the value of the formula is 0? (Right now it puts a 0 in the cell.) Thanks . |
| 857 | The COUNTIF function is not case sensitive. Alan Beban Earl Takasaki wrote: YOu can use the countif() function, but it is case sensitive and will recognize Smith and smith as two different entities. You can use an array formula (CTRL-SHIFT-ENTER): =SUM(IF(TRIM(UPPER(A1:A6)) = "SMITH",1,0)) HTH, -- Earl K. Takasaki Please reply to the group. "larry" <lwhersey@aol.com wrote in message news:10a8701c21dab$b1b9eb80$9ee62ecf@tkmsftngxa05... if I am entering a list of names or addresses in a column How can I count the number of times the same name or address appears. Example Column A smith jones AL Smith frank todd smith=2 |
| 861 | You can use a dynamic range in your VLOOKUP: =...VLOOKUP(B2,OFFSET(A2,,,COUNTA($A:$A)-1,4),4)... I'm assuming you have column headers in row 1. HTH Jason Atlanta, GA -----Original Message----- I've copied 3 columns of data from Access to Excel. In col D I have the following formula: =IF(B2=-1,C2,CONCATENATE(VLOOKUP(B2, $A$2:$D$614,4), ".", C2)) $A$2 - will ALWAYS be the first cell of this table. Col A-C - will always be the data columns copied. Col D - will always be the column with the function. Is there a formula to use that will let me determine the last row of the 3 columns I copied (all three are the same size) to sub in for $D$614. My other alternative is to use: $A$2:$D:$10000 as my list will never get that big. . |
| 890 | Hi Liz Assuming that in the 2002-01-01, the 2 characters to the right represent the day and not the month then =DATE(LEFT(A1,4),MID(A1,6,2),RIGHT(A1,2)) If the 2 characters to the right represent the month then use =DATE(LEFT(A1,4),RIGHT(A1,2),MID(A1,6,2)) -- -- Roger Govier Technology 4 U W98SR2 XL2K "Liz Crossland" <Elizabeth_Crossland@Key.com wrote in message news:1301601c21de8$7978e720$19ef2ecf@tkmsftngxa01... How can I convert a date in the format 2002-01-01 to dd/mm/yyyy easily? All help appreciated, deadline to meet! |
| 898 | Thanks for the help...I am going to give this a whirl and=20 see what I can make happen! =20 Scott -----Original Message----- Scott, Expletive deleted. Take your original formula and paste into any cell. ANY=20 CELL. Any cell that does NOT contain data. "Replace the first a1 with the cell that contains a phone=20 number on the MasterList (E5?)." "The formula should be on the MasterList sheet in the=20 column you want to use as a flag." I'm assuming the first telephone number is in E5 and your=20 match formula is in F5. If it is not in E5 then type in the actual=20 location. Yes. Both sheets need to be open. If you have two=20 different workbooks they both have to be open. John "Scott A. Jones" <sajones@rhtc.net wrote in message news:1380701c21d22$cdab89e0$35ef2ecf@TKMSFTNGXA11... Hey! Thanks for the help, but I am still a bit=20 confused. I am guessing when you say ,"Replace the first a1 with=20 the cell that contains a phone number on the MasterList (E5?)." You mean to put the match function there? That is confusing me a bit. Also, am I understanding correctly in believing that both sheets=20 need to be open for the function to work? Should I have one=20 as Sheet one, and the other as sheet 2, or what? What I tried was what you said, but I put the match function line in e1, and did a fill down, but that of course wiped out all my phone numbers, so that doesn't seem to be right...:) Anyway, than you for your help,=20 and any more help you can offer is greatly appreciated. Scott A. Jones -----Original Message----- Hi Scott, =3Dif(iserror(match(a1,donotcall!$a$1:$a$999,0)),"ok to call","do not call") Looks like an example. You just need to modify it. The formula should be on the MasterList sheet in the column you want to use as a flag. Replace the first a1 with the cell that contains a=20 phone number on the MasterList (E5?). "donotcall!" is the name of a sheet. Change it to "DoNotCallList" without the quotes. The "!" is required at the end of the sheetname. $a$1:$a$999 is where it is looking for a phone number match and probably should be $e$1:$e$999 Once you get it working select the cell and drag the mouse down the column. Do an Editfill down John "Scott A. Jones" <sajones@rhtc.net wrote in message news:12b8201c21b8c$d4073a10$35ef2ecf@TKMSFTNGXA11... Hi! I have posted about this problem once before and got some very useful information as a reply, but I am=20 still having trouble with making it work. As I am not an experienced Excel User, I am still trying to learn=20 how all the functions work. Anyway, here's the problem. I have 2 files. They are called: MasterList DoNotCallList The Master List is composed of a list of names and=20 phone numbers. The DoNotCallList is comprised of the same. The Names in the Lists start in Column A, Row 5. The Numbers in the Lists start in Column E, Row 5. All the Rows above are filled with headers and titles for the lists. =20 Both of the lists have about 110 names and numbers in them, though this may vary. What I want to do is use the Match Function to=20 compare the DoNotCallList to the Master List, and to either flag=20 or delete any matching numbers. The match will be done usi=CD{=9Dw=C0=1F=BC=03=DCy=F0p=EC<=F1pO=DA4=18 =EC=05ng the phone number, in an xxx-xxx-xxxx format. I have tried many different variations on the Match Function line=20 to compare the two, but after I apply the AutoFilter, it doesn't flag or delete any of the names. To test, I made sure the fist 10 numbers in each list were=20 duplicates. I suppose what the trouble is that I am having is=20 getting the Match Function to compare the two files, and not just matching something within the same list. So, what I need help on, is trying to determine the exact line that I would need to use to compare the two=20 files, and how I go about applying the line to the lists,=20 and how to start the filter once it is applied. I am very close, but there must be something I am doing wrong. The line I am using a variation on was originally provided to me as a reply to my first message. It is as=20 follows: =3Dif(iserror(match(a1,donotcall!$a$1:$a$999,0)),"ok to call","do not call") If anyone can help me figure out the exact changes I would need to make to this line to make it work with my files, I would be most appreciative. Thanks! Scott A. Jones . . . |
| 924 | Select the 2nd column (say column B), and, assuming the active cell in in row 1, enter this in the CF dialog: Formula is =COUNTIF(A:A, B1)0 (assuming the first column is A:A) then click on format and format Bold, color-coded, etc. In article <1118f01c21df8$cebbe330$2ae2c90a@hosting.microsoft.com, Frank <fjd@hitchcock.org wrote: I have a large list of 9 digit numbers down one column. I would like to be able to enter numbers down another column, and have the numbers change format IF they already exist in the other column. I was hoping I could set up CONDITIONAL FORMATTING so that the blank cells would be able to "look" into the original column and determine if I am entering a duplicate value (IE: I am hoping that any number that I am entering into the second column would go BOLD or COLOR-CODED if it is already entered into the first column). THANKS! |
| 926 | General module - tried to find ThisWorkbook module but have been unable to..... Could you point me in the right direction? Thanks. -----Original Message----- It worked ok for me. Did you put the code under ThisWorkbook module or in a General module? It's an easy mistake to make... Rob Pearce wrote: Otto, Nothing happens at all!! Thanks anyway, Rob. -----Original Message----- Rob I'm assuming that your workbook has only one sheet. If that is not the case, then add code to first select the pertinent sheet. I also added code to scroll the cell containing today's date to the top left of the display. Put this macro in your Workbook module. HTH Otto Private Sub Workbook_Open() Range("A:A").Find(What:=Date).Select With ActiveWindow .ScrollRow = ActiveCell.Row .ScrollColumn = ActiveCell.Column End With End Sub "Rob Pearce" <rob.pearce@consignia.com wrote in message news:123a201c21c5b$d54f7070$19ef2ecf@tkmsftngxa01... I have a worksheet that I use for a diary. In column A I have dates going on for the next 3 years. How can I get the workbook to open up on todays date everytime I open it up? I presume some kind of code in the auto_open sub. [Platform 2000] Thanks, Rob. . -- Dave Peterson ec35720@msn.com . |
| 942 | Gene, There isn't any "magic number" below which all is right with the world, and above which all hell will break loose. Moreover, there isn't even a good "range" to use, because it depends to a very large degree on what it is that makes up the size. For example, you can have a huge workbook consisting of primarily data, with relatively few formulas, that will work fine. You can also have a workbook of exactly the same file size, with a large proportion of formulas, that will not operate "acceptably" (usually in terms of recalculation time). Likewise, having a very large number of cell formats (every combination of font, font size, font color, background color, italics, bold, etc) can lead corruption of the file. Excel claims that it supports up to 4000, but I've run into corruption problems (in very complicated workbooks) when the number is around 2000 (client's penchant for formatting, not mine). Depending on the specific application, you may well find it useful to create a "hybrid" application of Access (or, better, SQL Server) for data storage and Excel for a calculation machine and front end. Access or another DB system is good for storage of very large amounts of data (Excel isn't that great at that), but databases are generally lousy at complex calculations, something that Excel is extremely good at. You may find it useful to take advantage of the strengths of both Excel and a DB. You can use Excel as the front end, and Access or SQL Server as the back end. Of course, this may require a lot of code, but there are ways to make all that work quite well. Your question is a good one. Unfortunately, there is not a good answer. It is sort of like asking "When I am too fat?". If you're single, probably now. If you are married, well maybe not yet. If you want a simple "rule of thumb", I wouldn't generally worry if the workbook file size is less than say 10-15 MB (assuming you are using a fairly new PC -- all bets are off if you have less than 64 MB RAM or a CPU less than, say, 600 MHz). When you get up around 15+ MB, it might be time to rethink the basic architecture of the application. But again, it depends greatly on specifically what it is that makes up that 15 MB. I guess, the real answer is "it depends". I'm willing to commit to that. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com chip@cpearson.com "Gene" <eugene.golden@motorola.com wrote in message news:1314e01c21e02$0d3ee9f0$19ef2ecf@tkmsftngxa01... Can someone please tell me what the recommended size limit is for Excel. Another words, when an Excel spread sheet is around xx mb in size you should either migrate to Access or bust up the spread sheet. Thanks |
| 950 | Please ignore this - I thought I was in the .programming group. Bernie "Bernie Deitrick" <deitbe@consumer.org wrote in message news:uKOiahgHCHA.2988@tkmsftngp12... Salman, Use the Worksheets collection. Dim nameVar As String nameVar = Worksheets(4).Name If you want to include graphs and such, use the Sheets collection. HTH, Bernie "Salman" <sahmadmit@hotmail.com wrote in message news:12e5701c21e04$8f97fc00$9be62ecf@tkmsftngxa03... Hi, How do I create a list of all the worksheets that I have in a workbook. Ideally I would like to do something like =worksheetname(4) and have the formula return the name of the 4th sheet in the workbook. Thanks. |
| 970 | One way to get there. Open your workbook in Excel. hit alt-F11 (this gets you to the VBE) hit ctrl-F4 (this displays the project explorer--no harm if it's already shown) You should see something that looks like VBAProject (Yourworkbookfilenamehere.xls) There's either a box with a + or - directly in front of this. If it's a +, then click on it to fan out its subordinates You should see an item named "microsoft Excel Objects" (expand this one if necessary) You should see an object for each worksheet and one named ThisWorkbook. Doubleclick on that one and you're there. (Code window should open on right hand side.) ========= Alternative way. Make the workbook less than fullscreen inside excel. (Window|Arrange|Tiled will work) Right click on that workbook's title bar and select view code. You're there. Good luck, Rob Pearce wrote: General module - tried to find ThisWorkbook module but have been unable to..... Could you point me in the right direction? Thanks. -----Original Message----- It worked ok for me. Did you put the code under ThisWorkbook module or in a General module? It's an easy mistake to make... Rob Pearce wrote: Otto, Nothing happens at all!! Thanks anyway, Rob. -----Original Message----- Rob I'm assuming that your workbook has only one sheet. If that is not the case, then add code to first select the pertinent sheet. I also added code to scroll the cell containing today's date to the top left of the display. Put this macro in your Workbook module. HTH Otto Private Sub Workbook_Open() Range("A:A").Find(What:=Date).Select With ActiveWindow .ScrollRow = ActiveCell.Row .ScrollColumn = ActiveCell.Column End With End Sub "Rob Pearce" <rob.pearce@consignia.com wrote in message news:123a201c21c5b$d54f7070$19ef2ecf@tkmsftngxa01... I have a worksheet that I use for a diary. In column A I have dates going on for the next 3 years. How can I get the workbook to open up on todays date everytime I open it up? I presume some kind of code in the auto_open sub. [Platform 2000] Thanks, Rob. . -- Dave Peterson ec35720@msn.com . -- Dave Peterson ec35720@msn.com |
| 1012 | In the userform's code module, assuming that your text box is named textBox1, add the following code: Private Sub TextBox1_Change() 'call the validate routine and pass to it the textbox object Validate_Number_Entry Me.TextBox1 End Sub In a regular module, put the following: Sub Validate_Number_Entry(oBox) Dim tempS As String 'store the text in a variable for later use tempS = oBox.Text 'if user has removed all entries, just exit If tempS = "" Then Exit Sub 'if the entry is numeric, then exit If IsNumeric(tempS) Then Exit Sub 'if the entry is not numeric, remove the last entry oBox.Text = Mid(tempS, 1, Len(tempS) - 1) End Sub Bob Flanagan Macro Systems -ins.com Productivity add-ins and downloadable books on VB macros for Excel "phil" <pperry@acsoft.co.uk wrote in message news:1166b01c21ebd$49fc29f0$36ef2ecf@tkmsftngxa12... Hi, how can i allow only numbers to be entered into a textbox on a user form? thanks phil |
| 1015 | excellent, thanks phil -----Original Message----- In the userform's code module, assuming that your text box is named textBox1, add the following code: Private Sub TextBox1_Change() 'call the validate routine and pass to it the textbox object Validate_Number_Entry Me.TextBox1 End Sub In a regular module, put the following: Sub Validate_Number_Entry(oBox) Dim tempS As String 'store the text in a variable for later use tempS = oBox.Text 'if user has removed all entries, just exit If tempS = "" Then Exit Sub 'if the entry is numeric, then exit If IsNumeric(tempS) Then Exit Sub 'if the entry is not numeric, remove the last entry oBox.Text = Mid(tempS, 1, Len(tempS) - 1) End Sub Bob Flanagan Macro Systems -ins.com Productivity add-ins and downloadable books on VB macros for Excel "phil" <pperry@acsoft.co.uk wrote in message news:1166b01c21ebd$49fc29f0$36ef2ecf@tkmsftngxa12... Hi, how can i allow only numbers to be entered into a textbox on a user form? thanks phil . |
| 1025 | Or maybe this? This will enter the date and the sheetname when a new worksheet has been created. Private Sub Workbook_NewSheet(ByVal Sh As Object) Sheet1.Activate ' a summary sheet of the PO's Range("A1").End(xlDown).Offset(1, 0).Activate ActiveCell.Value = Now ActiveCell.NumberFormat = "dddd, mmmm dd, yyyy" ActiveCell.Offset(0, 1).Value = Sh.Name End Sub Jon Crash wrote: Something like this maybe? Private Sub Workbook_NewSheet(ByVal Sh As Object) Range("A1").Activate ActiveCell.Value = Now ActiveCell.NumberFormat = "dddd, mmmm dd, yyyy" End Sub Jon dflannery wrote: What I am trying to do is create a page the keep track of purchase orders so there is many dates involed. Each PO is represented on a worksheet. I want a macro that will insert the date of when that current sheet was created, and will stay that way, it will not change to the current date everyday like the TODAY function does. does this help clear anything up? -----Original Message----- I, for one, am confused by *****I would like one that keeps that date****** and *****so 6/28/02 will be there everyday wich is not what I want. ****** ???? <ski_34_4@hotmail.com wrote in message news:135f501c21ea7$3abdbc80$3aef2ecf@TKMSFTNGXA09... I am looking for a function to insert into a macro that is similair to TODAY which enters todays date but I would like one that keeps that date. For example if I insert a date today 6/28/02 and I open the workbook tomorrow that date will still have 6/28/02 where I inserted it previously. I tried ctl+; which inserts the todays date but it enters it as a string so 6/28/02 will be there everyday wich is not what I want. Can anyone help me? Maybe there is a VBA statement that will allow this? . |
| 1036 | With a dynamic company list you might want to try the following. Copy in C1 and drag the formula down: =IF(COUNTIF($A$1:A1,A1)1,"",SUMIF(A:A,A1,B:B)) If the list of companies is stagnant (meaning new companies are never added) and you have a unique list of all of them, then you might simply want: =SUMIF(A:A,K1,B:B) where K1 hold the company name. HTH Jason Atlanta, GA -----Original Message----- I'm working on a project where I have a list of companies in column A and amounts for them in column B. These names of companies are repeated many times a day in many different orders, the amounts are different as well. So what I need is a way for excel to recognize the company names in colunm A and take the amounts in column B and total the amounts up for each company in column C. Any help would really be appreciated, thank you. . |
| 1038 | My apologies if this has been answered elsewhere; I can't seem to find it. When I create a pivot table by hand, dragging and dropping them into the data area via the wizard, frequently the fields become "Count of x" when what I want 99.9% of the time is "Sum of x." There must be a way to change the default behaviour, or so I hope. Thanks in advance |
| 1043 | Hi Claude, usually this is because Excel finds some cells that it thinks contain or might contain text. If all the cells are numeric the default is SUM. Charles Williams ______________________ Decision Models The Excel Calculation Site www.DecisionModels.com |
| 1047 | From Help SUMIF(range,criteria,sum_range) He meant that company name you are summing is in K1 and you put K1 in the formula "Joe" <jclark@clkinc.com wrote in message news:115b601c21ed4$124ef660$37ef2ecf@TKMSFTNGXA13... When you said that the list stagnant and K1 holds the company name, what did you mean. Do I put K1 in the formula or the company name. |
| 1054 | Hi Otto, I've read your postings and you don't have any need to "get smarter". Maybe I did not understand what the OP wants? I really wish they would all post back and say either "thanks, that works fine" or "hey, stupid, that's not what I wanted". I assumed the OP has a list of names they want removed from one or more worksheets and they want to save time with Excel clearing them, rather than searching and deleting them all manually. If I'm wrong, it won't be a first. Best Regards, "Otto Moehrbach" <ottom@worldnet.att.net wrote in message news:u$3fEUtHCHA.2480@tkmsftngp11... Wilson I need to get smarter to figure out how this works. It appears that your code clears the contents of every cell whose name is in the names range. I don't see how your code deletes the names from the workbook. Thanks for your help. Otto "Wilson" <jwilson@wickes.com wrote in message news:uOBIb7rHCHA.1696@tkmsftngp09... With the names to get rid of llisted on another sheet Sub NamesCleaner() Dim Nam As Variant Dim NamRng As Range Dim cell As Range Set NamRng = Worksheets("Name Sheet").Range("A2:A5") For Each Nam In NamRng For Each cell In ActiveSheet.UsedRange If cell = Nam Then cell.ClearContents End If Next cell Next Nam End Sub HTH "B. Teele" <bteele@pranainvestments.com wrote in message news:1174701c21ebd$14d4d560$a4e62ecf@tkmsftngxa06... Is there a way get rid of numerous names in our worksheets (other than by deleting them one by one)? |
| 1059 | Apesbrain Here is something to get you started. I used two sheets named FirstSheet and SecondSheet. The short (or "fixed") list is in the SecondSheet, Column A, starting in cell A1. The longer (or"variable") list is in the FirstSheet, Column A, starting in cell A1. This macro searches the longer list for each item in the shorter list and deletes the entire row, in the longer list, when it finds it. Error trapping is included in the code in the event that an item in the short list cannot be found in the long list. Let me know how this works for you or if you need anything else. HTH Otto Sub PurgeList() Dim BigList As Range Dim SmallList As Range Dim i As Range Set BigList = Range("A1", Range("A" & Rows.Count). _ End(xlUp).Address) With Sheets("SecondSheet") Set SmallList = .Range("A1", .Range("A" & Rows.Count). _ End(xlUp).Address) End With For Each i In SmallList On Error Resume Next BigList.Find(What:=i, LookAt:=xlWhole).EntireRow.Delete On Error GoTo 0 Next End Sub "Apesbrain" <apesbrain@yahoo.com wrote in message news:55a2234c.0206271705.63b4b048@posting.google.com... I have a spreadsheet of store locations with about 2500 records. I need to merge/purge a second sheet containing a subset of 250 locations that are private facilities not open to the public. The list of 250 remains the same from month to month, but the 2500 master list changes. Is there an easy way to write a macro that would automatically purge the fixed 250 from the variable 2500 and leave me with the 2250 current public locations? The two lists are related through "locationID". Any ideas would be appreciated. Thanks. |
| 1079 | Maybe you can insert an adjacent column and do something like: =DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,4,2)) (assumes data is in A1. The copy|paste special values, format the way you want and delete the original column.) Shannon Jacobs wrote: Dave Peterson <ec35720@msn.com wrote in message news:<3D1B84BD.6AF1F513@msn.com... Maybe the dates/text are results of formulas. If you don't care about the formulas anymore, maybe select that column. Edit|Copy, then Edit|Paste special values. Then try Data|text to columns. Excel is pretty smart. If you still need the formulas, then don't do this. <snip No, the dates are just simple text strings. They were exported as CSVs and land in Excel looking like MM/DD/YYYY. Long history there, but I basically just want to get them to be YYYY/MM/DD or YYYYMMDD. Special paste is not helpful. The Text to columns might be helpful, except that there is no such animal on my Data menu, even by the wildest stretches of translation (from Japanese). Excel is quite willing to let me specify date formats for the column, but to no effect. -- Dave Peterson ec35720@msn.com |
| 1081 | Hi Wilson I interpreted the OPs question (a little unclear) differently to you. My interpretation is that there are names in the workbook that need to be deleted. Using your example of there being a list of names to be deleted on the current sheet, this would delete all names in the workbook within that list. But my interpretation may well be wrong. Dim nm As Name, r As Range, c As Range With ThisWorkbook Set r = Range("A1:A50") 'Amend as necessary For Each c In r Names(c.Text).Delete Next c End With -- Regards William willwest22@yahoo.com "Wilson" <jwilson@wickes.com wrote in message news:#iOD5jtHCHA.2480@tkmsftngp11... | Hi Otto, | I've read your postings and you don't have any need to "get smarter". Maybe | I did not understand what the OP wants? I really wish they would all post | back and say either "thanks, that works fine" or "hey, stupid, that's not | what I wanted". I assumed the OP has a list of names they want removed from | one or more worksheets and they want to save time with Excel clearing them, | rather than searching and deleting them all manually. If I'm wrong, it | won't be a first. | Best Regards, | "Otto Moehrbach" <ottom@worldnet.att.net wrote in message | news:u$3fEUtHCHA.2480@tkmsftngp11... | Wilson | I need to get smarter to figure out how this works. It appears that | your code clears the contents of every cell whose name is in the names | range. I don't see how your code deletes the names from the workbook. | Thanks for your help. Otto | "Wilson" <jwilson@wickes.com wrote in message | news:uOBIb7rHCHA.1696@tkmsftngp09... | With the names to get rid of llisted on another sheet | Sub NamesCleaner() | Dim Nam As Variant | Dim NamRng As Range | Dim cell As Range | Set NamRng = Worksheets("Name Sheet").Range("A2:A5") | For Each Nam In NamRng | For Each cell In ActiveSheet.UsedRange | If cell = Nam Then | cell.ClearContents | End If | Next cell | Next Nam | End Sub | HTH | "B. Teele" <bteele@pranainvestments.com wrote in message | news:1174701c21ebd$14d4d560$a4e62ecf@tkmsftngxa06... | Is there a way get rid of numerous names in our worksheets | (other than by deleting them one by one)? | | | | | | |
| 1092 | Hi Matt Myrna Larson & Bill Manville has a free addin at /excel/download.htm called "Compare". It should do what you want. This method assumes that the sheets are similar. Insert one extra row in the middle of one of them and they suddenly become very very different. HTH. Best wishes Harald "Matt" <matt@slippednospampleasedisk.fsnet.co.uk wrote in message news:afjoak$fag$1@news6.svr.pol.co.uk... Hi All I have a workbook that contains similar worksheets of data. Is there an easy way of taking two worksheets, original and new and running a function that will compare the two, possibly giving the results in a new worksheet? I would like to have one result which will display new rows in the new sheet that do not appear in the original, and also another result that will show rows where data has changed. There must be an easier way than simply visually comparing each row one by one! I hope this explains what I want to do with these sheets. Any help is much appreciated. Many Thanks in Advance! Matt |
| 1096 | The simplest solution might be to just hide the worksheet tabs. The worksheets will still be Visible, but I think most people won't know how to change sheets (ctrl-pageup/ctrl-pagedown will work!). Tools|options|view|sheet tabs will toggle the view. Another solution would be to use the worksheet_beforedoubleclick event. If you format the cell with a blue font and underline it, it might even pass for a hyperlink. Option Explicit Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _ Cancel As Boolean) Dim wks As Worksheet Dim myCell As Range If Target.Cells.Count 1 Then Exit Sub If Target.Value = "" Then Exit Sub If Target.Column < 5 Then Exit Sub Set wks = Nothing On Error Resume Next Set wks = Worksheets(Target.Value) On Error GoTo 0 If wks Is Nothing Then MsgBox "Worksheet for " & Target.Value & " doesn't exist!" Exit Sub End If wks.Visible = True Application.Goto wks.Range("a1"), scroll:=True End Sub If you like this idea, right click on the index sheet and select view code. Then paste this in. ======= I just tried this and it seemed to work ok. It maybe the simplest solution. It also goes into the worksheet module. (The parsing of the worksheet in this example means that the link has t point to the same workbook.) Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink) On Error GoTo errHandler Worksheets(Left(Target.Parent, InStr(1, Target.Parent, "!") - 1)).Visible = True Application.EnableEvents = False Target.Follow errHandler: Application.EnableEvents = True End Sub Phil wrote: I want to hide some worksheets within a workbook and use an index page to hyperlink to the sheets. I have tried it and it works as long as the worksheets are not hidden. But, if I hide the worksheets then the hyperlink doesn't work. I am sure I have seen people do this before but can't figure it out. So, my question is how do you make the hyperlinks work when the worksheets are hidden? Thanks, Phil -- Dave Peterson ec35720@msn.com |
| 1099 | One way, =MAX(E2-"7:30",0) assuming that E2 holds the total time, will return anything over 7:30 and zero for anything less than 7:30.. Note that if you are summing up hours you must use custom format [h]:mm or else it won't go further than 24:00 -- Regards, Peo Sjoblom "Deb" <clifford@git.com.au wrote in message news:edOOuD#HCHA.2456@tkmsftngp08... One other quick question if you don't mind ??? If I was to add another column on for number of hours up (flexi-time) how would I do that ? eg. start lunch resume end total hours worked Hours up 07:30 12:00 12:30 15:30 07:30 Total hours - standard day = hours up ??? standard day is 07:30hrs Thanks for your help Debbie "EarlK" <earlk@livenet.net wrote in message news:uhut219HCHA.2212@tkmsftngp12... Deb, 7 and one-half hours is the correct answer, actually. Since you've converted the time values to hours, it should be 7.5. It would be 7:30 in hh:mm format. If you really want it to show 7:30 as 7.3, leave it as a real time value (remove the *24 from your formula), and format it (Format-Cells-Custom) as: hh.mm [hh].mm -- Regards from Virginia Beach, EarlK ------------------------------------------------------------- "Deb" <clifford@git.com.au wrote in message news:uqAXdt9HCHA.2636@tkmsftngp09... Hi I am trying to create a basic timesheet with the following: Start Lunch Resume Finish Total worked hours I try to write the formula of (finish - start)-(Resume-Lunch)*24 This all looks fine but when I do the following I get a wrong total 07:30 12:00 12:30 15:30 should be 7.30 but mine comes out as 7.50 ???? Help please Thanks |
| 1100 | "Deb" <clifford@git.com.au wrote in message news:edOOuD#HCHA.2456@tkmsftngp08... One other quick question if you don't mind ??? If I was to add another column on for number of hours up (flexi-time) how would I do that ? eg. start lunch resume end total hours worked Hours up 07:30 12:00 12:30 15:30 07:30 Total hours - standard day = hours up ??? standard day is 07:30hrs Thanks for your help Debbie Be aware that negative times cannot be displayed as such. Say that a standard days has 7:30 hours and total hours worked would be 7:15 on a given day. That means -0:15 in the hours up column. In Excel it will be displayed as #### So, use the ABS function to get rid of the negative sign (-- ABS(-5) = 5) =ABS(hours-standard day) and an IF function in a next column to show a plus or minus sign =IF((hours-standard day)<0;"-";"+") Use the Paste Function button to be sure to get the seperators (semicolon in my example) right for your Excel version. Hope this helps, Robert |
| 1101 | Hi Phil On your index sheet, I presume you have a list of the worksheet names. Place a hyperlink in each cell with a worksheet name in it (as I think you have done already) but reference the hyperlink to that same cell with the sheet name. Then place this code in the Index WorksheetModule. Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink) Sheets(ActiveCell.Text).Visible = True Sheets(ActiveCell.Text).Activate End Sub -- Regards William REMOVEwillwest22@yahoo.com Remove "REMOVE" when replying "Phil" <rowejunk2@mchsi.com wrote in message news:AgrT8.361757$cQ3.23521@sccrnsc01... | I want to hide some worksheets within a workbook and use an index page to | hyperlink to the sheets. I have tried it and it works as long as the | worksheets are not hidden. But, if I hide the worksheets then the hyperlink | doesn't work. I am sure I have seen people do this before but can't figure | it out. So, my question is how do you make the hyperlinks work when the | worksheets are hidden? | | Thanks, | Phil | | |
| 1102 | Hi Phil On your index sheet, I presume you have a list of the worksheet names. Place a hyperlink in each cell with a worksheet name in it (as I think you have done already) but reference the hyperlink to that same cell with the sheet name. Then place this code in the Index WorksheetModule. Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink) Sheets(ActiveCell.Text).Visible = True Sheets(ActiveCell.Text).Activate End Sub -- Regards William willwest22@yahoo.com "Phil" <rowejunk2@mchsi.com wrote in message news:AgrT8.361757$cQ3.23521@sccrnsc01... | I want to hide some worksheets within a workbook and use an index page to | hyperlink to the sheets. I have tried it and it works as long as the | worksheets are not hidden. But, if I hide the worksheets then the hyperlink | doesn't work. I am sure I have seen people do this before but can't figure | it out. So, my question is how do you make the hyperlinks work when the | worksheets are hidden? | | Thanks, | Phil | | |
| 1105 | Thanks for the suggestions everyone. I really appreciate them. Phil "William" <REMOVEwillwest22@yahoo.com wrote in message news:Oy8Ve8$HCHA.1744@tkmsftngp13... Hi Phil On your index sheet, I presume you have a list of the worksheet names. Place a hyperlink in each cell with a worksheet name in it (as I think you have done already) but reference the hyperlink to that same cell with the sheet name. Then place this code in the Index WorksheetModule. Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink) Sheets(ActiveCell.Text).Visible = True Sheets(ActiveCell.Text).Activate End Sub -- Regards William willwest22@yahoo.com "Phil" <rowejunk2@mchsi.com wrote in message news:AgrT8.361757$cQ3.23521@sccrnsc01... | I want to hide some worksheets within a workbook and use an index page to | hyperlink to the sheets. I have tried it and it works as long as the | worksheets are not hidden. But, if I hide the worksheets then the hyperlink | doesn't work. I am sure I have seen people do this before but can't figure | it out. So, my question is how do you make the hyperlinks work when the | worksheets are hidden? | | Thanks, | Phil | | |
| 1110 | Robert Thanks for your help but I am still having problems (I am still learning excel : ( Currently I have the following: Hours Carried Forward: 01:00 Day Start Stop (lunch) Resume Finish Days Total (hrs) "Hours up or down (flexi-time)" Monday 07:30 12:00 12:30 15:30 07:30 01:00 =(E10-B10)-(D10-C10) =F10-E5+C8 (E5 being a cell with 07:30) At what point do I put the ABS ??? Thanks Deb "RobBlue" <robblue@wanadoo.nl wrote in message news:l%wT8.556$LX5.3809@castor.casema.net... "Deb" <clifford@git.com.au wrote in message news:edOOuD#HCHA.2456@tkmsftngp08... One other quick question if you don't mind ??? If I was to add another column on for number of hours up (flexi-time) how would I do that ? eg. start lunch resume end total hours worked Hours up 07:30 12:00 12:30 15:30 07:30 Total hours - standard day = hours up ??? standard day is 07:30hrs Thanks for your help Debbie Be aware that negative times cannot be displayed as such. Say that a standard days has 7:30 hours and total hours worked would be 7:15 on a given day. That means -0:15 in the hours up column. In Excel it will be displayed as #### So, use the ABS function to get rid of the negative sign (-- ABS(-5) = 5) =ABS(hours-standard day) and an IF function in a next column to show a plus or minus sign =IF((hours-standard day)<0;"-";"+") Use the Paste Function button to be sure to get the seperators (semicolon in my example) right for your Excel version. Hope this helps, Robert |
| 1111 | Just a note for Excel XP... DeriveMonthName = MonthName(6, True) For June, and where 'True" is for abbreviated name -- Dana DeLouis Windows XP & Office XP = = = = = = = = = = = = = = = = = "" <DavidH@OzGrid.com wrote in message news:5qQS8.20$0O6.40826@vicpull1.telstra.net... Hi Bill Just an added bit to Johns suggestion, look into using Sheet CodeNames and consider using: DeriveMonthName = Format(Dateserial(2002,intMonth,1),"mmm") To replace the Select Case Statement, will make you life a lot easier. I would also suggest naming the cell C2 and simply refer to it by the name chosen name. If Isdate(Range("DateCell")) then DeriveMonthName = Format(Dateserial(2002,Range("DateCell"),1),"mmm") End if I'm thinking you can shorten you whole code to a few lines only. -- FREE EXCEL NEWSLETTER /News/2home.htm "Bill" <wapfu@xtra.co.nz wrote in message news:1134101c21e2c$39a7ae80$36ef2ecf@tkmsftngxa12... | I can export/import to excel the following data from an | access query. | Sum Of Value Count Of MarketSales Expr1 | $49.00 2 6 | | Market SumOfNZD Expr1 | Australia 14456.75 6 | Europe 9500.00 6 | Indian Sub Cont. 1527.14 6 | New Zealand 76848.53 6 | North America 80000.50 6 | S.E. Asia 502.60 6 | South America 116628.57 6 | | Where 6 is month integer. | I Have the following code to convert the month integer to | alpha. | Function DeriveMonthName(intMonth As Integer) As String | | IntMonth = Worksheets | ("MarketSalesInquiryInstantInvoice").Cell(C2) | | Select Case intMonth | Case 1 | DeriveMonthName = "Jan" | Case 2 | DeriveMonthName = "Feb" | Case 3 | DeriveMonthName = "Mar" | Case 4 | DeriveMonthName = "Apr" | Case 5 | DeriveMonthName = "May" | Case 6 | DeriveMonthName = "Jun" | Case 7 | DeriveMonthName = "July" | Case 8 | DeriveMonthName = "Aug" | Case 9 | DeriveMonthName = "Sept" | Case 10 | DeriveMonthName = "Oct" | Case 11 | DeriveMonthName = "Nov" | Case 12 | DeriveMonthName = "Dec" | Case Else | DeriveMonthName = "???" | End Select | End Function | | And I want to use the following code to transfer data | Sub InsertDataMainInstant() | 'Check the value of the Month number in Cell C2 of the | sheet | '("MarketSalesInquiryInstantInvoiceSumQuery") and | Convert to Alpha | 'Using Function DeriveMonthName. | 'Dependent on the output, place the value of Cell A2 | 'from sheet MarketSumOfValueInstantInvoice Cell A2 | 'To The correct Cell in Sheet REDBOOK 2002-2003 | | Procedure = DeriveMonthName | | | Select Case Worksheets | ("MarketSalesInquiryInstantInvoice").Cell(C2) | | Case Is = "Jan" | Worksheets("REDBOOK 2002-2003").Cell(B5) = | Worksheets("MarketSalesInquiryInstantInvoice").Cell(A2) | | Case Is = "Feb" | Worksheets("REDBOOK 2002-2003").Cell(B6) = | Worksheets("MarketSalesInquiryInstantInvoice").Cell(A2) | Case Is = "Mar" | Worksheets("REDBOOK 2002-2003").Cell(B7) = | Worksheets("MarketSalesInquiryInstantInvoice").Cell(A2) | Case Is = "Apr" | Worksheets("REDBOOK 2002-2003").Cell(B8) = | Worksheets("MarketSalesInquiryInstantInvoice").Cell(A2) | Case Is = "May" | Worksheets("REDBOOK 2002-2003").Cell(B9) = | Worksheets("MarketSalesInquiryInstantInvoice").Cell(A2) | Case Is = "Jun" | Worksheets("REDBOOK 2002-2003").Cell(B10) = | Worksheets("MarketSalesInquiryInstantInvoice").Cell(A2) | Case Is = "July" | Worksheets("REDBOOK 2002-2003").Cell(B11) = | Worksheets("MarketSalesInquiryInstantInvoice").Cell(A2) | Case Is = "Aug" | Worksheets("REDBOOK 2002-2003").Cell(B12) = | Worksheets("MarketSalesInquiryInstantInvoice").Cell(A2) | Case Is = "Sept" | Worksheets("REDBOOK 2002-2003").Cell(B13) = | Worksheets("MarketSalesInquiryInstantInvoice").Cell(A2) | Case Is = "Oct" | Worksheets("REDBOOK 2002-2003").Cell(B14) = | Worksheets("MarketSalesInquiryInstantInvoice").Cell(A2) | Case Is = "Nov" | Worksheets("REDBOOK 2002-2003").Cell(B15) = | Worksheets("MarketSalesInquiryInstantInvoice").Cell(A2) | Case Is = "Dec" | Worksheets("REDBOOK 2002-2003").Cell(B16) = | Worksheets("MarketSalesInquiryInstantInvoice").Cell(A2) | | End Select | End Sub | But I can't get it to work, Please help. | |
| 1121 | Hi Deb, I don't see any use for ABS in your time sheet . If the later time looks like an earlier time you add one day to the later time. Time is stored as fractions of a day so adding 24 hours is same as adding 1 with a logical test (D10E10) =(E10-B10)-(D10-C10) 'what a convoluted formula change to =E10-D10+(D10E10) + C10 - B10 + (B10 C10) If you want to convert an Excel time to hours for calculation with an hourly rate of pay then multiply the Excel time by 24. more information in and may even match your data Date & Time /dmcritchie/excel/datetime.htm --- HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel Macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Deb" <clifford@git.com.au wrote in message news:u$Cn2aDICHA.2404@tkmsftngp08... Robert Thanks for your help but I am still having problems (I am still learning excel : ( Currently I have the following: Hours Carried Forward: 01:00 Day Start Stop (lunch) Resume Finish Days Total (hrs) "Hours up or down (flexi-time)" Monday 07:30 12:00 12:30 15:30 07:30 01:00 =(E10-B10)-(D10-C10) =F10-E5+C8 (E5 being a cell with 07:30) At what point do I put the ABS ??? Thanks Deb "RobBlue" <robblue@wanadoo.nl wrote in message news:l%wT8.556$LX5.3809@castor.casema.net... "Deb" <clifford@git.com.au wrote in message news:edOOuD#HCHA.2456@tkmsftngp08... One other quick question if you don't mind ??? If I was to add another column on for number of hours up (flexi-time) how would I do that ? eg. start lunch resume end total hours worked Hours up 07:30 12:00 12:30 15:30 07:30 Total hours - standard day = hours up ??? standard day is 07:30hrs Thanks for your help Debbie Be aware that negative times cannot be displayed as such. Say that a standard days has 7:30 hours and total hours worked would be 7:15 on a given day. That means -0:15 in the hours up column. In Excel it will be displayed as #### So, use the ABS function to get rid of the negative sign (-- ABS(-5) = 5) =ABS(hours-standard day) and an IF function in a next column to show a plus or minus sign =IF((hours-standard day)<0;"-";"+") Use the Paste Function button to be sure to get the seperators (semicolon in my example) right for your Excel version. Hope this helps, Robert |
| 1125 | Wilson I still need to get smarter. Your interpretation of the post is the same as mine, namely that he has some names in his workbook that he wants to get rid of (deleted) and wants a macro to do it for him. My problem with your macro is that it appears that it deletes the contents of all the named cells rather than the names themselves. I've about convinced myself that your macro does it right, but I don't know how. That's where the "getting smarter" part comes in. Otto "Wilson" <jwilson@wickes.com wrote in message news:#iOD5jtHCHA.2480@tkmsftngp11... Hi Otto, I've read your postings and you don't have any need to "get smarter". Maybe I did not understand what the OP wants? I really wish they would all post back and say either "thanks, that works fine" or "hey, stupid, that's not what I wanted". I assumed the OP has a list of names they want removed from one or more worksheets and they want to save time with Excel clearing them, rather than searching and deleting them all manually. If I'm wrong, it won't be a first. Best Regards, "Otto Moehrbach" <ottom@worldnet.att.net wrote in message news:u$3fEUtHCHA.2480@tkmsftngp11... Wilson I need to get smarter to figure out how this works. It appears that your code clears the contents of every cell whose name is in the names range. I don't see how your code deletes the names from the workbook. Thanks for your help. Otto "Wilson" <jwilson@wickes.com wrote in message news:uOBIb7rHCHA.1696@tkmsftngp09... With the names to get rid of llisted on another sheet Sub NamesCleaner() Dim Nam As Variant Dim NamRng As Range Dim cell As Range Set NamRng = Worksheets("Name Sheet").Range("A2:A5") For Each Nam In NamRng For Each cell In ActiveSheet.UsedRange If cell = Nam Then cell.ClearContents End If Next cell Next Nam End Sub HTH "B. Teele" <bteele@pranainvestments.com wrote in message news:1174701c21ebd$14d4d560$a4e62ecf@tkmsftngxa06... Is there a way get rid of numerous names in our worksheets (other than by deleting them one by one)? |
| 1126 | If you want a counter for flex time I'd suggest you use the Mac date system, toolsoptionscalculation and check 1904 date system, assume that your start time begins in A2 and finish time in D2, in E2 (total time per day) put =D2-C2+(C2D2)+B2-A2+(A2B2) in F2 (flexi time) put =IF(COUNT(A2:D2)=4,E2-"7:30"+IF(ISNUMBER(F1),F1,0),0) Now you can copy down the formula If you don't want to use the 1904 date system (although I can't see why) then you have to use decimal hours and multiply the formula by 24 =(IF(COUNT(A2:D2)=4,E2-"7:30"+IF(ISNUMBER(F1),F1,0),0))*24 Example emailed as well Note that when you change to 1904 system all dates will have 4 years and one day added. -- Regards, Peo Sjoblom "Deb" <clifford@git.com.au wrote in message news:u$Cn2aDICHA.2404@tkmsftngp08... Robert Thanks for your help but I am still having problems (I am still learning excel : ( Currently I have the following: Hours Carried Forward: 01:00 Day Start Stop (lunch) Resume Finish Days Total (hrs) "Hours up or down (flexi-time)" Monday 07:30 12:00 12:30 15:30 07:30 01:00 =(E10-B10)-(D10-C10) =F10-E5+C8 (E5 being a cell with 07:30) At what point do I put the ABS ??? Thanks Deb "RobBlue" <robblue@wanadoo.nl wrote in message news:l%wT8.556$LX5.3809@castor.casema.net... "Deb" <clifford@git.com.au wrote in message news:edOOuD#HCHA.2456@tkmsftngp08... One other quick question if you don't mind ??? If I was to add another column on for number of hours up (flexi-time) how would I do that ? eg. start lunch resume end total hours worked Hours up 07:30 12:00 12:30 15:30 07:30 Total hours - standard day = hours up ??? standard day is 07:30hrs Thanks for your help Debbie Be aware that negative times cannot be displayed as such. Say that a standard days has 7:30 hours and total hours worked would be 7:15 on a given day. That means -0:15 in the hours up column. In Excel it will be displayed as #### So, use the ABS function to get rid of the negative sign (-- ABS(-5) = 5) =ABS(hours-standard day) and an IF function in a next column to show a plus or minus sign =IF((hours-standard day)<0;"-";"+") Use the Paste Function button to be sure to get the seperators (semicolon in my example) right for your Excel version. Hope this helps, Robert |
| 1132 | I have two sheets, sheet A is detailed information, sheet B is a summary sheet with the last 12 pieces of info from sheet A. This is effected by using dynamic ranges to pick up the last 12 rows ie the range address for the last row, called surprisingly 'LastRecord', = 'OFFSET('Detailed History'!$B$1,COUNTA('Detailed History'!$B:$B)-1,0,1,COUNTA('Detailed History'!$1:$1))' This is then picked up in the summary sheet by using the formula =IF(ISERR(LastRecordMinus1),0,LastRecord). This works fine when I have these two base sheets. How can I alter this so that when I copy the two sheets the summary refers to the correct 'Detailed History' ? Robert PS Sorry this was misposted in programming as well ! |
| 1133 | Hi Robert, Group the two sheets. Select one sheet, Hold Ctrl key and select the second. Copy your sheets (move or copy sheets) as before. DO NOT FORGET TO UNGROUP YOUR SHEETS. Failure to ungroup sheets will result in serious damage as any change made to a cell in one will change the other sheets as well as if also entered into the same cell. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel Macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Robert Brown" <robert@nospam-southall-brown.co.uk wrote in message news:e8AH5mFICHA.2464@tkmsftngp12... I have two sheets, sheet A is detailed information, sheet B is a summary sheet with the last 12 pieces of info from sheet A. This is effected by using dynamic ranges to pick up the last 12 rows ie the range address for the last row, called surprisingly 'LastRecord', = 'OFFSET('Detailed History'!$B$1,COUNTA('Detailed History'!$B:$B)-1,0,1,COUNTA('Detailed History'!$1:$1))' This is then picked up in the summary sheet by using the formula =IF(ISERR(LastRecordMinus1),0,LastRecord). This works fine when I have these two base sheets. How can I alter this so that when I copy the two sheets the summary refers to the correct 'Detailed History' ? Robert PS Sorry this was misposted in programming as well ! |
| 1134 | Thanks David, however, the new summary sheet, literally named 'Summary(2)' is still referring to the data on the original 'Detailed History' and not 'Detailed History(2)'. I presume that this is due to the original range being a workbook level name (as it was the first one created) and the second range is a worksheet level name. What I am wondering is if I can change the range definition so that instead of reading 'Detailed History'!$B$1 it says something like Sheets(-1)!$B$1 (as the Detailed History sheet is always the one before the Summary sheet - structure is protected and code acts as a backup) Any thoughts ? Robert "" <dmcritchie@msn.com wrote in message news:ecy7V0FICHA.2240@tkmsftngp08... Hi Robert, Group the two sheets. Select one sheet, Hold Ctrl key and select the second. Copy your sheets (move or copy sheets) as before. DO NOT FORGET TO UNGROUP YOUR SHEETS. Failure to ungroup sheets will result in serious damage as any change made to a cell in one will change the other sheets as well as if also entered into the same cell. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel Macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Robert Brown" <robert@nospam-southall-brown.co.uk wrote in message news:e8AH5mFICHA.2464@tkmsftngp12... I have two sheets, sheet A is detailed information, sheet B is a summary sheet with the last 12 pieces of info from sheet A. This is effected by using dynamic ranges to pick up the last 12 rows ie the range address for the last row, called surprisingly 'LastRecord', = 'OFFSET('Detailed History'!$B$1,COUNTA('Detailed History'!$B:$B)-1,0,1,COUNTA('Detailed History'!$1:$1))' This is then picked up in the summary sheet by using the formula =IF(ISERR(LastRecordMinus1),0,LastRecord). This works fine when I have these two base sheets. How can I alter this so that when I copy the two sheets the summary refers to the correct 'Detailed History' ? Robert PS Sorry this was misposted in programming as well ! |
| 1137 | Are these two sheets self contained with each other or are other sheets involved. I've done similar things in the past, copying a worksheet & graph together to make a new set. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel Macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Robert Brown" <robert@nospam-southall-brown.co.uk wrote in message news:ufA$88FICHA.1772@tkmsftngp09... Thanks David, however, the new summary sheet, literally named 'Summary(2)' is still referring to the data on the original 'Detailed History' and not 'Detailed History(2)'. I presume that this is due to the original range being a workbook level name (as it was the first one created) and the second range is a worksheet level name. What I am wondering is if I can change the range definition so that instead of reading 'Detailed History'!$B$1 it says something like Sheets(-1)!$B$1 (as the Detailed History sheet is always the one before the Summary sheet - structure is protected and code acts as a backup) Any thoughts ? Robert "" <dmcritchie@msn.com wrote in message news:ecy7V0FICHA.2240@tkmsftngp08... Hi Robert, Group the two sheets. Select one sheet, Hold Ctrl key and select the second. Copy your sheets (move or copy sheets) as before. DO NOT FORGET TO UNGROUP YOUR SHEETS. Failure to ungroup sheets will result in serious damage as any change made to a cell in one will change the other sheets as well as if also entered into the same cell. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel Macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Robert Brown" <robert@nospam-southall-brown.co.uk wrote in message news:e8AH5mFICHA.2464@tkmsftngp12... I have two sheets, sheet A is detailed information, sheet B is a summary sheet with the last 12 pieces of info from sheet A. This is effected by using dynamic ranges to pick up the last 12 rows ie the range address for the last row, called surprisingly 'LastRecord', = 'OFFSET('Detailed History'!$B$1,COUNTA('Detailed History'!$B:$B)-1,0,1,COUNTA('Detailed History'!$1:$1))' This is then picked up in the summary sheet by using the formula =IF(ISERR(LastRecordMinus1),0,LastRecord). This works fine when I have these two base sheets. How can I alter this so that when I copy the two sheets the summary refers to the correct 'Detailed History' ? Robert PS Sorry this was misposted in programming as well ! |
| 1138 | There's no built-in relative sheet addressing. You would have to accomplish this a VBA macro that copies the sheet, then changes the name definition and/or formulas. On Sun, 30 Jun 2002 18:56:24 +0100, "Robert Brown" <robert@nospam-southall-brown.co.uk wrote: Thanks David, however, the new summary sheet, literally named 'Summary(2)' is still referring to the data on the original 'Detailed History' and not 'Detailed History(2)'. I presume that this is due to the original range being a workbook level name (as it was the first one created) and the second range is a worksheet level name. What I am wondering is if I can change the range definition so that instead of reading 'Detailed History'!$B$1 it says something like Sheets(-1)!$B$1 (as the Detailed History sheet is always the one before the Summary sheet - structure is protected and code acts as a backup) Any thoughts ? Robert "" <dmcritchie@msn.com wrote in message news:ecy7V0FICHA.2240@tkmsftngp08... Hi Robert, Group the two sheets. Select one sheet, Hold Ctrl key and select the second. Copy your sheets (move or copy sheets) as before. DO NOT FORGET TO UNGROUP YOUR SHEETS. Failure to ungroup sheets will result in serious damage as any change made to a cell in one will change the other sheets as well as if also entered into the same cell. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel Macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Robert Brown" <robert@nospam-southall-brown.co.uk wrote in message news:e8AH5mFICHA.2464@tkmsftngp12... I have two sheets, sheet A is detailed information, sheet B is a summary sheet with the last 12 pieces of info from sheet A. This is effected by using dynamic ranges to pick up the last 12 rows ie the range address for the last row, called surprisingly 'LastRecord', = 'OFFSET('Detailed History'!$B$1,COUNTA('Detailed History'!$B:$B)-1,0,1,COUNTA('Detailed History'!$1:$1))' This is then picked up in the summary sheet by using the formula =IF(ISERR(LastRecordMinus1),0,LastRecord). This works fine when I have these two base sheets. How can I alter this so that when I copy the two sheets the summary refers to the correct 'Detailed History' ? Robert PS Sorry this was misposted in programming as well ! |
| 1142 | Dave's routine is vulnerable to lock-up any time Amount is = the number of single digits in the range Bottom to Top and Top-Bottom is =10. Of course, it's unusual for any lotto to have more than 9 picks, so this would happen rarely in Lotto picking. However the routine below generalizes this a bit and returns up to Bottom-Top + 1 (i.e., all) numbers: Public Function Rands(Bottom As Integer, Top As Integer, _ Amount As Integer) As String Dim iArr As Variant Dim i As Integer Dim r As Integer Dim temp As Integer Application.Volatile ReDim iArr(Bottom To Top) For i = Bottom To Top iArr(i) = i Next i For i = Top To Bottom + 1 Step -1 r = Int(Rnd() * (i - Bottom + 1)) + Bottom temp = iArr(r) iArr(r) = iArr(i) iArr(i) = temp Next i For i = Bottom To Bottom + Amount - 1 Rands = Rands & " " & iArr(i) Next i Rands = Trim(Rands) End Function In article <uwNIHS7HCHA.2012@tkmsftngp13, Sandy Mann <sandymann@tiscali.co.uk wrote: Dave, I am not trying to be a smart-ass here - I just trying to learn good programming techniques through these NG's - and I know that your function is just a bit of fun but it seems, to me at least, to have a fatal flaw when the number of random numbers approaches the total of numbers available. For example with numbers 1 to 20, when you ask for 16 random numbers, (i.e. *Amount*)' Excel seem to freeze. I assume that the reason is that it ends up going through an endless loop. I think what is happening is that when 12 is selected then when either 1 or 2 is selected, the InStr function will find then in the number 12 ans so will reject them. (i.e. Running the function numerous times I noticed that if 12 is selected early then neither 1 nor 2 will be but if 1 and 2 are selected then 12 can still appear in the list.) It must be that the odds are that the numbers 16 through 20 have already been selected and so when 1, 2, 6, 7, 8 or 9 are selected, the InStr function will find that number and so will continue the loop without end because it still requires another number. At least when I added a counting variable to see how many times the loop had executed I got a message box at 20,000,000 (I didn't have the patients to wait any longer.) Of course I could be wrong about the above but I would value your comments to further my knowledge of Excel & VBA Sandy "" <DavidH@OzGrid.com wrote in message news:imUS8.40$0O6.53272@vicpull1.telstra.net... Hi Wayne I have a fun little Function that you could use: Click the link below for full easy instructions if are unsure of how to use in the Worksheet. /VBA/RandomNumbers.htm Function RandLotto(Bottom As Integer, Top As Integer, Amount As Integer) Dim iNum As String Dim strNum As String Dim i As Integer Application.Volatile iNum = Int((Top - Bottom + 1) * Rnd + Bottom) For i = 1 To Amount strNum = Trim(strNum & " " & iNum) Do Until InStr(1, strNum, iNum) = 0 iNum = Int((Top - Bottom + 1) * Rnd + Bottom) Loop Next i RandLotto = strNum End Function -- FREE EXCEL NEWSLETTER /News/2home.htm "Wayne Robinson" <waynerobinson@bigpond.com wrote in message news:M9US8.22710$Hj3.70951@newsfeeds.bigpond.com... | I am trying to use the rand function to generate several random numbers. | That's fine. But how do I make sure that no two numbers are ever the same??? | | Any help would be greatly appreciated! | | |
| 1147 | Hi J.E. Just for fun, and to add to your excellent idea. I believe that one needs different techniques based on different situations. If one needed to pick 999 out of 1,000 numbers, then doing a search on previous numbers would take a long time. A loop would take a long time as it tried to pick a number that was not already used. Loading the 1,000 numbers, and shuffling would probably be the best way. On the other hand, if you had to pick 100 numbers out of 1,000,000, then building a large array, and then sorting that array, would not be the best idea either. I like to use the following general technique. It handles the problem of a large pool to choose from. (not building a large array to shuffle). It is also pretty fast for "Lotto" size problems. Although it has a programming loop :( ,most of the "hard" looping is done by Excel as it checks previous entries. As a side note...I don't believe a collection can be used as a 1 dimensional array when finished. (example: Join / Split). That is why the last "for-loop." I just hate that last loop! <vbg ..and I have tried everything! I removed some code, and removed error checking... (16 numbers between 1 & 20) Debug.Print PickNumbers(16, 1, 20) 8,13,15,4,2,5,20,14,19,6,16,12,3,18,10,1 Function PickNumbers(N As Long, Low As Long, High As Long) As String '// By: Dana DeLouis Dim grp As New Collection Dim v As Variant Dim j As Long Dim s As String On Error Resume Next Do While grp.Count < N s = CStr(Int(Rnd * (High - Low + 1)) + Low) grp.Add s, s Loop '// Basically done. Do what you want here... ReDim v(1 To grp.Count) For j = 1 To grp.Count: v(j) = grp(j): Next PickNumbers = Join(v, ",") End Function -- Dana DeLouis Windows XP & Office XP = = = = = = = = = = = = = = = = = "J.E. McGimpsey" <jemcgimpsey@mvps.org wrote in message news:300620021308012025%jemcgimpsey@mvps.org... Dave's routine is vulnerable to lock-up any time Amount is = the number of single digits in the range Bottom to Top and Top-Bottom is =10. Of course, it's unusual for any lotto to have more than 9 picks, so this would happen rarely in Lotto picking. However the routine below generalizes this a bit and returns up to Bottom-Top + 1 (i.e., all) numbers: Public Function Rands(Bottom As Integer, Top As Integer, _ Amount As Integer) As String Dim iArr As Variant Dim i As Integer Dim r As Integer Dim temp As Integer Application.Volatile ReDim iArr(Bottom To Top) For i = Bottom To Top iArr(i) = i Next i For i = Top To Bottom + 1 Step -1 r = Int(Rnd() * (i - Bottom + 1)) + Bottom temp = iArr(r) iArr(r) = iArr(i) iArr(i) = temp Next i For i = Bottom To Bottom + Amount - 1 Rands = Rands & " " & iArr(i) Next i Rands = Trim(Rands) End Function In article <uwNIHS7HCHA.2012@tkmsftngp13, Sandy Mann <sandymann@tiscali.co.uk wrote: Dave, I am not trying to be a smart-ass here - I just trying to learn good programming techniques through these NG's - and I know that your function is just a bit of fun but it seems, to me at least, to have a fatal flaw when the number of random numbers approaches the total of numbers available. For example with numbers 1 to 20, when you ask for 16 random numbers, (i.e. *Amount*)' Excel seem to freeze. I assume that the reason is that it ends up going through an endless loop. I think what is happening is that when 12 is selected then when either 1 or 2 is selected, the InStr function will find then in the number 12 ans so will reject them. (i.e. Running the function numerous times I noticed that if 12 is selected early then neither 1 nor 2 will be but if 1 and 2 are selected then 12 can still appear in the list.) It must be that the odds are that the numbers 16 through 20 have already been selected and so when 1, 2, 6, 7, 8 or 9 are selected, the InStr function will find that number and so will continue the loop without end because it still requires another number. At least when I added a counting variable to see how many times the loop had executed I got a message box at 20,000,000 (I didn't have the patients to wait any longer.) Of course I could be wrong about the above but I would value your comments to further my knowledge of Excel & VBA Sandy "" <DavidH@OzGrid.com wrote in message news:imUS8.40$0O6.53272@vicpull1.telstra.net... Hi Wayne I have a fun little Function that you could use: Click the link below for full easy instructions if are unsure of how to use in the Worksheet. /VBA/RandomNumbers.htm Function RandLotto(Bottom As Integer, Top As Integer, Amount As Integer) Dim iNum As String Dim strNum As String Dim i As Integer Application.Volatile iNum = Int((Top - Bottom + 1) * Rnd + Bottom) For i = 1 To Amount strNum = Trim(strNum & " " & iNum) Do Until InStr(1, strNum, iNum) = 0 iNum = Int((Top - Bottom + 1) * Rnd + Bottom) Loop Next i RandLotto = strNum End Function -- FREE EXCEL NEWSLETTER /News/2home.htm "Wayne Robinson" <waynerobinson@bigpond.com wrote in message news:M9US8.22710$Hj3.70951@newsfeeds.bigpond.com... | I am trying to use the rand function to generate several random numbers. | That's fine. But how do I make sure that no two numbers are ever the same??? | | Any help would be greatly appreciated! | | |
| 1176 | Hi Sandy I am glad you bought this up as I wrote the code about a year ago and it was for a user wanting non-repeating random numbers between 1 and 10. I hade forgotten about this :o) I will revisit this and see if I can loosen it up somewhat, starting with Jims suggestion. -- \ Get 8 Add-ins in one! For less than the price of 1 /Services/AddinExamples.htm FREE EXCEL NEWSLETTER /News/2home.htm "Sandy Mann" <sandymann@tiscali.co.uk wrote in message news:uwNIHS7HCHA.2012@tkmsftngp13... | Dave, | | I am not trying to be a smart-ass here - I just trying to learn good | programming techniques through these NG's - and I know that your function is | just a bit of fun but it seems, to me at least, to have a fatal flaw when | the number of random numbers approaches the total of numbers available. | | For example with numbers 1 to 20, when you ask for 16 random numbers, (i.e. | *Amount*)' Excel seem to freeze. I assume that the reason is that it ends | up going through an endless loop. I think what is happening is that when 12 | is selected then when either 1 or 2 is selected, the InStr function will | find then in the number 12 ans so will reject them. (i.e. Running the | function numerous times I noticed that if 12 is selected early then neither | 1 nor 2 will be but if 1 and 2 are selected then 12 can still appear in the | list.) It must be that the odds are that the numbers 16 through 20 have | already been selected and so when 1, 2, 6, 7, 8 or 9 are selected, the InStr | function will find that number and so will continue the loop without end | because it still requires another number. At least when I added a counting | variable to see how many times the loop had executed I got a message box at | 20,000,000 (I didn't have the patients to wait any longer.) | | Of course I could be wrong about the above but I would value your comments | to further my knowledge of Excel & VBA | | | | Sandy | | "" <DavidH@OzGrid.com wrote in message | news:imUS8.40$0O6.53272@vicpull1.telstra.net... | Hi Wayne | | I have a fun little Function that you could use: Click the link below for | full easy instructions if are unsure of how to use in the Worksheet. | /VBA/RandomNumbers.htm | | Function RandLotto(Bottom As Integer, Top As Integer, Amount As Integer) | Dim iNum As String | Dim strNum As String | Dim i As Integer | | Application.Volatile | iNum = Int((Top - Bottom + 1) * Rnd + Bottom) | | For i = 1 To Amount | strNum = Trim(strNum & " " & iNum) | Do Until InStr(1, strNum, iNum) = 0 | iNum = Int((Top - Bottom + 1) * Rnd + Bottom) | Loop | Next i | | RandLotto = strNum | | End Function | | | | -- | | | | | FREE EXCEL NEWSLETTER | /News/2home.htm | | | | "Wayne Robinson" <waynerobinson@bigpond.com wrote in message | news:M9US8.22710$Hj3.70951@newsfeeds.bigpond.com... | | I am trying to use the rand function to generate several random numbers. | | That's fine. But how do I make sure that no two numbers are ever the | same??? | | | | Any help would be greatly appreciated! | | | | | | | | |
| 1177 | Hi Jim You are right, the original Function was written to work on numbers between 1 and 10 and I hade forgotten that. I have updated this code on my site with a thank you to you for modifying it: /VBA/RandomNumbers.htm -- \ Get 8 Add-ins in one! For less than the price of 1 /Services/AddinExamples.htm FREE EXCEL NEWSLETTER /News/2home.htm "J.E. McGimpsey" <jemcgimpsey@mvps.org wrote in message news:300620021308012025%jemcgimpsey@mvps.org... | Dave's routine is vulnerable to lock-up any time Amount is = the | number of single digits in the range Bottom to Top and Top-Bottom is | =10. Of course, it's unusual for any lotto to have more than 9 picks, | so this would happen rarely in Lotto picking. However the routine below | generalizes this a bit and returns up to Bottom-Top + 1 (i.e., all) | numbers: | | Public Function Rands(Bottom As Integer, Top As Integer, _ | Amount As Integer) As String | Dim iArr As Variant | Dim i As Integer | Dim r As Integer | Dim temp As Integer | | Application.Volatile | | ReDim iArr(Bottom To Top) | For i = Bottom To Top | iArr(i) = i | Next i | For i = Top To Bottom + 1 Step -1 | r = Int(Rnd() * (i - Bottom + 1)) + Bottom | temp = iArr(r) | iArr(r) = iArr(i) | iArr(i) = temp | Next i | For i = Bottom To Bottom + Amount - 1 | Rands = Rands & " " & iArr(i) | Next i | Rands = Trim(Rands) | End Function | | | | In article <uwNIHS7HCHA.2012@tkmsftngp13, Sandy Mann | <sandymann@tiscali.co.uk wrote: | | Dave, | | I am not trying to be a smart-ass here - I just trying to learn good | programming techniques through these NG's - and I know that your function is | just a bit of fun but it seems, to me at least, to have a fatal flaw when | the number of random numbers approaches the total of numbers available. | | For example with numbers 1 to 20, when you ask for 16 random numbers, (i.e. | *Amount*)' Excel seem to freeze. I assume that the reason is that it ends | up going through an endless loop. I think what is happening is that when 12 | is selected then when either 1 or 2 is selected, the InStr function will | find then in the number 12 ans so will reject them. (i.e. Running the | function numerous times I noticed that if 12 is selected early then neither | 1 nor 2 will be but if 1 and 2 are selected then 12 can still appear in the | list.) It must be that the odds are that the numbers 16 through 20 have | already been selected and so when 1, 2, 6, 7, 8 or 9 are selected, the InStr | function will find that number and so will continue the loop without end | because it still requires another number. At least when I added a counting | variable to see how many times the loop had executed I got a message box at | 20,000,000 (I didn't have the patients to wait any longer.) | | Of course I could be wrong about the above but I would value your comments | to further my knowledge of Excel & VBA | | | | Sandy | | "" <DavidH@OzGrid.com wrote in message | news:imUS8.40$0O6.53272@vicpull1.telstra.net... | Hi Wayne | | I have a fun little Function that you could use: Click the link below for | full easy instructions if are unsure of how to use in the Worksheet. | /VBA/RandomNumbers.htm | | Function RandLotto(Bottom As Integer, Top As Integer, Amount As Integer) | Dim iNum As String | Dim strNum As String | Dim i As Integer | | Application.Volatile | iNum = Int((Top - Bottom + 1) * Rnd + Bottom) | | For i = 1 To Amount | strNum = Trim(strNum & " " & iNum) | Do Until InStr(1, strNum, iNum) = 0 | iNum = Int((Top - Bottom + 1) * Rnd + Bottom) | Loop | Next i | | RandLotto = strNum | | End Function | | | | -- | | | | | FREE EXCEL NEWSLETTER | /News/2home.htm | | | | "Wayne Robinson" <waynerobinson@bigpond.com wrote in message | news:M9US8.22710$Hj3.70951@newsfeeds.bigpond.com... | | I am trying to use the rand function to generate several random numbers. | | That's fine. But how do I make sure that no two numbers are ever the | same??? | | | | Any help would be greatly appreciated! | | | | | | | | |
| 1182 | Very nice, Dana - My few tests showed a 99.8% improvement using your code over mine for the 100/1000000 scenario (after changing Ints to Longs), ~50% improvement for smaller lotto-type samples (e.g., 7/49), to a 400% degradation with larger proportion samples (e.g., 42/49). In article <uNl7mdHICHA.1728@tkmsftngp10, Dana DeLouis <ng_only@hotmail.com wrote: Hi J.E. Just for fun, and to add to your excellent idea. I believe that one needs different techniques based on different situations. If one needed to pick 999 out of 1,000 numbers, then doing a search on previous numbers would take a long time. A loop would take a long time as it tried to pick a number that was not already used. Loading the 1,000 numbers, and shuffling would probably be the best way. On the other hand, if you had to pick 100 numbers out of 1,000,000, then building a large array, and then sorting that array, would not be the best idea either. I like to use the following general technique. It handles the problem of a large pool to choose from. (not building a large array to shuffle). It is also pretty fast for "Lotto" size problems. Although it has a programming loop :( ,most of the "hard" looping is done by Excel as it checks previous entries. As a side note...I don't believe a collection can be used as a 1 dimensional array when finished. (example: Join / Split). That is why the last "for-loop." I just hate that last loop! <vbg ..and I have tried everything! I removed some code, and removed error checking... (16 numbers between 1 & 20) Debug.Print PickNumbers(16, 1, 20) 8,13,15,4,2,5,20,14,19,6,16,12,3,18,10,1 Function PickNumbers(N As Long, Low As Long, High As Long) As String '// By: Dana DeLouis Dim grp As New Collection Dim v As Variant Dim j As Long Dim s As String On Error Resume Next Do While grp.Count < N s = CStr(Int(Rnd * (High - Low + 1)) + Low) grp.Add s, s Loop '// Basically done. Do what you want here... ReDim v(1 To grp.Count) For j = 1 To grp.Count: v(j) = grp(j): Next PickNumbers = Join(v, ",") End Function |
| 1195 | Hi, You can refer to the previous and next sheet using defined names with XL4 formula's: Define these names: ThisSheet =GET.CELL(32+0*now();indirect("rc";False)) AllSheets =GET.WORKBOOK(1+0*now()) PrevSheet =INDEX(AllSheets;MATCH(ThisSheet;AllSheets;0)-1+0*now()) NextSheet =INDEX(AllSheets;MATCH(ThisSheet;AllSheets;0)+1+0*now()) Now refer to cel B1 on the next sheet using: =INDIRECT("'"&NextSheet&"'!"&CELL("address";B1)) Warning: Copying a cell that contains one of these names to another worksheet will cause XL2000 to crash. Copying a sheet with that name in a cell did not cause trouble with my XL2000 though. Regards, Jan Karel Pieterse Excel TA/MVP -----Original Message----- Thanks David, however, the new summary sheet, literally named 'Summary(2)' is still referring to the data on the original 'Detailed History' and not 'Detailed History(2)'. I presume that this is due to the original range being a workbook level name (as it was the first one created) and the second range is a worksheet level name. What I am wondering is if I can change the range definition so that instead of reading 'Detailed History'!$B$1 it says something like Sheets(-1)!$B$1 (as the Detailed History sheet is always the one before the Summary sheet - structure is protected and code acts as a backup) Any thoughts ? Robert "" <dmcritchie@msn.com wrote in message news:ecy7V0FICHA.2240@tkmsftngp08... Hi Robert, Group the two sheets. Select one sheet, Hold Ctrl key and select the second. Copy your sheets (move or copy sheets) as before. DO NOT FORGET TO UNGROUP YOUR SHEETS. Failure to ungroup sheets will result in serious damage as any change made to a cell in one will change the other sheets as well as if also entered into the same cell. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel Macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Robert Brown" <robert@nospam-southall-brown.co.uk wrote in message news:e8AH5mFICHA.2464@tkmsftngp12... I have two sheets, sheet A is detailed information, sheet B is a summary sheet with the last 12 pieces of info from sheet A. This is effected by using dynamic ranges to pick up the last 12 rows ie the range address for the last row, called surprisingly 'LastRecord', = 'OFFSET('Detailed History'!$B$1,COUNTA('Detailed History'!$B:$B)- 1,0,1,COUNTA('Detailed History'!$1:$1))' This is then picked up in the summary sheet by using the formula =IF(ISERR(LastRecordMinus1),0,LastRecord). This works fine when I have these two base sheets. How can I alter this so that when I copy the two sheets the summary refers to the correct 'Detailed History' ? Robert PS Sorry this was misposted in programming as well ! . |
| 1196 | That's the problem, I have about 200 rates per property, based on market segment. "Debra Dalgleish" <dsd@contextures.com wrote in message news:3D1C4833.70609@contextures.com... You could place the region in the page area, and place the towns and properties in the row area. Assuming one rate per property, all the rates should be visible. HB wrote: Hello Debra Thanks for your reply. Why can I achieve my objective using the MOWC pivot table in an .asp page, and show the data as stored in the database. But in Excel it shows calculated values eg. min, max, or sum values. My database contains rates for properties in specific areas and towns.' I want to select a region, and want to see all the properties for that region, and their specific rates, which varies throughout. What I can't understand is why is it possible as a web component, but not in Excel? Thanks HB "Debra Dalgleish" <dsd@contextures.com wrote in message news:3D1C41D9.8080008@contextures.com... A pivot table is designed to show summary data from a list. In the data area, it can only display numbers, and can't show the value from an individual cell in the list. If you explain your data structure, and what you're trying to achieve, perhaps someone could suggest a non-pivot table solution. HB wrote: Hallo I want to display the true value of a entity in the data area of an Excel pivot table. Not the sum, Min or Max values, but just the true value as it is stored in the database. How can I do this? Thanks -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 1200 | If you mean do they reference other sheets then the answer is that they are self contained and it sounds very similar to the task you've done previously with copying a graph, in fact, it is almost the same because a couple of buttons on the summary sheet have the ability to produce graphs from this sheet. But at the end of the day, yes, these two sheets could operate without any others existing. Robert "" <dmcritchie@msn.com wrote in message news:OTrQvXGICHA.2488@tkmsftngp09... Are these two sheets self contained with each other or are other sheets involved. I've done similar things in the past, copying a worksheet & graph together to make a new set. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel Macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Robert Brown" <robert@nospam-southall-brown.co.uk wrote in message news:ufA$88FICHA.1772@tkmsftngp09... Thanks David, however, the new summary sheet, literally named 'Summary(2)' is still referring to the data on the original 'Detailed History' and not 'Detailed History(2)'. I presume that this is due to the original range being a workbook level name (as it was the first one created) and the second range is a worksheet level name. What I am wondering is if I can change the range definition so that instead of reading 'Detailed History'!$B$1 it says something like Sheets(-1)!$B$1 (as the Detailed History sheet is always the one before the Summary sheet - structure is protected and code acts as a backup) Any thoughts ? Robert "" <dmcritchie@msn.com wrote in message news:ecy7V0FICHA.2240@tkmsftngp08... Hi Robert, Group the two sheets. Select one sheet, Hold Ctrl key and select the second. Copy your sheets (move or copy sheets) as before. DO NOT FORGET TO UNGROUP YOUR SHEETS. Failure to ungroup sheets will result in serious damage as any change made to a cell in one will change the other sheets as well as if also entered into the same cell. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel Macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Robert Brown" <robert@nospam-southall-brown.co.uk wrote in message news:e8AH5mFICHA.2464@tkmsftngp12... I have two sheets, sheet A is detailed information, sheet B is a summary sheet with the last 12 pieces of info from sheet A. This is effected by using dynamic ranges to pick up the last 12 rows ie the range address for the last row, called surprisingly 'LastRecord', = 'OFFSET('Detailed History'!$B$1,COUNTA('Detailed History'!$B:$B)-1,0,1,COUNTA('Detailed History'!$1:$1))' This is then picked up in the summary sheet by using the formula =IF(ISERR(LastRecordMinus1),0,LastRecord). This works fine when I have these two base sheets. How can I alter this so that when I copy the two sheets the summary refers to the correct 'Detailed History' ? Robert PS Sorry this was misposted in programming as well ! |
| 1202 | Myrna I had wondered about doing this, but to put it bluntly, when I think about it my brain hurts ! Thinking aloud, I would have to go into the detailed sheet and change each range definition so that it applied to the new sheet - doable but I need to specify that it is the range on this sheet ie not the workbook level name, what is the syntax for that if I wanted to change range name 'LastRecord' on sheet 'Air Quality Detail' to refer to the range A2:C10 (for simplicity's sake - I could work it out for the actual range) ? I could then presumably refer to the range 'Air Quality Detail'!LastRecord on the summary sheet ? Robert "Myrna Larson" <myrnalarson@chartermi.net wrote in message news:ajkuhu4ev3gddbjc4hvg2qget8tmem79fa@4ax.com... There's no built-in relative sheet addressing. You would have to accomplish this a VBA macro that copies the sheet, then changes the name definition and/or formulas. On Sun, 30 Jun 2002 18:56:24 +0100, "Robert Brown" <robert@nospam-southall-brown.co.uk wrote: Thanks David, however, the new summary sheet, literally named 'Summary(2)' is still referring to the data on the original 'Detailed History' and not 'Detailed History(2)'. I presume that this is due to the original range being a workbook level name (as it was the first one created) and the second range is a worksheet level name. What I am wondering is if I can change the range definition so that instead of reading 'Detailed History'!$B$1 it says something like Sheets(-1)!$B$1 (as the Detailed History sheet is always the one before the Summary sheet - structure is protected and code acts as a backup) Any thoughts ? Robert "" <dmcritchie@msn.com wrote in message news:ecy7V0FICHA.2240@tkmsftngp08... Hi Robert, Group the two sheets. Select one sheet, Hold Ctrl key and select the second. Copy your sheets (move or copy sheets) as before. DO NOT FORGET TO UNGROUP YOUR SHEETS. Failure to ungroup sheets will result in serious damage as any change made to a cell in one will change the other sheets as well as if also entered into the same cell. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel Macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Robert Brown" <robert@nospam-southall-brown.co.uk wrote in message news:e8AH5mFICHA.2464@tkmsftngp12... I have two sheets, sheet A is detailed information, sheet B is a summary sheet with the last 12 pieces of info from sheet A. This is effected by using dynamic ranges to pick up the last 12 rows ie the range address for the last row, called surprisingly 'LastRecord', = 'OFFSET('Detailed History'!$B$1,COUNTA('Detailed History'!$B:$B)-1,0,1,COUNTA('Detailed History'!$1:$1))' This is then picked up in the summary sheet by using the formula =IF(ISERR(LastRecordMinus1),0,LastRecord). This works fine when I have these two base sheets. How can I alter this so that when I copy the two sheets the summary refers to the correct 'Detailed History' ? Robert PS Sorry this was misposted in programming as well ! |
| 1218 | Hi, i assume that all the zip codes are the same length, 5 characters?. If so left align the column, then Data menu, text to columns, fixed width. Make sure that the column next to it is blank. Hope this Helps. Muirray -----Original Message----- i have created a spreadsheet that has all california zip codes on it. within each cell is a zip code and a corresponding city -- with the zip code listed first. the problem is this: i don't know how to rearrange my spreadsheet to where the zip and city name switch places. because they are within each cell together, i cannot simply 'juxtapose' rows with one another -- i need to actually highlight all of the cells, and then switch the order of the zip/city name within each cell. i need to do this so that i can alphabetize the cells. currently, if i alphabetize, the zipcodes are the first characters in each cell, so it basically sorts them numerically -- hence the need for me to have the cell content read city first, and then zip. please heeelllp! thanks. . |
| 1221 | To use a sheet-level name, you include the sheet name as part if the name and part of the reference: Name is 'Air Quality Detail'!LastRecord and the reference is 'Air Quality Detail'!$A$2:$C$10 Be careful with relative references. They are relative to the cell that's active at the time you write the formula. If the cell won't change, use absolute, as I did above. You perhaps could use a macro to update and or create the names. On Mon, 1 Jul 2002 09:17:27 +0100, "Robert Brown" <robert@nospam-southall-brown.co.uk wrote: Myrna I had wondered about doing this, but to put it bluntly, when I think about it my brain hurts ! Thinking aloud, I would have to go into the detailed sheet and change each range definition so that it applied to the new sheet - doable but I need to specify that it is the range on this sheet ie not the workbook level name, what is the syntax for that if I wanted to change range name 'LastRecord' on sheet 'Air Quality Detail' to refer to the range A2:C10 (for simplicity's sake - I could work it out for the actual range) ? I could then presumably refer to the range 'Air Quality Detail'!LastRecord on the summary sheet ? Robert "Myrna Larson" <myrnalarson@chartermi.net wrote in message news:ajkuhu4ev3gddbjc4hvg2qget8tmem79fa@4ax.com... There's no built-in relative sheet addressing. You would have to accomplish this a VBA macro that copies the sheet, then changes the name definition and/or formulas. On Sun, 30 Jun 2002 18:56:24 +0100, "Robert Brown" <robert@nospam-southall-brown.co.uk wrote: Thanks David, however, the new summary sheet, literally named 'Summary(2)' is still referring to the data on the original 'Detailed History' and not 'Detailed History(2)'. I presume that this is due to the original range being a workbook level name (as it was the first one created) and the second range is a worksheet level name. What I am wondering is if I can change the range definition so that instead of reading 'Detailed History'!$B$1 it says something like Sheets(-1)!$B$1 (as the Detailed History sheet is always the one before the Summary sheet - structure is protected and code acts as a backup) Any thoughts ? Robert "" <dmcritchie@msn.com wrote in message news:ecy7V0FICHA.2240@tkmsftngp08... Hi Robert, Group the two sheets. Select one sheet, Hold Ctrl key and select the second. Copy your sheets (move or copy sheets) as before. DO NOT FORGET TO UNGROUP YOUR SHEETS. Failure to ungroup sheets will result in serious damage as any change made to a cell in one will change the other sheets as well as if also entered into the same cell. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel Macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Robert Brown" <robert@nospam-southall-brown.co.uk wrote in message news:e8AH5mFICHA.2464@tkmsftngp12... I have two sheets, sheet A is detailed information, sheet B is a summary sheet with the last 12 pieces of info from sheet A. This is effected by using dynamic ranges to pick up the last 12 rows ie the range address for the last row, called surprisingly 'LastRecord', = 'OFFSET('Detailed History'!$B$1,COUNTA('Detailed History'!$B:$B)-1,0,1,COUNTA('Detailed History'!$1:$1))' This is then picked up in the summary sheet by using the formula =IF(ISERR(LastRecordMinus1),0,LastRecord). This works fine when I have these two base sheets. How can I alter this so that when I copy the two sheets the summary refers to the correct 'Detailed History' ? Robert PS Sorry this was misposted in programming as well ! |
| 1225 | Hi Mows. Yes! I find this very interesting! I have always thought this as a good problem from a "program timing" point of view for a while. The problem of picking 42 out of 49 size problems I find is very interesting. Although there are a few pretty looking solutions, the timing involved in every effort is actually slower then the bruit force method of building every number, and sorting. I think J.E.'s method is actually faster for these size problems. I have been trying to come up with a formula to measure expected attempts like those that you have posted for a long time without success. So, thanks!! I lost you on your formula though. I had to follow along using Mathematica. With "a" set like you mentioned.your formula Simplify[Sum[x*a^(x - 1)*(1 - a), {x, 1, Infinity}]] Did reduce to the following. n/(1 - i + n) With Expectation "e" set. e[i_, n_] := Plus @@ Table[n/(n - x + 1), {x, i}] I tried the 3 solutions that you mentioned. I get the same answers. e[4, 20] - 4.340213278293773 e[16, 20] - 30.288126476206973 e[100, 100] - 518.737751763962 I really lost you on the Harmonic Table though. I will have to study it some more. I cannot figure out what you are doing for large numbers. I tested your data quickly, and the numbers agree. Table[{10^n, Plus @@ Sum[1./z, {z, 10^n}]}, {n, 6}] {10., 2.9289682539682538}, {100., 5.187377517639621}, {1000., 7.485470860550343}, {10000., 9.787606036044345}, {100000., 12.090146129863282}, {1.*^6, 14.392726722864781} I honestly cannot figure out how you, and now Euro, came up with your equation. I do remember something Jay Petrulis wrote about a year ago. Therefore, I took a guess and combined what you wrote with some other stuff. For small sizes, this agrees with you numbers. Function HowManyTrys(Pick As Long, N As Long) As Double Dim SoFar As Double Dim j As Long For j = N - Pick + 1 To N SoFar = SoFar + 1 / WorksheetFunction.HypGeomDist(1, 1, j, N) Next HowManyTrys = SoFar End Function Sub TestIt() Debug.Print HowManyTrys(4, 20) Debug.Print HowManyTrys(16, 20) Debug.Print HowManyTrys(100, 100) '//and of course... Debug.Print HowManyTrys(42, 49) End Sub Anwers I got were. 4.34021327829377 30.288126476207 518.737751763962 92.4310615781418 Anyway, thanks again. -- Dana DeLouis Windows XP & Office XP = = = = = = = = = = = = = = = = = "mows" <mows@mopar.freeserve.co.uk wrote in message news:exCGY9RICHA.2132@tkmsftngp10... Dana, I don't know if it is of interest, but we can get a rought idea of how efficient your code is. Stripping it down we get something like for i = 1 to k do {Choose a number} Repeat Until {Choice not been out before} next i The cost of the main loop must be at least k. The probability of a repeat is (i - 1) / N, where N is the total number of unique choices. i - 1 because there will not be a repeat the first time around. The probability that the length of the inner loop is x, is the probability that there were (x - 1) repeats and 1 unique. Therefore .... p(x) = a^(x - 1)) * (1 - a) where a = (i - 1) / N So, the expected length is E{x}=Sum[x p(x), {x, 1, Infinity}] = N / (N - (i - 1) Introducing H(n) for Harmonic numbers. e.g. H(4) = 1 / 1 + 1 / 2 + 1 / 3 + 1 / 4 The total cost for the outer loop is the sum of E{x} through 1 to k = N * Sum[E{x},{x,1,k}] = N * Sum [1 / (N - (i - 1)),{i,1,k}] = N * (H(N) - H(N - k)) (Here, N = High-Low+1 and k = n in your code) For your example PickNumbers(16, 1, 20) we would expect to need about 30 loops to get the 16 numbers. It may be quicker here to do something like PickNumbers(4, 1, 20), which takes only 4.3 loops and select those not chosen. With this in mind, you will only ever need to do a maximun of PickNumbers(X/2, 1, X),. which needs about X Log 2 loops. If you tried to something like PickNumbers(100, 1, 100), then expect to wait over 500 loops. For the U.K lottery PickNumbers(6, 1, 49) will on average need 6.33 loops. In excel speak, if C3 = High, C2 = low and C1 = n then =(C3-C2+1) * (Harmonic(C3-C2+1)-Harmonic((C3-C2+1)-C1)) returns the expected number of loops; ie the value the counter in this snippet from you code Do While grp.Count < n counter = counter + 1 s = CStr(Int(Rnd * (High - Low + 1)) + Low) grp.Add s, s Loop Function Harmonic(n) Dim B(7) B(0) = 0.577215664901533 'Euler's Gamma B(1) = -1 / 12 B(2) = 1 / 120 B(3) = -1 / 252 B(4) = 1 / 240 B(5) = -1 / 132 B(6) = 691 / 32760 '"adjusted " Bernoulli numbers B(7) = -1 / 12 If n 10 Then k = 8 'adjust number of cycles If n 1000 Then k = 4 If n 6000000 Then k = 1 z = n * n For i = 1 To k h = h / z + B(k - i) Next Har = h + Log(n) + 1 / 2 / n Else For i = 1 To n h = h + 1 / i 'add up reciprocals for small n Next Har = h End If Harmonic = Har End Function To get the feel, Here a few Harmonic Numbers n H(n) 0 0 1 1 10 2.9290 100 5.1874 1000 7.4855 10000 9.7876 100000 12.09 1000000 14.3927 mows ExcelXP/WinXP |
| 1232 | Excel has a COUNTIF and SUMIF command. Is there a way to make a MAXIF command? |
| 1239 | Hi Ted The best way for this is via the DMAX function, this can take up to 256 separate criteria. There are array formulae that look more efficient but they wouldn't come close is efficiency. =DMAX($A$1:$H$5000,$C$1,Criteria) Where "$A$1:$H$5000 is your Data Table C1 is a reference to the column heading for the data you want to get the MaxIf from "Criteria" is a named range that has copy of your heading that needs evaluation with a criteria directly underneath, eg 50 I have some examples of the Database function here that you can download if you wish: /download/default.htm under "Dfunction with validation.zip" -- \ Get 8 Add-ins in one! For less than the price of 1 /Services/AddinExamples.htm FREE EXCEL NEWSLETTER /News/2home.htm "Ted Chee" <tchee@jamcracker.com wrote in message news:143d601c22162$38aabb60$9be62ecf@tkmsftngxa03... | Excel has a COUNTIF and SUMIF command. Is there a way to | make a MAXIF command? |
| 1240 | I have a spreadsheet that was created in Excel 2000 which displays an error condition, but only when opened in Excel 2002. Some cells containing numbers are displayed with a small green triangle in the upper left corner of the cell. When you place your mouse pointer in this cell an error box appears to the left of the cell which contains an exclamation point and a drop down menu. The drop down menu tells you that you have a number in the cell which has been stored as text, and asks whether you want to correct this "error" by converting it to a number. Once you do this the green triangle is removed, and you are presumably okay. I opened a new spreadsheet in 2002 and pre-formatted a cell to text before entering a number. The green triangle appeared, and reformatting the cell to general or number did not make it go away. You have to fix the "error" with the special drop down box to the left of the cell. However, if you format a cell as text after a number has been entered into it using general or number format this error does not occur. I also encountered this problem when trying to import this spreadsheet into Access 2000. These cells cause an import error to occur - error 3349 numeric overflow. Once the "error" cells are fixed this import error disappears. Is anyone aware of a general fix for this problem by way of a service pack for Excel 2002? Bill |
| 1242 | Hi Mark Just so you know, Excel stores dates as serial numbers with 1 being 1/Jan/1900, 2 being 2/Jan/1900 etc. When you reference as cell that has a date format Excel assumes you want a date returned as your result and formats it the same, painful at times, handy at other times. -- \ Get 8 Add-ins in one! For less than the price of 1 /Services/AddinExamples.htm FREE EXCEL NEWSLETTER /News/2home.htm "Mark" <Markh@bwacc.org wrote in message news:Oup0JnUICHA.2632@tkmsftngp09... | When I use the DATEDIF() function This is what I get | | DATEDIF(G2, K2, "Y") Where G2 = 7/6/65 And K2 = 7/31/01 | I Get 2/5/1900. I want to see the difference of Years. | | | |
| 1247 | Hi Bill ToolsOptionsError Checking. You can record a macro toggling the options to get the code. -- \ Get 8 Add-ins in one! For less than the price of 1 /Services/AddinExamples.htm FREE EXCEL NEWSLETTER /News/2home.htm "Bill Murphy" <wmurphy@NO*SPAM!houston.rr.com wrote in message news:377U8.25029$p85.681329@twister.austin.rr.com... | I have a spreadsheet that was created in Excel 2000 which displays an error | condition, but only when opened in Excel 2002. Some cells containing | numbers are displayed with a small green triangle in the upper left corner | of the cell. When you place your mouse pointer in this cell an error box | appears to the left of the cell which contains an exclamation point and a | drop down menu. The drop down menu tells you that you have a number in the | cell which has been stored as text, and asks whether you want to correct | this "error" by converting it to a number. Once you do this the green | triangle is removed, and you are presumably okay. | | I opened a new spreadsheet in 2002 and pre-formatted a cell to text before | entering a number. The green triangle appeared, and reformatting the cell | to general or number did not make it go away. You have to fix the "error" | with the special drop down box to the left of the cell. However, if you | format a cell as text after a number has been entered into it using general | or number format this error does not occur. | | I also encountered this problem when trying to import this spreadsheet into | Access 2000. These cells cause an import error to occur - error 3349 | numeric overflow. Once the "error" cells are fixed this import error | disappears. | | Is anyone aware of a general fix for this problem by way of a service pack | for Excel 2002? | | Bill | | |
| 1251 | One way: Assume you want the Max of B1:B1000 for each row in which there's a 1 in column A: =MAX(IF(A1:A1000=1,B1:B1000,"")) The DMAX command is probably faster, especially for large data sets, but requires that you have column headers and a criteria range. If you're doing a *lot* of MAX(IF())s that's probably the way to go. In article <143d601c22162$38aabb60$9be62ecf@tkmsftngxa03, Ted Chee <tchee@jamcracker.com wrote: Excel has a COUNTIF and SUMIF command. Is there a way to make a MAXIF command? |
| 1254 | Dave, Thanks for this tip. I turned off this error check and it immediately resolved my problem, including the problem when importing to Access. Bill "" <DavidH@OzGrid.com wrote in message news:6o7U8.18$tm.19011@vicpull1.telstra.net... Hi Bill ToolsOptionsError Checking. You can record a macro toggling the options to get the code. -- \ Get 8 Add-ins in one! For less than the price of 1 /Services/AddinExamples.htm FREE EXCEL NEWSLETTER /News/2home.htm "Bill Murphy" <wmurphy@NO*SPAM!houston.rr.com wrote in message news:377U8.25029$p85.681329@twister.austin.rr.com... | I have a spreadsheet that was created in Excel 2000 which displays an error | condition, but only when opened in Excel 2002. Some cells containing | numbers are displayed with a small green triangle in the upper left corner | of the cell. When you place your mouse pointer in this cell an error box | appears to the left of the cell which contains an exclamation point and a | drop down menu. The drop down menu tells you that you have a number in the | cell which has been stored as text, and asks whether you want to correct | this "error" by converting it to a number. Once you do this the green | triangle is removed, and you are presumably okay. | | I opened a new spreadsheet in 2002 and pre-formatted a cell to text before | entering a number. The green triangle appeared, and reformatting the cell | to general or number did not make it go away. You have to fix the "error" | with the special drop down box to the left of the cell. However, if you | format a cell as text after a number has been entered into it using general | or number format this error does not occur. | | I also encountered this problem when trying to import this spreadsheet into | Access 2000. These cells cause an import error to occur - error 3349 | numeric overflow. Once the "error" cells are fixed this import error | disappears. | | Is anyone aware of a general fix for this problem by way of a service pack | for Excel 2002? | | Bill | | |
| 1270 | Last modified, corect assumption. Try this Phil: Function FileInfo(strFile As String) As String Dim oFSO As Object, oF As Object Set oFSO = CreateObject("Scripting.FileSystemObject") Set oF = oFSO.GetFile(strFile) FileInfo = UCase(strFile) & Chr(10) FileInfo = FileInfo & "Created: " & oF.DateCreated & Chr(10) FileInfo = FileInfo & "Last Accessed: " & oF.DateLastAccessed & Chr(10) FileInfo = FileInfo & "Last Modified: " & oF.DateLastModified End Function Sub test() MsgBox FileInfo("C:\temp\Book1.xls") End Sub HTH. Best wishes Harald "Myrna Larson" <myrnalarson@chartermi.net skrev i melding news:ssr1iu8ne6ctfs2ecpm3s482831h6i4auh@4ax.com... I didn't see the original question. Is is clear whether he wants the date last *modified* vs the date *created*? If the latter, I have some code for that. On Mon, 1 Jul 2002 07:08:53 -0400, "Jim Rech" <jarech@kpmg.com wrote: Try the FileDateTime function. |
| 1288 | Assuming zip codes are 5 numbers in length with a space before the city name (if not alter code) Sub CityZip() Dim CtyLst As Range Dim cell As Range Dim LstRow As Long LstRow = Cells(Rows.Count, "A").End(xlUp).Row Set CtyLst = Range("A2:A" & LstRow) For Each cell In CtyLst cell = Right(cell, Len(cell) - 6) & " " & Left(cell, 5) Next cell End Sub HTH "mark" <jedi208@hotmail.com wrote in message news:1209c01c2214f$c98e14a0$37ef2ecf@TKMSFTNGXA13... i have created a spreadsheet that has all california zip codes on it. within each cell is a zip code and a corresponding city -- with the zip code listed first. the problem is this: i don't know how to rearrange my spreadsheet to where the zip and city name switch places. because they are within each cell together, i cannot simply 'juxtapose' rows with one another -- i need to actually highlight all of the cells, and then switch the order of the zip/city name within each cell. i need to do this so that i can alphabetize the cells. currently, if i alphabetize, the zipcodes are the first characters in each cell, so it basically sorts them numerically -- hence the need for me to have the cell content read city first, and then zip. please heeelllp! thanks. |
| 1289 | Hello, I have a program which creates a number (25 or more) .csv (comma seperated values) data files in a subdirectory. I would like to open a spreadsheet and combine all these data files into a single worksheet so that I can do some simple sums on the data. What is the best way to do this. All advice welcome Thanks KK |
| 1307 | Dana, The Harmonic function was written in the days of the 80286 for QBasic. I did not have the option to add up any numbers in a large loop, hence the long winded code that will only do a few loops. Eero's code is more appropriate for Excel. It is based upon the fact that H(n) = Log(n) + Eulers constant + O(1/n). The first 2^16 numbers are simply added up, above that it uses Log(n) + Euler's constant, which is certainly near enough for what we are doing here. Harmonic() goes a bit further, H(n) = Log(n) + Eulers constant + 1/(2n) - 1/(12n^2) + 1/(120n^4) & etc. The constants being the Bernoulli numbers. Have a peek here /home/kmath284.htm /HarmonicNumber.html although being a Mathematica person, I am sure you will know this last site well. I lost you on your formula though. I had to follow along using Mathematica.With "a" set like you mentioned.your formula Simplify[Sum[x*a^(x - 1)*(1 - a), {x, 1, Infinity}]] Did reduce to the following. n/(1 - i + n) Here Sum[x*a^(x - 1)*(1- a), {x, 1, Infinity}] taking a outside the sum =Sum[x*a^x*(1- a), {x, 1, Infinity}] * (1/a) now take (1- a) outside the sum =Sum[x*a^x, {x, 1, Infinity}] * (1/a)*(1-a) =(1-a)*(1/a) * a/(a-1)^2 = 1/(1-a) and so E{x}= n/(1- i + n) The total cost is now = Sum[E{x},{x,1,k}] = Sum [N / (N - i + 1),{i,1,k}] = N* Sum [1 / (N - i + 1),{i,1,k}] = N* Sum [1 / j, {j, N - k + 1, N}] and here we see how Harmonic numbers creap in. With Expectation "e" set. e[i_, n_] := Plus @@ Table[n/(n - x + 1), {x, i}] I notice that Mathematica comes up with n (-PolyGamma[0, k - n] + PolyGamma[0, -n]) for n* Sum [1 / (n - i + 1), {i, 1, k}], which then does not like integers! Mathematica4 has HarmonicNumber, so you could do e[k_, n_] := n *(HarmonicNumber[n] - HarmonicNumber[n - k]) N[e[4, 20], 17] --- 4.3402132782937736 e[k_, n_] := n* Sum [1 / (n - i + 1), {i, 1, k}] also seems fine. The problem of picking 42 out of 49 size problems I find is very interesting. Or not picking 7 / 49 Function HowManyTrys(Pick As Long, N As Long) As Double Dim SoFar As Double Dim j As Long For j = N - Pick + 1 To N SoFar = SoFar + 1 / WorksheetFunction.HypGeomDist(1, 1, j, N) Next HowManyTrys = SoFar End Function Spot on -- but perhaps a bit complex as for integer x and y =HYPGEOMDIST(1,1,x,y) = x / y From the formula above, N* Sum [1 / j, {j, N - k + 1, N}] we can simply do --- Function HowManyTrys2(Pick As Long, N As Long) As Double Dim SoFar As Double Dim j As Long For j = N - Pick + 1 To N SoFar = SoFar + 1 / j Next HowManyTrys2 = N * SoFar End Function Using the Harmonic function we could also do --- Function HowManyTrys3(Pick As Long, N As Long) As Double HowManyTrys3 = N * (Harmonic(N) - Harmonic(N - Pick)) End Function mows Windows XP & Office XP = = = = = = = = = = = = = = = = = |
| 1321 | One way: Assume the data to be copied is in the range B2:J10 and the destination is sheetB, columns A:I (adjust to suit): Public Sub MoveToSheetB() With Sheets("SheetA").Range("B2:J10") .Copy Destination:= Sheets("SheetB").Range("A" & _ Rows.Count).End(xlUp).Offset(1, 0) .ClearContents End With End Sub In article <156cc01c221ce$b1453e80$35ef2ecf@TKMSFTNGXA11, LWhitt <queenbyte@yahoo.com wrote: I have data in spreadsheet a. When I am completed with this information, I would like to copy this information in spreadsheet A, paste in spreadsheet B. and come back to spreadsheet A and delete the information. I would like to be able to append this information to the the information that is already in spreadsheet B (I am not a programmer). When I set the macro to do this, it just goes to the same place and does the paste over the information that is already there. Is this a possible task? Thanks! L. Whitt |
| 1331 | One way... =SUM(IF(NOT(ISBLANK(C1:C8))*(ISBLANK(B1:B8)),A1:A8)) Hit ctrl+shift+enter for it to work. HTH Jason Atlanta, GA -----Original Message----- I'm really confused trying to use 2 different arguments to sum if. in the sample if cell(s)in column c1:c8 is NOT blank and column B1:B8 corresponding cell(s) IS blank, sum the A1:A8 corresponding cell(s) |
| 1358 | Thanks,Rob That was the key. I've used and opened thousands of worksheets and this was the first one that I noticed this behavior. MikeL "Rob Bovey" <Rob_Bovey@msn.com wrote in message news:OJ90hPjICHA.2488@tkmsftngp09... I doublechecked and it worked only when the book's contents were protected. It worked only with the tab key but not the arrow keys. Hi Mike, That's the secret. Select a block of cells, choose Format/Cells/Protection from the menu, and uncheck the Locked checkbox. Next, protect the worksheet using the Tools/Protection menu. You should get the tabbing wrap-around behavior you describe within the block of unlocked cells. -- Rob Bovey, MCSE, MCSD, Excel MVP Application Professionals / * Please post all replies to this newsgroup * * I delete all unsolicited e-mail responses * "Mike Lavallee" <lavalleemike@cox.net wrote in message news:uGGSbviICHA.2528@tkmsftngp08... Let me clarify what I wrote earlier. I found a workbook which allowed me to do the following A1 tab A2 tab A3 tab A4 (auto-return at column "T") B1 tab B2 tab B3 tab B4 (auto-return at column "T") Etc. I Checked every place I could think of which might hide code. Sheet, Workbook, modules. NO CELLS were preselected. The ScrollArea for the sheet had no input. It behaved undoubtedly as it was supposed to. It went to the predetermined column then dropped down a row for the next set of data. WISCONSIN DEPARTMENT OF PUBLIC INSTRUCTION SCHOOL FINANCIAL SERVICES SUMMER SCHOOL PROGRAM is where the workbook is from pi1805_00a.xls is the workbook name and I was able to refind it using Google search. It worked as I described on the sheet named "PI-1804-W (Worksheet)" I doublechecked and it worked only when the book's contents were protected. It worked only with the tab key but not the arrow keys. Was the data originally entered through a data form or some such??? I'd like to be able to use the auto carriage return feature, if it is a feature and not a quirk. Regards, Puzzled |
| 1362 | Another way is to define a range for each worksheet that you want to use as the selection to define the zoom factor. For instance, if you wanted to sheet6 to have A1:J1, then define a name called myZoom that refers to Sheet6!$a$1:$J$1. It's important to include the sheet name in the definition. Then you can reuse this sheet level name on multiple sheets. One way to define the range name is to use Insert|Name, then in the "names in workbook" box, put the sheetname followed by exclamation point, then the name (like sheet6!myZoom). Even easier is to select the range, then use the name box (left of the formula bar) and type sheet6!myZoom (you have to put the sheetname here, too.) Then you can loop through the sheets and use the same stuff zoom each sheet. Option Explicit Sub testme() Dim tstRange As Range Dim curWks As Worksheet Dim wks As Worksheet Set curWks = ActiveSheet For Each wks In ActiveWorkbook.Worksheets With wks Set tstRange = Nothing On Error Resume Next Set tstRange = .Range("myzoom") On Error GoTo 0 If tstRange Is Nothing Then 'do nothing Else .Select tstRange.Select ActiveWindow.Zoom = True .Range("a1").Select End If End With Next wks curWks.Select End Sub Lee Heppenstall wrote: Hi, I need help with a workbook I am currently creating. Many of our users use different screen resolutions and laptops etc. Therefore when I create a workbook some users only see parts of it and have to scroll along for the rest. Is there a way to automatically re-size a worksheet when the workbook is opened. I would be grateful for any assistance. -- Dave Peterson ec35720@msn.com |
| 1392 | Enter this formula in F4 and copy across to J4 and down to F8 =SUMPRODUCT(($B$2:$B$8=$E4)*($C$2:$C$8=F$3)) HTH "Dot Appleman" <appleman@achamp.gsfc.nasa.gov wrote in message news:1276c01c222a4$8d41b5f0$9ee62ecf@tkmsftngxa05... Help again with an array problem (I think). see file This board has been SO helpful. Dot |
| 1419 | Try: =INDIRECT(ADDRESS(MAX((NOT(ISERROR(FIND(TODAY(),A1:O20))) *ROW(1:20))),MAX((NOT(ISERROR(FIND(TODAY(),A1:O20)))*COLUMN (A1:O20)))+2)) Array-entered. I assumed the range was 20 rows (1-20) and 15 columns (A- O). HTH Jason Atlanta, GA -----Original Message----- I have a sheet with 3 columns: In col. B I have the numbers 1 to 100, in successive rows In col. C I have a different integer for each serial number, which I refer to as a code number. In col. A I enter today's date in the first blank row. Usually I use one serial number each day, but some days two or three serial numbers.. When I've used all the numbers, I delete the dates in col. A, and start again. On another sheet I want to return the value of the code number for today. I use the formula: VLOOKUP(NOW(), A1:A100,2). This works fine. I am able to print a document which has this reference number on it. Also if I enter today's date in two rows the formula returns the code corresponding to the more recent entry. Problem is I'd like to reconfigure the sheet with 3 columns and 100 rows so that I have 15 columns and 20 rows, and I can see all 100 serial numbers on one screen. I'd have the dates in col. A, the numbers 1-20 in column B, the code numbers in C, dates in col. D, numbers 21-40 in col. E, code number in col. F, and so on. This way I can enter the date straight away rather than have to go down the screen. It looks nicer and has a more compact feel to it. However I don't know how I can use the VLOOKUP in these circumstances. How can I return the code number, 2 cells to the right of today's date? I'd be grateful for any ideas. Thanks, John Stout . |
| 1427 | Wow, thanks Jason, that is great, it works! Brilliant! VMT John Jason Morin <jason.morin@us.exel.com wrote in message news:14c7201c222bc$7d926f60$b1e62ecf@tkmsftngxa04... Try: =INDIRECT(ADDRESS(MAX((NOT(ISERROR(FIND(TODAY(),A1:O20))) *ROW(1:20))),MAX((NOT(ISERROR(FIND(TODAY(),A1:O20)))*COLUMN (A1:O20)))+2)) Array-entered. I assumed the range was 20 rows (1-20) and 15 columns (A- O). HTH Jason Atlanta, GA -----Original Message----- I have a sheet with 3 columns: In col. B I have the numbers 1 to 100, in successive rows In col. C I have a different integer for each serial number, which I refer to as a code number. In col. A I enter today's date in the first blank row. Usually I use one serial number each day, but some days two or three serial numbers.. When I've used all the numbers, I delete the dates in col. A, and start again. On another sheet I want to return the value of the code number for today. I use the formula: VLOOKUP(NOW(), A1:A100,2). This works fine. I am able to print a document which has this reference number on it. Also if I enter today's date in two rows the formula returns the code corresponding to the more recent entry. Problem is I'd like to reconfigure the sheet with 3 columns and 100 rows so that I have 15 columns and 20 rows, and I can see all 100 serial numbers on one screen. I'd have the dates in col. A, the numbers 1-20 in column B, the code numbers in C, dates in col. D, numbers 21-40 in col. E, code number in col. F, and so on. This way I can enter the date straight away rather than have to go down the screen. It looks nicer and has a more compact feel to it. However I don't know how I can use the VLOOKUP in these circumstances. How can I return the code number, 2 cells to the right of today's date? I'd be grateful for any ideas. Thanks, John Stout . |
| 1430 | Eeeeek! I spoke too soon. If I have today's date in cell A20, for serial # 20, and also in cell D1 for serial #21, the formula returns the code number for serial #40 as if today's date were in cell D20. I would want the code number for serial #21. How could I get around this? Thanks, John Jason Morin <jason.morin@us.exel.com wrote in message news:14c7201c222bc$7d926f60$b1e62ecf@tkmsftngxa04... Try: =INDIRECT(ADDRESS(MAX((NOT(ISERROR(FIND(TODAY(),A1:O20))) *ROW(1:20))),MAX((NOT(ISERROR(FIND(TODAY(),A1:O20)))*COLUMN (A1:O20)))+2)) Array-entered. I assumed the range was 20 rows (1-20) and 15 columns (A- O). HTH Jason Atlanta, GA -----Original Message----- I have a sheet with 3 columns: In col. B I have the numbers 1 to 100, in successive rows In col. C I have a different integer for each serial number, which I refer to as a code number. In col. A I enter today's date in the first blank row. Usually I use one serial number each day, but some days two or three serial numbers.. When I've used all the numbers, I delete the dates in col. A, and start again. On another sheet I want to return the value of the code number for today. I use the formula: VLOOKUP(NOW(), A1:A100,2). This works fine. I am able to print a document which has this reference number on it. Also if I enter today's date in two rows the formula returns the code corresponding to the more recent entry. Problem is I'd like to reconfigure the sheet with 3 columns and 100 rows so that I have 15 columns and 20 rows, and I can see all 100 serial numbers on one screen. I'd have the dates in col. A, the numbers 1-20 in column B, the code numbers in C, dates in col. D, numbers 21-40 in col. E, code number in col. F, and so on. This way I can enter the date straight away rather than have to go down the screen. It looks nicer and has a more compact feel to it. However I don't know how I can use the VLOOKUP in these circumstances. How can I return the code number, 2 cells to the right of today's date? I'd be grateful for any ideas. Thanks, John Stout . |
| 1433 | Very clever, Jason! "Jason Morin" <jason.morin@us.exel.com wrote in message news:14c7201c222bc$7d926f60$b1e62ecf@tkmsftngxa04... Try: =INDIRECT(ADDRESS(MAX((NOT(ISERROR(FIND(TODAY(),A1:O20))) *ROW(1:20))),MAX((NOT(ISERROR(FIND(TODAY(),A1:O20)))*COLUMN (A1:O20)))+2)) Array-entered. I assumed the range was 20 rows (1-20) and 15 columns (A- O). HTH Jason Atlanta, GA -----Original Message----- I have a sheet with 3 columns: In col. B I have the numbers 1 to 100, in successive rows In col. C I have a different integer for each serial number, which I refer to as a code number. In col. A I enter today's date in the first blank row. Usually I use one serial number each day, but some days two or three serial numbers.. When I've used all the numbers, I delete the dates in col. A, and start again. On another sheet I want to return the value of the code number for today. I use the formula: VLOOKUP(NOW(), A1:A100,2). This works fine. I am able to print a document which has this reference number on it. Also if I enter today's date in two rows the formula returns the code corresponding to the more recent entry. Problem is I'd like to reconfigure the sheet with 3 columns and 100 rows so that I have 15 columns and 20 rows, and I can see all 100 serial numbers on one screen. I'd have the dates in col. A, the numbers 1-20 in column B, the code numbers in C, dates in col. D, numbers 21-40 in col. E, code number in col. F, and so on. This way I can enter the date straight away rather than have to go down the screen. It looks nicer and has a more compact feel to it. However I don't know how I can use the VLOOKUP in these circumstances. How can I return the code number, 2 cells to the right of today's date? I'd be grateful for any ideas. Thanks, John Stout . |
| 1439 | Why does Excel only allow the entry of time as a specific 'time of day' instead of a 'unit' of time? For example, in a spreadsheet which records how many hours per day each staff member is on the telephone, entering "5:25" into a cell will calculate as "5:25 AM". What I'm trying to record is not a time of day, but 5 hours and 25 minutes of time. Entering "15:25" calculates as "3:25 PM". Evaluating the actual value shows me that Excel has automatically converted it into a date serial number (i.e. formatting the same value as a date gives me "1/0/1900 3:25 PM") To abate the confusion this causes, I've always suggested my coworkers separate 'time' data-entry into three columns: Hours, Minutes, and Seconds. Then summing or averaging the data of course requires a formula which adds each column and divides by 60 or 3600 accordingly to convert to hours. Then a separate formula is required to re-convert it to hours, minutes and seconds. Am I just not seeing how to enter time as a unit or is this a function that Excel doesn't do? Any suggestions? |
| 1441 | Not to be a buttinski, John, but I have been following along and replicated your data because I wanted to test and keep Jason's clever formula and it works find for me? I can send a file, if you'd like? Regards, "John Stout" <jstout@ibl.bm wrote in message news:3d23453d$0$27342$724ebb72@reader2.ash.ops.us.uu.net... Eeeeek! I spoke too soon. If I have today's date in cell A20, for serial # 20, and also in cell D1 for serial #21, the formula returns the code number for serial #40 as if today's date were in cell D20. I would want the code number for serial #21. How could I get around this? Thanks, John Jason Morin <jason.morin@us.exel.com wrote in message news:14c7201c222bc$7d926f60$b1e62ecf@tkmsftngxa04... Try: =INDIRECT(ADDRESS(MAX((NOT(ISERROR(FIND(TODAY(),A1:O20))) *ROW(1:20))),MAX((NOT(ISERROR(FIND(TODAY(),A1:O20)))*COLUMN (A1:O20)))+2)) Array-entered. I assumed the range was 20 rows (1-20) and 15 columns (A- O). HTH Jason Atlanta, GA -----Original Message----- I have a sheet with 3 columns: In col. B I have the numbers 1 to 100, in successive rows In col. C I have a different integer for each serial number, which I refer to as a code number. In col. A I enter today's date in the first blank row. Usually I use one serial number each day, but some days two or three serial numbers.. When I've used all the numbers, I delete the dates in col. A, and start again. On another sheet I want to return the value of the code number for today. I use the formula: VLOOKUP(NOW(), A1:A100,2). This works fine. I am able to print a document which has this reference number on it. Also if I enter today's date in two rows the formula returns the code corresponding to the more recent entry. Problem is I'd like to reconfigure the sheet with 3 columns and 100 rows so that I have 15 columns and 20 rows, and I can see all 100 serial numbers on one screen. I'd have the dates in col. A, the numbers 1-20 in column B, the code numbers in C, dates in col. D, numbers 21-40 in col. E, code number in col. F, and so on. This way I can enter the date straight away rather than have to go down the screen. It looks nicer and has a more compact feel to it. However I don't know how I can use the VLOOKUP in these circumstances. How can I return the code number, 2 cells to the right of today's date? I'd be grateful for any ideas. Thanks, John Stout . |
| 1442 | Cathryn Excel time can be formatted the way I think you want it by going to the Format Cells dialog and choosing the custom format [h]:mm:ss. You can amend this to [h]:mm if you don't want seconds. You should now be able to do arithmetic on the entries. HTH Mike "Cathryn" <cmeaders@magellanhealth.com wrote in message news:14c4901c222c4$edf3fdc0$9ae62ecf@tkmsftngxa02... Why does Excel only allow the entry of time as a specific 'time of day' instead of a 'unit' of time? For example, in a spreadsheet which records how many hours per day each staff member is on the telephone, entering "5:25" into a cell will calculate as "5:25 AM". What I'm trying to record is not a time of day, but 5 hours and 25 minutes of time. Entering "15:25" calculates as "3:25 PM". Evaluating the actual value shows me that Excel has automatically converted it into a date serial number (i.e. formatting the same value as a date gives me "1/0/1900 3:25 PM") To abate the confusion this causes, I've always suggested my coworkers separate 'time' data-entry into three columns: Hours, Minutes, and Seconds. Then summing or averaging the data of course requires a formula which adds each column and divides by 60 or 3600 accordingly to convert to hours. Then a separate formula is required to re-convert it to hours, minutes and seconds. Am I just not seeing how to enter time as a unit or is this a function that Excel doesn't do? Any suggestions? |
| 1444 | That'd be great, thanks. I've probably done something wrong. Appreciate it. Regards, John Wilson <jwilson@wickes.com wrote in message news:eV$zFXsICHA.2628@tkmsftngp08... Not to be a buttinski, John, but I have been following along and replicated your data because I wanted to test and keep Jason's clever formula and it works find for me? I can send a file, if you'd like? Regards, "John Stout" <jstout@ibl.bm wrote in message news:3d23453d$0$27342$724ebb72@reader2.ash.ops.us.uu.net... Eeeeek! I spoke too soon. If I have today's date in cell A20, for serial # 20, and also in cell D1 for serial #21, the formula returns the code number for serial #40 as if today's date were in cell D20. I would want the code number for serial #21. How could I get around this? Thanks, John Jason Morin <jason.morin@us.exel.com wrote in message news:14c7201c222bc$7d926f60$b1e62ecf@tkmsftngxa04... Try: =INDIRECT(ADDRESS(MAX((NOT(ISERROR(FIND(TODAY(),A1:O20))) *ROW(1:20))),MAX((NOT(ISERROR(FIND(TODAY(),A1:O20)))*COLUMN (A1:O20)))+2)) Array-entered. I assumed the range was 20 rows (1-20) and 15 columns (A- O). HTH Jason Atlanta, GA -----Original Message----- I have a sheet with 3 columns: In col. B I have the numbers 1 to 100, in successive rows In col. C I have a different integer for each serial number, which I refer to as a code number. In col. A I enter today's date in the first blank row. Usually I use one serial number each day, but some days two or three serial numbers.. When I've used all the numbers, I delete the dates in col. A, and start again. On another sheet I want to return the value of the code number for today. I use the formula: VLOOKUP(NOW(), A1:A100,2). This works fine. I am able to print a document which has this reference number on it. Also if I enter today's date in two rows the formula returns the code corresponding to the more recent entry. Problem is I'd like to reconfigure the sheet with 3 columns and 100 rows so that I have 15 columns and 20 rows, and I can see all 100 serial numbers on one screen. I'd have the dates in col. A, the numbers 1-20 in column B, the code numbers in C, dates in col. D, numbers 21-40 in col. E, code number in col. F, and so on. This way I can enter the date straight away rather than have to go down the screen. It looks nicer and has a more compact feel to it. However I don't know how I can use the VLOOKUP in these circumstances. How can I return the code number, 2 cells to the right of today's date? I'd be grateful for any ideas. Thanks, John Stout . |
| 1446 | Thanks for your help. I used the custom format which did remove the "AM" from the cell (although it's still in the formula bar.) -----Original Message----- Cathryn Excel time can be formatted the way I think you want it by going to the Format Cells dialog and choosing the custom format [h]:mm:ss. You can amend this to [h]:mm if you don't want seconds. You should now be able to do arithmetic on the entries. HTH Mike "Cathryn" <cmeaders@magellanhealth.com wrote in message news:14c4901c222c4$edf3fdc0$9ae62ecf@tkmsftngxa02... Why does Excel only allow the entry of time as a specific 'time of day' instead of a 'unit' of time? For example, in a spreadsheet which records how many hours per day each staff member is on the telephone, entering "5:25" into a cell will calculate as "5:25 AM". What I'm trying to record is not a time of day, but 5 hours and 25 minutes of time. Entering "15:25" calculates as "3:25 PM". Evaluating the actual value shows me that Excel has automatically converted it into a date serial number (i.e. formatting the same value as a date gives me "1/0/1900 3:25 PM") To abate the confusion this causes, I've always suggested my coworkers separate 'time' data-entry into three columns: Hours, Minutes, and Seconds. Then summing or averaging the data of course requires a formula which adds each column and divides by 60 or 3600 accordingly to convert to hours. Then a separate formula is required to re-convert it to hours, minutes and seconds. Am I just not seeing how to enter time as a unit or is this a function that Excel doesn't do? Any suggestions? . |
| 1451 | In the destination cell, enter the SUM function (sigma tool). It will by default select certain cells. In the formula, between the ( ), delete whatever it selects and replace it with the addresses of the cells to be included in the total (click on each cell in turn). Separate each with a comma. Your formula should look something like this: =SUM(A3,B4,C5,D6,E7). Another way to do it is as follows: =A3+B4+C5+D6+E7. Since you aren't using a function you don't need ( ). But this sounds like what you tried to do and didn't like. "Edward" <strott@charis.co.uk wrote in message news:15fae01c222cb$19f24b10$35ef2ecf@TKMSFTNGXA11... I want to add together the values shown in various unconnected cells, and place the result in a chosen destination cell. When using Excel on my old Mac I would start by first inserting the equal (=)sign in a destination cell, and then click in sequence each cell containing a value, finally clicking the Tick/Acceptsign on the menu bar. But when I do this on my new PC with Excel 2000(by trying to aggregate each of the value cells by clicking in sequence)the destination cell only includes the value shown in the LAST cell clicked. i.e it ignores the other cells previously clicked.!! HOW CAN I DO THIS WITHOUT HAVING TO LABORIOUSLY PRESS THE 'ADD' button each time I click a cell??? Help! |
| 1452 | I usually type =sum( then use the mouse to point at a cell, then with my left hand hovering over the comma, I hit the comma. Then I point to the next cell. (and so on). If you're not using =sum() (maybe just =a1+b9+c13+d99), then maybe you can add the "+" icon to a spot close to the formula bar. Then =PointAtCe11-1(click + Icon)pointatcell-2...etc. The "+" icon can be found under Tools|customize|Commands Tab|Insert Category| (a little more than halfway down (xl2002)). Edward wrote: I want to add together the values shown in various unconnected cells, and place the result in a chosen destination cell. When using Excel on my old Mac I would start by first inserting the equal (=)sign in a destination cell, and then click in sequence each cell containing a value, finally clicking the Tick/Acceptsign on the menu bar. But when I do this on my new PC with Excel 2000(by trying to aggregate each of the value cells by clicking in sequence)the destination cell only includes the value shown in the LAST cell clicked. i.e it ignores the other cells previously clicked.!! HOW CAN I DO THIS WITHOUT HAVING TO LABORIOUSLY PRESS THE 'ADD' button each time I click a cell??? Help! -- Dave Peterson ec35720@msn.com |
| 1454 | Try: =SUM(A1:P1)/MAX(1,COUNT(A1:P1)-COUNTIF(A1:P1,0)) Aladin "Rob Pearce" <rob.pearce@consignia.com wrote in message news:1509d01c22376$0391e000$9ae62ecf@tkmsftngxa02... I want to average several figures (a1:p1), that may contain zeros. How do I discount the zeros from my average? Thanks, Rob. |
| 1458 | Wanda, Worksheets are stored in a collection object called, not surprisingly, Worksheets. You can access an item (a worksheet) in the Worksheets collection either by specifying its name or its position. For example, Dim WS As Worksheet Set WS = Worksheets("Sheet2") Set WS = Worksheets(2) As long as your sheets are in "normal order", the two Set statements are the same. For any worksheet, the Index property returns its position in the Worksheets collection (I'm making the assumption here that you do have other types of sheets, e.g., Charts, in the workbook). Therefore, the sheet that is "three sheets to the left" (not be confused with "three sheet to the wind") of the ActiveSheet can be referenced with code like Dim WS As Worksheet Set WS = Worksheets(ActiveSheet.Index - 3) Of course, the code will fail if the ActiveSheet isn't far enough to the right (the number passed to Worksheets must be between 1 and the number of sheets in the workbook -- otherwise, an error 9, "Subscript Out Of Range" will occur). For example, to copy the value from A1 on the sheet 3 to the left to the activesheet, you can use code like ActiveSheet.Range("A1").Value = _ Worksheets(ActiveSheet.Index - 3).Range("A1").Value Note also that the Worksheet object also has Previous and Next properties that return a reference to the previous and next sheets. Unfortunately, you cannot specify "how many" to go to either the left or right. Its always 1. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com chip@cpearson.com "wanda" <steele-wls@msn.com wrote in message news:153cc01c22381$3bef7f60$9be62ecf@tkmsftngxa03... I am trying to record a macro in a workbook. The step I am not figuring out is "to go to the 3rd worksheet previous, copy a cell amount, add a one factor and paste it into a given cell on the sheet I am working on". I can go to the cell reference easy, copy the information, add a 1 factor and paste in the new worksheet. What I cannot figure is how to go to the 3rd worksheet previous to the one I am on. If I just go there in my macro, then when I duplicate the three worksheets and bring "previous" information forward, I am still pulling the information from the original worksheet that I made the macro sequence from instead of the next newly created ones. For instance: worksheets 1, 2, 3 have data. I want to create a new set just like 1,2,3 and call them 4,5,6. 4,5,6 have columns where the ending figures from 1,2,3 carry forward. Then additional data is in 4,5,6 and new ending balances exist. Those new ending balances will then forward to new sheets 7,8,9. When I am in sheet 4 and want to grab the data from sheet one to feed into 4, if I run the macro again in sheet 7, it is grabbing the data from sheet 1 instead of the new cumulative in 4 because it is referencing the worksheet 1 instead of the new worksheet 4. When I copy the "set of 3 pages" to a new set and bring the ending balances forward, I need to pull the information from the previous 3rd page, not the original starter page. If you understand what I am trying to do, and can give me any information, or steer me to where I can find it great. |
| 1460 | Hi Ron, Another way: =SUM(A1:A8)/COUNTIF(A1:A8,"0") HTH Regards, Howard "Rob Pearce" <rob.pearce@consignia.com wrote in message news:1509d01c22376$0391e000$9ae62ecf@tkmsftngxa02... I want to average several figures (a1:p1), that may contain zeros. How do I discount the zeros from my average? Thanks, Rob. |
| 1463 | You could try this: =SUM(A1:P1)/COUNTIF(A1:P1,"<0") HTH RD "Rob Pearce" <rob.pearce@consignia.com wrote in message news:1509d01c22376$0391e000$9ae62ecf@tkmsftngxa02... I want to average several figures (a1:p1), that may contain zeros. How do I discount the zeros from my average? Thanks, Rob. |
| 1464 | Good ... but doesn't work with negatives! Regards, RD "L. Howard Kittle" <lhkittle@attbi.com wrote in message news:w%0V8.283094$6m5.251558@rwcrnsc51.ops.asp.att.net... Hi Ron, Another way: =SUM(A1:A8)/COUNTIF(A1:A8,"0") HTH Regards, Howard "Rob Pearce" <rob.pearce@consignia.com wrote in message news:1509d01c22376$0391e000$9ae62ecf@tkmsftngxa02... I want to average several figures (a1:p1), that may contain zeros. How do I discount the zeros from my average? Thanks, Rob. |
| 1471 | You could have XL automatically enter Jason's formula for you by simply selecting the cell under the last cell in the column you wish to total (B5) and then Double Click on the Sigma (AutoSum) icon in the formula bar. HTH RD "Jason Morin" <jason.morin@us.exel.com wrote in message news:1335001c22368$6acfcf10$a4e62ecf@tkmsftngxa06... Use SUBTOTAL when filtering: =SUBTOTAL(9,B2:B4) Happy 4th! HTH Jason Atlanta, GA -----Original Message----- Take a simple excel array as below :- name jan feb mar steve 4 4 5 paul 3 3 1 steve 6 4 2 totals 13 11 8 If you filter on name and then select steve you get :- name jan feb mar steve 4 4 5 steve 6 4 2 13 11 8 Fine but the totals are wrong - how can you filter and get :- name jan feb mar steve 4 4 5 steve 6 4 2 10 8 7 Thanks, Andy. . |
| 1493 | This has been posted by Dana DeLouis a few times. Sub TrailingMinus() ' = = = = = = = = = = = = = = = = ' Use of CDbl suggested by Peter Surcouf ' Program by Dana DeLouis, dana2@msn.com ' = = = = = = = = = = = = = = = = Dim rng As Range Dim bigrng As Range On Error Resume Next Set bigrng = Cells.SpecialCells(xlConstants, xlTextValues).Cells If bigrng Is Nothing Then Exit Sub For Each rng In bigrng.Cells rng = CDbl(rng) Next End Sub ===== There is an option in xl2002 that allows you to treat trailing minus signs as negatives. It's under the Advanced button on step 3 of the text to columns wizard. But I'm not sure if it was added in xl2k or xl2002. I do recall that it's not part of xl97 (but that doesn't help you!). If it is in xl2k, I bet that would be the quickest. If you do look, could you post back to let me know. Thanks. Patrick Ritter wrote: I am downloading reports from a mainframe into Excel 2000. The problem that I have is that in ther mainframe a negative number is shown like this "10-" instead of the standard "-10". When this data is put into Excel it is recognized as text instead of as a number. I have to manually delete the negative sign after the number and insert a negative in front of the number. I think that there must be a way to create a macro or something to be able to change all of the numbers to negative at the same time. This is very important to me and could save alot of time since some reports contain 500-600 negative numbers. Thank you very much for your help and I look forward to your reply. Sincerely, Patrick Ritter patrritt@hotmail.com -- Dave Peterson ec35720@msn.com |
| 1502 | I think you're right, particularly since as you put it, "excel plots the position of numbers on the grid," and I assume you're suggesting that it will not know what to plot a text string as, whetever that text string may be. So it instead just interprets a text string as zero. One of the things I thought of (as an experiment only) was using a value that would be extremely unlikely to be used otherwise in the datasheet in place of NA's (say 999999 for example), and then use custom number formatting as such: [=999999]"NA";0 That works in creating the desired effect, but it is obviously a nightmare for practical usage. It requires me using a duplicate and hidden data series so that we don't see that massive columns/lines that would create (that series data labels being the only visible sign of the series itself). Also EVERYONE else who uses and updates the sheet to recognize that this value is being used to refer to the char string "NA". It's silly, but a lot of times doing things like that leads us to a better understanding of how the software works and maybe even a better way of doing it. So, if its impossible I can sleep. I just thought maybe there was something I was missing. Thanks. -----Original Message----- I don't think this is possible. Excel plots the position of numbers on the grid. What should it use as the position of a point with the text-value NA? If you want to display the text, I think you'll need a macro to update the data labels. On Thu, 4 Jul 2002 15:35:17 -0700, "Bear" <db@billsfan.net wrote: I have been trying to get excel to plot the string "NA" as "NA" within a graph (say for a data label). The problem is that excel interprets "NA" as 0, and consequently plots all 0's as "NA." This is with the following formatting/equation applied to the data label: [="NA"]"NA";0 Does anyone know of a way to get Excel to plot "NA" within the data sheet as "NA" in the graph, without using linked text or text boxes which disconnect the data from the data sheet, while maintaing the ability to plot zeros as zeros? . |
| 1529 | that's a nice one Dave Regards Ron "Dave Peterson" <ec35720@msn.com schreef in bericht news:3D24D024.652E8951@msn.com... This has been posted by Dana DeLouis a few times. Sub TrailingMinus() ' = = = = = = = = = = = = = = = = ' Use of CDbl suggested by Peter Surcouf ' Program by Dana DeLouis, dana2@msn.com ' = = = = = = = = = = = = = = = = Dim rng As Range Dim bigrng As Range On Error Resume Next Set bigrng = Cells.SpecialCells(xlConstants, xlTextValues).Cells If bigrng Is Nothing Then Exit Sub For Each rng In bigrng.Cells rng = CDbl(rng) Next End Sub ===== There is an option in xl2002 that allows you to treat trailing minus signs as negatives. It's under the Advanced button on step 3 of the text to columns wizard. But I'm not sure if it was added in xl2k or xl2002. I do recall that it's not part of xl97 (but that doesn't help you!). If it is in xl2k, I bet that would be the quickest. If you do look, could you post back to let me know. Thanks. Patrick Ritter wrote: I am downloading reports from a mainframe into Excel 2000. The problem that I have is that in ther mainframe a negative number is shown like this "10-" instead of the standard "-10". When this data is put into Excel it is recognized as text instead of as a number. I have to manually delete the negative sign after the number and insert a negative in front of the number. I think that there must be a way to create a macro or something to be able to change all of the numbers to negative at the same time. This is very important to me and could save alot of time since some reports contain 500-600 negative numbers. Thank you very much for your help and I look forward to your reply. Sincerely, Patrick Ritter patrritt@hotmail.com -- Dave Peterson ec35720@msn.com |
| 1533 | Thanks Dave ... that is just the ticket. I have about 4 workbooks that have to be open ... I presume that I can call them "otherWkbk1" & "otherWkbkName1" , and so on? Follow up question ... Where do I put this to get it to run automatically when the template is open ? Can't find any reference to 'auto run', other than how to disable it. ( Is it just me, or is Excel help, not ? ) "Dave Peterson" <ec35720@msn.com wrote in message news:3D24467B.D1B3B382@msn.com... Here's one way: Option Explicit Sub testme() Dim otherWkbk As Workbook Dim otherWkbkName As String otherWkbkName = "book2.xls" Set otherWkbk = Nothing On Error Resume Next Set otherWkbk = Workbooks(otherWkbkName) On Error GoTo 0 If otherWkbk Is Nothing Then 'not found, so open Set otherWkbk = Workbooks.Open _ (Filename:="C:\my documents\excel\" & otherWkbkName) Else 'already open--don't do anything End If End Sub Brian Belliveau wrote: Can anyone guide me down the route of having a template, on being opened, check to see if another file (on which it depends for data) is open, and if not, open that file. Exel XP Thanks to all -- Dave Peterson ec35720@msn.com |