| Article | Body |
| 3 | Said Shades in alt.destroy.microsoft on Sun, 18 Mar 2001 19:27:20 -0800; "T. Max Devlin" <tmax@commercelinks.net wrote in message news:tm4abtgfsatb2vmeefdretb24uuhn6pudv@4ax.com... 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. Um... did MS exploit any workers? What works of others? [...] No, customers and software developers. Not anyone who worked for MS (as far as this discussion is concerned.) Don't be so pig-headed, and we might be able to have a discussion here. 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. That isn't the real jist of what Adam Smith was saying. Ayn Rand stated it pretty clearly in "Atlas Shrugged". [...] Bwah-ha-ha-ha-ha-ha. So much for your ability to not be pig-headed. You must have been born that way, to believe that Ayn Rand has anything informative or worthwhile to say about Adam Smith. -- T. Max Devlin *** The best way to convince another is to state your case moderately and accurately. - Benjamin Franklin *** |
| 5 | Um... what business reason does MS have of putting these products on Linux or Solaris? Sale of the products to potential customers who run Linux or Solaris, perhaps? What business reason does MS have for foregoing those profits, hmmm? And how well acquainted are you with the details of anti-trust law? SQL Server runs only on NT because MS has no reason to port it to Unix. Why would they? I thought you said they were a competitive company. Yet they seem to cringe from even the slightest hint of competition, so much so that even if they were to make money selling a product, they won't, because it might reduce the demand for some other of their product (which happens to enjoy a monopoly). Are you getting this, yet? I have seen Oracle port to NT and it was not very good (mem leaks, etc). SQL Server is by far superior from an Admin and developing perspective. You also may not be aware of this but in many ways large companies liked going to one company regarding a problem. Too many times if Oracle was screwing up they would blame it on the OS and vice versa. Also I do not believe your analysis is correct on "cringing" from competition. Check out the "Host Integration Service" from MS which allows you to integrate with CICS, DB2 among other things. Also there have been ODBC drivers since the dawn of time that allowed you to access Oracle databases. Besides, as many Linux and Unix people like to mention, Windows NT Server is not as strong in the server market as they are. SQL Server and NT are not enjoying a monopoly, just the desktop is and as I said SQL Server interoperates with many systems. There is no barrier of using it and if you do not like the OS you can simply use Oracle on your Unix. MS clients and web services will talk to them too (and CICS and DB2...). You make statements where MS doesn't interoperate but there are many counter arguments I can add that says MS works with many products, OS's and software. XML is just another example. Where do you see a cost benefit to port it to Unix? Ah, but the difficulty porting a Win32 program to Unix is entirely Microsoft fault, you see. Had they not made it so difficult, do you think they'd then no longer wish to forego those profits? And what business benefit was there, precisely, in making Win32 so different from the de facto standard OS? Well porting from any OS to another is not a simple task. Porting from VMS to Unix isn't and neither is porting from Win32 to Unix. Your claim that Unix is a defacto os would probably piss off some old VMS people who thinks Unix is full of shit. Also the defacto DESKTOP OS at the time was DOS and OS/2. Win32 came out of the old Windows API built on DOS and borrowed stuff from OS/2 considering MS developed part of it. All new stuff came in the OS came from Dave Cutler's group. Thinking that Unix is the center of the OS universe is maybe why there is so much animosity towards NT. I don't care as much because I personally think VMS was much better than any other OS. There are many OS features that NT added that Unix did not have at the time. My wife (a Unix and ex-VMS programmer) used to yell at me about how Winsock had "extra" calls above and beyond the Berkeley spec and she claimed it was because MS wanted to "change the standard". These extra calls she came to realize allowed you to develop socket code that support asynchronous IO better than the Berkeley spec (at that time) had. You may say argh and ugh over it but it is better and I do not have to use them if I do not want to. I can stick with Berkeley just fine or check the OS I am running on. Also my statement wasn't because the port would be too expensive but the support would be. Supporting software on multiple platform is expensive at best. MS had no experience with software on the Sun architecture. Continued support of the Intel architecture seems a better bet for them and their clients(though I hate it). As I said, I doubt the cost would justify the sales. MS did play with Unix in the mid 80's(I think). They owned a chunk of SCO and it did not do well enough so they stuck with what they knew. If there was a business demand to do so I am sure MS would for making money is what the game is about. In other words, you have no reason to believe MS would ever act competitively, but you believe they would. I think MS HAS and DOES act un-competitively in certain areas. I also KNOW Oracle and Sun are not strangers to this practice either. MS is a very easy target to hit but I contend that I have seen with my own eyes some of Oracle's actions too. Does that make me hate all software comapanies and I should jump to Open Source. No, it makes me ensure that I am not stupid enough to put myself in a state where I get suckered. I never like COM for it would have put me in that state. .NET looks better but the jury is still out. It runs on SOAP which is becoming a standard so we shall have to see. It makes more sense to have a product like SQL Server be accessible from other OS's. Why? MS doesn't make money selling other OSes. Aren't they simply forgoing profits from sale of Windows by making their server accessible from other OSes? No. Customers demand that data be accessible from heterogeneous environs. If they don't they (meaning MS) are screwed. When it makes business sense to interoperate MS will do it. Otherwise it is 3rd party time. SQL Server 2000 for example can stream query data in XML. Any client/server machine can basically get data from it. And what about should users of non-Windows OSes desire more than "can basically get data from it"? Complete interoperability. Do they do it? No. Does MS have good partial solutions? Yes, and it has gotten much better. In addition the DTC of SQL Server can support being part of a two phase commit using the industry XA standard. Hmmm... does it not make more business sense to optimize SQL Server on one OS and ensure it is accessible from other systems? The "run everywhere" mantra so far doesn't work too well IMHO. Are they selling databases or OSes? The "run everything" mantra so far doesn't work too well. Period. They are selling solutions to cutomers. The OS is part of it but there is so much more than just an OS that a corporation needs to run on. Selling just a technology only goes so far. Even MS fails at this (COM for example solved nothing and was a piece of crap). This is what the large corporations want more than anything else. If I can sell you a product that will create indexes and smart searches on documents(all formats) on Unix systems, NT systems, databases and other repositories AND it works well people will pay through the nose. I am not sure if Unix has it, MS has a first cut and they are coming out with another that is supposed to be better (though I heard the installation sucks). I brought up the DTC example because so many say MS doesn't interoperate and I have seen where they do more than others. Everyone thinks BG is evil but for all his money-making he is nothing compared to Ellison in my humble opinion. Not even close. Anyway thanks for a good response and for not calling me pig headed again... |
| 11 | Thank you Sahak and Andre I never would have succeeded without your assistance Andre Croteau <milandre@bigpond.com wrote in message news:D9wO8.12373$Hj3.40490@newsfeeds.bigpond.com... Ed, You could also try this formula: TEXT(ROUNDUP(MONTH(F17)/3,0),0)&" qtr "&TEXT(YEAR(F17),0) André "Ed Wauszkiewicz" <nredwz@yahoo.com wrote in message news:aedjaq$6adlh$1@ID-78737.news.dfncis.de... I have a date in cell f17 it's format is 06/14/02 I wish to use this date in cell g5 to indicate the second quarter of the current year that is 2 qtr 02 with the spaces. Is this possible? thanks for checking this post..Ed |
| 15 | I have an attendance form and a mileage travel form in excel 2000 format. Each requires a signature in the appropriate place. I've scanned my signature as a bitmap and when I paste it in the appropriate line I can't get rid of the background in that picture. This signature works fine in WORD but I can't find anything on excel. I've used the format picture command etc and can't loose the background to make it transparent so the signature line (actual line) will show through the background.. Any ideas or help would be appreciated. THanks |
| 17 | That's brilliant, fits perfectly with a little project I have. Thanks Andy "Debra Dalgleish" <dsd@contextures.com wrote in message news:3D0A6AF7.4030905@contextures.com... You can do this on the worksheet: 1. In cell B4, enter the number 1 2. In cell C4, enter the following formula: =REPT("y",B4*100) 3. Format cell C4 as Monotype Sorts font, and add a border to the cell. 4. Adjust the width of cell C4 to fit the bar. 5. Now, you can enter a percentage in cell B4 and the bar in cell C4 will be adjusted. Note: for a smaller thermometer, use x, and for a larger one, use z Peter Kretzman wrote: Does anyone know a product or available control that will let me display a visual for percent complete? A simple horizontal thermometer, filled accordingly, is what I was thinking of. Thanks, PK -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 24 | Jay, one way =A1+B1/24 format as hh:mm -- Regards, Peo Sjoblom "Jay Bonham" <jaybham62@earthlink.net wrote in message news:edPO8.115$6a.9@newsread1.prod.itd.earthlink.net... Hi. I'm trying to add hours duration to a start time to get an end time. my worksheet has a column A with a start time (lets say 4:00 AM) , column B is hours worked (lets say 4.25 hours) I want column C to add 4.25 hours on to 4:00 AM. (in this case it would be 8:15 AM). This seems like it should be easy - but i have tried just about everything and have not been sucessful. Thanks for your help! |
| 32 | Hi, Tom. I want to "set" the custom format for Textbox2. I am getting the value for Textbox2 from Textbox1. As I exit Textbox1, I give Textbox2 the value of (1-Textbox1.value). For example, I don't want to see "0.5" in Textbox2, but ".5". On the Excel spreadsheet, I have a custom NumberFormat ".0". I unsuccessfully tried Format for both Value and Text: Private Sub Textbox2_Change() Format (tbxTextbox2.Value), ".0" ' Format (tbxTextbox2.Text), ".0" End Sub and, also Private Sub Textbox1_Exit() Format (tbxTextbox2.value), ".0" End Sub Any ideas, Tom? Thanks for your help. "Tom Ogilvy" <twogilvy@msn.com wrote in message news:e##sjhVFCHA.1716@tkmsftngp07... for the most part, it should work. You have to use the Format command. If you are filling the textbox from the cell with code, it might be easier to do Textbox1.Text = Range("A1").Text You might want to post the specifics. Regards, Tom Ogilvy Splash <splash@mosquitonet.com wrote in message news:ugpfmqgvnqmec7@corp.supernews.com... I have defined a custom "NumberFormat" for my Excel cells. Is there a way to make that same custom format apply to a textbox in a userform that runs from an Excel macro? (I've asked the VBA group, with no responses). Thanks, guys. |
| 47 | The drop down messages appear from the main toolbar at the top of the screen, the one that says File, Edit, View, Insert, Format, etc. First one menu will appear and then another! "Debra Dalgleish" <dsd@contextures.com wrote in message news:3D0D45C1.4030400@contextures.com... Where do these dropdown menus appear -- in the cell when you select it? If so, it may be data validation. Choose DataValidation, then click the Clear All button to remove it. If that's not it, perhaps you could describe where they appear, and what's in the menus. Jay Lathrop wrote: For no reason at all various drop down menus keep appearing at random. Makes it very difficult to enter data. This does not happen on any other programs. Would appreciate any thoughts on how to correct the problem. Jay -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 48 | hi all, i have been looking at this for an hour or so, and either ive lost my marbles or excel is trying to wind me up; in cell a1 i have value 569212145 in cell a2 i have value 569211864 why does the formula =IF(A1A2,"YES","NO") give NO???? both cells are format general, is there something completely glaringly obvious that I am missing? |
| 52 | Dear all, when I write in a cell '1-December' , It automatically changes it to '1-Dec' . how can I prevent Excel from doing so? By the way.... I have checked in the format option for cells, and I can change it to 1-December-2002 , but this is not what I want. I simply want it to change to 1-December!! Thanks a million, Yass :) |
| 54 | Cell A2 is probably formatted as text. or the value was entered with a leading apostrophe. Format the cell as General or another numeric format, edit the cell (F2) and hit enter. In article <f36b01c215f9$94863aa0$19ef2ecf@tkmsftngxa01, Dave <pepperds@lycos.co.uk wrote: hi all, i have been looking at this for an hour or so, and either ive lost my marbles or excel is trying to wind me up; in cell a1 i have value 569212145 in cell a2 i have value 569211864 why does the formula =IF(A1A2,"YES","NO") give NO???? both cells are format general, is there something completely glaringly obvious that I am missing? |
| 55 | One possible way, with your string in A1 =TRIM(SUBSTITUTE(MID(A1,FIND("/",A1)-2,10),".",""))*1 format result as mm/dd/yy this will work if there is always a space before the date and the date string itself is not less than 8 characters Regards, Peo Sjoblom 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 !!! . |
| 56 | Hi Yass, Highlight the cell(s) concerned. Then, a. Click FormatCells b. Click 'Number' tab, select 'Custom' under Category c. Click inside the box for 'Type', enter d-mmmm. Click OK. hth Max -----Original Message----- Dear all, when I write in a cell '1-December' , It automatically changes it to '1-Dec' .. how can I prevent Excel from doing so? By the way.... I have checked in the format option for cells, and I can change it to 1-December-2002 , but this is not what I want. I simply want it to change to 1-December!! Thanks a million, Yass :) . |
| 60 | Try: Format/Cells/Number/Custom d-mmmm In article <aekmad$ca7@news.emirates.net.ae, Yass <gol_e_yass@yahoo.com wrote: Dear all, when I write in a cell '1-December' , It automatically changes it to '1-Dec' . how can I prevent Excel from doing so? By the way.... I have checked in the format option for cells, and I can change it to 1-December-2002 , but this is not what I want. I simply want it to change to 1-December!! Thanks a million, Yass :) |
| 62 | Could you be accidentally hitting the F10 key instead of the equal sign key? That would activate the menus. Jay Lathrop wrote: The drop down messages appear from the main toolbar at the top of the screen, the one that says File, Edit, View, Insert, Format, etc. First one menu will appear and then another! "Debra Dalgleish" <dsd@contextures.com wrote in message news:3D0D45C1.4030400@contextures.com... Where do these dropdown menus appear -- in the cell when you select it? If so, it may be data validation. Choose DataValidation, then click the Clear All button to remove it. If that's not it, perhaps you could describe where they appear, and what's in the menus. Jay Lathrop wrote: For no reason at all various drop down menus keep appearing at random. Makes it very difficult to enter data. This does not happen on any other programs. Would appreciate any thoughts on how to correct the problem. Jay -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 65 | Thanx!! This works great. "Peo Sjoblom" <terre08@mvps.org wrote in message news:f3a501c21600$5a94fdc0$19ef2ecf@tkmsftngxa01... One possible way, with your string in A1 =TRIM(SUBSTITUTE(MID(A1,FIND("/",A1)-2,10),".",""))*1 format result as mm/dd/yy this will work if there is always a space before the date and the date string itself is not less than 8 characters Regards, Peo Sjoblom 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 !!! . |
| 70 | Hi! I have posted about this problem once before and got some very useful information as a reply, but I am still having trouble with making it work. As I am not an experienced Excel User, I am still trying to learn 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 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. 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 compare the DoNotCallList to the Master List, and to either flag or delete any matching numbers. The match will be done using the phone number, in an xxx-xxx-xxxx format. I have tried many different variations on the Match Function line 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 duplicates. I suppose what the trouble is that I am having is 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 files, and how I go about applying the line to the lists, 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 follows: =if(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 |
| 72 | I am using XL2000 and Win 98. I don’t know if this problem is a Macro issue or a general Excel issue, but I start with this news group. I get an error message, “Cannot paste that macro formula into a worksheet” when ever I try to copy and paste a cell or group of cells form one workbook to another. This only happens in workbooks with macros, however the message occurs regardless of whether the copied cell is references in a macro. I can use the Paste Special, Formulas but that loses formatting. The Paste Special, Values or Paste Special, Formats always yields the error message. The details of the error say that I have caused an invalid page fault in Excel.exe. I have seen other posts in which the same error seems to prevent the file from opening, but this is not my problem. Any advice appreciated. Ray Wright -- raycyn.wright@prodigy.net |
| 77 | I need help with exporting to excel from a jsp,I am able to achieve it using the code <%@ page contentType="application/vnd.ms-excel" % But i am facing a problem,which is ,although the desired table opens up in excel format on the browser ,it simultaneously opens up a separate excel window also. I am facing this problem only in IE. Please help me resolve this problem. |
| 82 | "Andy Walawender" <info@globalvillas.com wrote in message news:f92301c2160f$dc7dfd00$3bef2ecf@TKMSFTNGXA10... Does anyone known how to / whether it is possible to insert text into a column of already filled cells. ie column of cells with numeric values to which I want to add the string "EUR " for euro in front of the the monetary values without deleting or clearing the cells. (There's hundreds of them so doing each one manually is pretty tedious !!) Thanks for yr help Andy You could create a custom format and use the euro symbol (this means your numbers will stay as numbers) alternatively, add a column and do something like: ="EUR "&A1 in the new row, drag it down then copy and paste special: Values to fix the new column Doug |
| 84 | How about formatting using the Euro symbol? formatcustom [$?-1]#,##0.00 or if you want EUR "EUR"#,##0.00 Regards, Peo Sjoblom Does anyone known how to / whether it is possible to insert text into a column of already filled cells. ie column of cells with numeric values to which I want to add the string "EUR " for euro in front of the the monetary values without deleting or clearing the cells. (There's hundreds of them so doing each one manually is pretty tedious !!) Thanks for yr help Andy . |
| 85 | Try selecting the column, right-click, Format Cells, Number tab, Custom and insert: "Eur" #,##0_);"Eur" (#,##0) HTH Jason Atlanta, GA -----Original Message----- Does anyone known how to / whether it is possible to insert text into a column of already filled cells. ie column of cells with numeric values to which I want to add the string "EUR " for euro in front of the the monetary values without deleting or clearing the cells. (There's hundreds of them so doing each one manually is pretty tedious !!) Thanks for yr help Andy . |
| 86 | 1. Select the cells 2. Choose FormatCells 3. On the Number tab, choose Currency or Accounting 4. Select the number of decimal places, and negative number formatting that you prefer; select $ as the Symbol. 5. Choose the Custom Category 6. In the Type box, replace each $ with: "EUR " 7. Click OK Andy Walawender wrote: Does anyone known how to / whether it is possible to insert text into a column of already filled cells. ie column of cells with numeric values to which I want to add the string "EUR " for euro in front of the the monetary values without deleting or clearing the cells. (There's hundreds of them so doing each one manually is pretty tedious !!) Thanks for yr help Andy -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 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 !!! |
| 88 | Note: If you have Excel 2000 or Excel 2002, EUR is one of the available currency symbols, so steps 5 and 6 are not required. Debra Dalgleish wrote: 1. Select the cells 2. Choose FormatCells 3. On the Number tab, choose Currency or Accounting 4. Select the number of decimal places, and negative number formatting that you prefer; select $ as the Symbol. 5. Choose the Custom Category 6. In the Type box, replace each $ with: "EUR " 7. Click OK Andy Walawender wrote: Does anyone known how to / whether it is possible to insert text into a column of already filled cells. ie column of cells with numeric values to which I want to add the string "EUR " for euro in front of the the monetary values without deleting or clearing the cells. (There's hundreds of them so doing each one manually is pretty tedious !!) Thanks for yr help Andy -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 96 | Is there any way to have a custom format have a hard return. I want to have the date displayed as ddd dd/mm/yy, but I don't want the column to be wide, I want the day of the week to be on one line and the date on the next line of the cell. Like this: Mon Tues 6/17/02 6/18/02 Ethan |
| 103 | Is there a way to make all empty cells in a column be formatted as a color, and when they are no longer empty, that that color be removed (and they have no color)? Thanks, guys. |
| 105 | Select Column A, for example, with A1 active (i.e., A1 in the name box on the formula bar). Choose Format/Conditional Formatting and enter, using the dropdowns and textbox: Formula Is =ISBLANK(A1) Click on Format/Pattern, choose green, then OK, OK. In article <ugs5t2mk9oc42a@corp.supernews.com, Splash <splash@mosquitonet.com wrote: Is there a way to make all empty cells in a column be formatted as a color, and when they are no longer empty, that that color be removed (and they have no color)? Thanks, guys. |
| 127 | Hello! I'm creating a excel spreadsheet to import information into a SQL Server Database. I've got a few columns that I've used functions to create some of the data. Now here's my question: Is there a way to copy that value that's produced by a function to another cell, so that it could be imported into the database. For example, a formula I have on the spreadsheet is =A3+17, which gives me a value. I'd like to have that value copied into a cell, so that it could be imported into the database. Currently the cell hold the value =A3+17, I want the result, say 20, not the actual function =A3+17 in another cell so I can DTS it into my database. I hope this has made some sense. Thanks, Erica |
| 128 | Highlight the column format, conditional formatting cell value is equal to 0 format, patterns, choose green say OK. Barbara Splash <splash@mosquitonet.com wrote in message news:ugs5t2mk9oc42a@corp.supernews.com... Is there a way to make all empty cells in a column be formatted as a color, and when they are no longer empty, that that color be removed (and they have no color)? Thanks, guys. |
| 136 | I have unusual problem with printing more than one copy of a spread sheet from excell 2000/2002. When more than one copy is printed, the first copy prints, however the second copy prints very large and the formatting is wrecked as well. Sr 1 for xp applied and sr2 for 2000 applied. firm ware on printer is up to date as well. This function works on any onther printer except the one in question a 5si nx. Further, this is not a problem in excel off97, I can print multiple copies in office 97 |
| 139 | Because you are creating multiple inter-related sets of data (relational tables) for inventory, invoicing, payables, and jobs, MS Access is the optimum application to use. Access has the ability to create data entry and display forms, as well as reports that provide the specific management information structure desired. However, the flat tables in MS Excel, if constructed properly, can be related using MS Query, and reports can be generated with VBA Code and Userforms. -----Original Message----- Hi all I work for a small building business in the uk. We need a program that will moniter all our stock as well as the the jobs in progress ie: Moniter stock Moniter payments in(interim etc) Moniter payments out Moniter several ongoing jobs at once We've have had no luck as yet in our search (Iv'e been on google all day) I was wondering if it might be better to MS access or excel Any input most welcome Cheers Jill . |
| 140 | 1. Select the cells that you want to format 2. Choose FormatCells 3. On the Number tab, choose Custom 4. In the Type box, type ddd 5. Hold the Alt key, and on the number keypad, type 0010 6. Type dd/mm/yy 7. On the Alignment tab, choose Wrap Text, click OK 8. You'll have to manually adjust the height of the cells, as Autofit doesn't seem to work with this formatting. Ethan wrote: Is there any way to have a custom format have a hard return. I want to have the date displayed as ddd dd/mm/yy, but I don't want the column to be wide, I want the day of the week to be on one line and the date on the next line of the cell. Like this: Mon Tues 6/17/02 6/18/02 Ethan -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 147 | Is there an echo?? - Jon In article <3D0E5CB4.BCF0639C@msn.com, ec35720@msn.com says... How about this? You keep the date in a column (hidden??) and then use a formula that formats it and wraps it the way you like: =TEXT(A1,"ddd")&CHAR(10)&TEXT(A1,"mm/dd/yyyy") ethan wrote: After playing with it some more, I still have the same original problem. The width of the columns is determined by the format of the date as if the hard return was not there. So even though the format looks correct, if I shrink the column width down so that it is the width of just the date, I get ####. This makes the sheet too wide to fit on the page. Ethan "Debra Dalgleish" <dsd@contextures.com wrote in message news:3D0E4989.3050501@contextures.com... 1. Select the cells that you want to format 2. Choose FormatCells 3. On the Number tab, choose Custom 4. In the Type box, type ddd 5. Hold the Alt key, and on the number keypad, type 0010 6. Type dd/mm/yy 7. On the Alignment tab, choose Wrap Text, click OK 8. You'll have to manually adjust the height of the cells, as Autofit doesn't seem to work with this formatting. Ethan wrote: Is there any way to have a custom format have a hard return. I want to have the date displayed as ddd dd/mm/yy, but I don't want the column to be wide, I want the day of the week to be on one line and the date on the next line of the cell. Like this: Mon Tues 6/17/02 6/18/02 Ethan -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html -- Dave Peterson ec35720@msn.com |
| 174 | I've done many compound graphs like you describe. The trick is to create separate charts embedded on a worksheet. By careful use of formatting, alignment by snapping to cell intersections and grouping, I manage to get the desired effect. In your oscilloscope analogy, you would have 3 differing Y axis and want to share the X or Time axis. In other words, only the bottom time scale shows. I often do this by scaling all X axes identically on all charts then overlapping/hiding the axis for the top graphs. You might think you can just delete the unwanted axis, but the problem you will continually do battle with is Excel's distressing tendency to resize the plot area every time you alter axis formatting. You will want everything *exactly* the same except the Y axis and perhaps, the series displayed. Hint - no auto scaling/formatting whatsoever. No auto *anything*. Basically the procedure is this: 1) Create the bottom chart exactly as you would like to see it. 2) Fix (freeze) the axes by turning autoscaling off. 3) Copy/paste this chart for as many charts as you are going to stack. 4) Alter the Y scaling on the stacked charts as necessary. 5) Lay them out and glue them together (group) With a little effort, the results can be perfect. Thomas Bartkus "Microsoft" <edward@biebel.net wrote in message news:u8btS6uFCHA.2324@tkmsftngp04... Hi all: I have a question that has got me stumped and which I believe cannot done in Excel but figured I'd ask to be sure. I've been asked to construct a chart that has three different scales on it. However, the problem is not the multiple scales but the layout that is being requested. Essentially the person requesting the chart wants the bottom third of the chart done in one scale, the middle third done in a second scale and the top third in third scale. The only analogy that I can think of to describe the look he would like if from my HS physics days where you have an oscilloscope with different meter bands each with its own waveform stacked on top of each other. Anyone ever tried this and does this even sound possible? It didn't possible to me but you known how it goes when you get the "this should be relatively simple" requests. Thanks, Ed |
| 175 | Hi I often use vlookup looking up a key composed of 4 digits to find some associated data. Sometimes these keys and data are imported from access. The problem is that sometimes these 4 digits are treated as numbers and sometimes they are treated as text. Vlookup fails if the thing you are looking up is text but in the lookup table it is numeric (or it might be the other way round!) even though there is no way to tell the difference visually - especially if the data is centred so you cannot tell by the default justification. I get round this by adding a column with formulae something like = 1*A1 where A1 contains the text/numeric digits. This forces them all to numeric, then I copy/ paste special|values back over the original data. Do this for both source and lookup table and everything is OK. But.... There must be a better way !!! How can I force the CONTENTS of a cell as opposed to the display FORMAT to be numeric? Howard |
| 178 | Can you loop through each cell in the ranges and make them numeric this way Sub MakeNumeric() 'Multilpies each cell by 1 to make it numeric Dim DataRng As Range Dim cell As Range Set DataRng = Range("A2:A" & Range("A65536").End(xlUp).Row) For Each cell In DataRng cell = cell * 1 Next cell End Sub HTH? "Howard" <hsm@sentech.freeserve.co.uk wrote in message news:aeo1th$mbc$1@newsg3.svr.pol.co.uk... Hi I often use vlookup looking up a key composed of 4 digits to find some associated data. Sometimes these keys and data are imported from access. The problem is that sometimes these 4 digits are treated as numbers and sometimes they are treated as text. Vlookup fails if the thing you are looking up is text but in the lookup table it is numeric (or it might be the other way round!) even though there is no way to tell the difference visually - especially if the data is centred so you cannot tell by the default justification. I get round this by adding a column with formulae something like = 1*A1 where A1 contains the text/numeric digits. This forces them all to numeric, then I copy/ paste special|values back over the original data. Do this for both source and lookup table and everything is OK. But.... There must be a better way !!! How can I force the CONTENTS of a cell as opposed to the display FORMAT to be numeric? Howard |
| 179 | Howard, copy an empty cell, select your data (source and lookup table), do editpaste special and select add. That will make them all numeric.. -- Regards, Peo Sjoblom "Howard" <hsm@sentech.freeserve.co.uk wrote in message news:aeo1th$mbc$1@newsg3.svr.pol.co.uk... Hi I often use vlookup looking up a key composed of 4 digits to find some associated data. Sometimes these keys and data are imported from access. The problem is that sometimes these 4 digits are treated as numbers and sometimes they are treated as text. Vlookup fails if the thing you are looking up is text but in the lookup table it is numeric (or it might be the other way round!) even though there is no way to tell the difference visually - especially if the data is centred so you cannot tell by the default justification. I get round this by adding a column with formulae something like = 1*A1 where A1 contains the text/numeric digits. This forces them all to numeric, then I copy/ paste special|values back over the original data. Do this for both source and lookup table and everything is OK. But.... There must be a better way !!! How can I force the CONTENTS of a cell as opposed to the display FORMAT to be numeric? Howard |
| 182 | Thank you, but isn't this similar to what I do already. OK its a bit quicker but still seems a bit of a cheating way to do it. I was hoping there was some way of setting the data type like you can set the format (the lookup tables can be quite large) Howard "Peo Sjoblom" <terre08@mvps.org wrote in message news:OaVQcFwFCHA.2672@tkmsftngp13... Howard, copy an empty cell, select your data (source and lookup table), do editpaste special and select add. That will make them all numeric.. -- Regards, Peo Sjoblom "Howard" <hsm@sentech.freeserve.co.uk wrote in message news:aeo1th$mbc$1@newsg3.svr.pol.co.uk... Hi I often use vlookup looking up a key composed of 4 digits to find some associated data. Sometimes these keys and data are imported from access. The problem is that sometimes these 4 digits are treated as numbers and sometimes they are treated as text. Vlookup fails if the thing you are looking up is text but in the lookup table it is numeric (or it might be the other way round!) even though there is no way to tell the difference visually - especially if the data is centred so you cannot tell by the default justification. I get round this by adding a column with formulae something like = 1*A1 where A1 contains the text/numeric digits. This forces them all to numeric, then I copy/ paste special|values back over the original data. Do this for both source and lookup table and everything is OK. But.... There must be a better way !!! How can I force the CONTENTS of a cell as opposed to the display FORMAT to be numeric? Howard |
| 186 | Thank you, but this is essentially doing in code what I currently do by hand just copying down the formula =1*A1 and it would still need to be done with both the lookup table and any spreadsheets that use it. Surely there is a way to force cells to have a data type!? After all, what is the point of having a format command to show text or numeric if it doesn't really change the data type? Howard "Wilson" <jwilson@wickes.com wrote in message news:uL5oYAwFCHA.2552@tkmsftngp05... Can you loop through each cell in the ranges and make them numeric this way Sub MakeNumeric() 'Multilpies each cell by 1 to make it numeric Dim DataRng As Range Dim cell As Range Set DataRng = Range("A2:A" & Range("A65536").End(xlUp).Row) For Each cell In DataRng cell = cell * 1 Next cell End Sub HTH? "Howard" <hsm@sentech.freeserve.co.uk wrote in message news:aeo1th$mbc$1@newsg3.svr.pol.co.uk... Hi I often use vlookup looking up a key composed of 4 digits to find some associated data. Sometimes these keys and data are imported from access. The problem is that sometimes these 4 digits are treated as numbers and sometimes they are treated as text. Vlookup fails if the thing you are looking up is text but in the lookup table it is numeric (or it might be the other way round!) even though there is no way to tell the difference visually - especially if the data is centred so you cannot tell by the default justification. I get round this by adding a column with formulae something like = 1*A1 where A1 contains the text/numeric digits. This forces them all to numeric, then I copy/ paste special|values back over the original data. Do this for both source and lookup table and everything is OK. But.... There must be a better way !!! How can I force the CONTENTS of a cell as opposed to the display FORMAT to be numeric? Howard |
| 188 | Change the data type, before you run the macro. "Howard" <hsm@sentech.freeserve.co.uk wrote in message news:aeo4oe$er2$1@newsg2.svr.pol.co.uk... Thank you, but this is essentially doing in code what I currently do by hand just copying down the formula =1*A1 and it would still need to be done with both the lookup table and any spreadsheets that use it. Surely there is a way to force cells to have a data type!? After all, what is the point of having a format command to show text or numeric if it doesn't really change the data type? Howard "Wilson" <jwilson@wickes.com wrote in message news:uL5oYAwFCHA.2552@tkmsftngp05... Can you loop through each cell in the ranges and make them numeric this way Sub MakeNumeric() 'Multilpies each cell by 1 to make it numeric Dim DataRng As Range Dim cell As Range Set DataRng = Range("A2:A" & Range("A65536").End(xlUp).Row) For Each cell In DataRng cell = cell * 1 Next cell End Sub HTH? "Howard" <hsm@sentech.freeserve.co.uk wrote in message news:aeo1th$mbc$1@newsg3.svr.pol.co.uk... Hi I often use vlookup looking up a key composed of 4 digits to find some associated data. Sometimes these keys and data are imported from access. The problem is that sometimes these 4 digits are treated as numbers and sometimes they are treated as text. Vlookup fails if the thing you are looking up is text but in the lookup table it is numeric (or it might be the other way round!) even though there is no way to tell the difference visually - especially if the data is centred so you cannot tell by the default justification. I get round this by adding a column with formulae something like = 1*A1 where A1 contains the text/numeric digits. This forces them all to numeric, then I copy/ paste special|values back over the original data. Do this for both source and lookup table and everything is OK. But.... There must be a better way !!! How can I force the CONTENTS of a cell as opposed to the display FORMAT to be numeric? Howard |
| 197 | Just a question and guesses: Does excel blow up (with the same error) with any other workbook? If yes, then it's probably not this workbook. If no, then maybe the workbook is corrupted (like Dave suggested). I've had luck using xl2002 opening files that both xl97 and xl2k couldn't open. Sometimes formatting was lost, but the data/formulas/macros were there. Other people have reported that Sun's OpenOffice/StarOffice can sometimes open corrupted excel files. (It used to be between 80-110 meg download. I looked and decided that it's too big for my puny modem!) Not-for-free StarOffice. /software/download/desktop_app.html Free version /dev_docs/source/1.0.0/index.html IrelandA wrote: Dear All I have got a 3MB worksheet which I cannot open. When I try to open it, I get the following dialogue box: =================================== Application Error The instruction at "0x30099749f" referenced memory at "0x00000002". The memory could not be "read". Click on OK to terminate the application. Click on Cancel to debug the application. =================================== Even if I click on "Cancel", it doesn't open Excel, so I can't view the spreadsheet at all. Any help/advice gratefully received... Regards Alistair -- Dave Peterson ec35720@msn.com |
| 201 | You could perhaps, try using "helper" columns. For example, if your Vlookup formula is: =Vlookup(A1,C1:E100,2,0) Change it to =Vlookup(B1,C1:E100,2,0) Where B1 contains the formula =A1*1 Using the same concept, pre-establish a column with a similar formula to take care of the look-up array of your imported data. HTH RD "Howard" <hsm@sentech.freeserve.co.uk wrote in message news:aeo4fe$ceh$1@newsg4.svr.pol.co.uk... Thank you, but isn't this similar to what I do already. OK its a bit quicker but still seems a bit of a cheating way to do it. I was hoping there was some way of setting the data type like you can set the format (the lookup tables can be quite large) Howard "Peo Sjoblom" <terre08@mvps.org wrote in message news:OaVQcFwFCHA.2672@tkmsftngp13... Howard, copy an empty cell, select your data (source and lookup table), do editpaste special and select add. That will make them all numeric.. -- Regards, Peo Sjoblom "Howard" <hsm@sentech.freeserve.co.uk wrote in message news:aeo1th$mbc$1@newsg3.svr.pol.co.uk... Hi I often use vlookup looking up a key composed of 4 digits to find some associated data. Sometimes these keys and data are imported from access. The problem is that sometimes these 4 digits are treated as numbers and sometimes they are treated as text. Vlookup fails if the thing you are looking up is text but in the lookup table it is numeric (or it might be the other way round!) even though there is no way to tell the difference visually - especially if the data is centred so you cannot tell by the default justification. I get round this by adding a column with formulae something like = 1*A1 where A1 contains the text/numeric digits. This forces them all to numeric, then I copy/ paste special|values back over the original data. Do this for both source and lookup table and everything is OK. But.... There must be a better way !!! How can I force the CONTENTS of a cell as opposed to the display FORMAT to be numeric? Howard |
| 204 | If your VLOOKUP is of the exact-match type, you might want to have a look at a thread that discusses the issue you rise: /board/viewtopic.php?topic=10915&forum=2 Aladin "Howard" <hsm@sentech.freeserve.co.uk wrote in message news:aeo1th$mbc$1@newsg3.svr.pol.co.uk... Hi I often use vlookup looking up a key composed of 4 digits to find some associated data. Sometimes these keys and data are imported from access. The problem is that sometimes these 4 digits are treated as numbers and sometimes they are treated as text. Vlookup fails if the thing you are looking up is text but in the lookup table it is numeric (or it might be the other way round!) even though there is no way to tell the difference visually - especially if the data is centred so you cannot tell by the default justification. I get round this by adding a column with formulae something like = 1*A1 where A1 contains the text/numeric digits. This forces them all to numeric, then I copy/ paste special|values back over the original data. Do this for both source and lookup table and everything is OK. But.... There must be a better way !!! How can I force the CONTENTS of a cell as opposed to the display FORMAT to be numeric? Howard |
| 210 | You could use INDEX instead (array-entered: Ctrl+Shift+Enter) =INDEX($I$1:$I$18,MATCH(A1*1,$H$1:$H$18*1,0)) where the values to match are in column I and the value to be returned is in column H. If it doesn't find the value it will return an #N/A error. Howard wrote: Hi I often use vlookup looking up a key composed of 4 digits to find some associated data. Sometimes these keys and data are imported from access. The problem is that sometimes these 4 digits are treated as numbers and sometimes they are treated as text. Vlookup fails if the thing you are looking up is text but in the lookup table it is numeric (or it might be the other way round!) even though there is no way to tell the difference visually - especially if the data is centred so you cannot tell by the default justification. I get round this by adding a column with formulae something like = 1*A1 where A1 contains the text/numeric digits. This forces them all to numeric, then I copy/ paste special|values back over the original data. Do this for both source and lookup table and everything is OK. But.... There must be a better way !!! How can I force the CONTENTS of a cell as opposed to the display FORMAT to be numeric? Howard -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 213 | Correction: where the values to match are in column H and the value to be returned is in column I. Debra Dalgleish wrote: You could use INDEX instead (array-entered: Ctrl+Shift+Enter) =INDEX($I$1:$I$18,MATCH(A1*1,$H$1:$H$18*1,0)) where the values to match are in column I and the value to be returned is in column H. If it doesn't find the value it will return an #N/A error. Howard wrote: Hi I often use vlookup looking up a key composed of 4 digits to find some associated data. Sometimes these keys and data are imported from access. The problem is that sometimes these 4 digits are treated as numbers and sometimes they are treated as text. Vlookup fails if the thing you are looking up is text but in the lookup table it is numeric (or it might be the other way round!) even though there is no way to tell the difference visually - especially if the data is centred so you cannot tell by the default justification. I get round this by adding a column with formulae something like = 1*A1 where A1 contains the text/numeric digits. This forces them all to numeric, then I copy/ paste special|values back over the original data. Do this for both source and lookup table and everything is OK. But.... There must be a better way !!! How can I force the CONTENTS of a cell as opposed to the display FORMAT to be numeric? Howard -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 215 | Chip Pearson has information on his web site that will help you find and tag duplicates. Combine your lists on one sheet, then use one of Chip's suggestions. /excel/duplicat.htm#TaggingDuplicates jvoortman wrote: 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? -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 224 | Hi Otto Thanks for replying. I realise that its quite complicated as Excel offers all sorts of features to scale the page according to print requirements. And that the geometry requirements of the screen is quite different to a printed page. I guess I'll just have to print out some kind of grid from a spreadsheet to work it all out. Its just a real pain when trying to position objects at a specific position on the page or set column width and row height to a specific mm measurement. In these days of WYSIWYG and DTP etc I hope they may be able to improve this aspect of printing spreadsheets. As I am so used to using DTP and CAD I am spoiled by being able to position text and objects exactly where I want and adjust many fine aspects of text formatting. I find it a bit archaic in the 21st century to have to resort to so much trial and error with many test prints and rulers! Ian. "Otto Moehrbach" <ottom@worldnet.att.net wrote in message news:eFrWdswFCHA.1744@tkmsftngp13... Ian What you will get when you print is determined by your printer driver more than anything else. To see what you will get, click File - Print Preview. HTH Otto "Ian Roberts" <Ian_Roberts@SPAMOFFBTinternet.com wrote in message news:aeo4nq$7of$1@nntp-m01.news.aol.com... Hi I'm trying to recreate a printed form in Excel. I'm just getting a bit confused trying to relate the screen row height and column width measurements to how it will appear printed on paper. Although it might say row height 20.25 (27 Pixels). 20.25 what? How many pixels =1cm. I'm sure this will vary according to screen res - but in order to speed up the process it would help me to have some guidelines. I hope its clear what I'm asking for. Thanks Ian |
| 237 | FWIW When mapping out new forms, two things that I find help me is: Making all columns a width of 5, And placing the "ToggleGrid" icon on my toolbar. The narrow columns give the form a "graph paper" appearance on the screen, so you can make small adjustments to text placement, while the ability to instantly toggle the grid off and on to see how the form will actually appear, is a big time saver. HTH RD "Ian Roberts" <Ian_Roberts@SPAMOFFBTinternet.com wrote in message news:aeoe9a$fra$1@nntp-m01.news.aol.com... Hi Otto Thanks for replying. I realise that its quite complicated as Excel offers all sorts of features to scale the page according to print requirements. And that the geometry requirements of the screen is quite different to a printed page. I guess I'll just have to print out some kind of grid from a spreadsheet to work it all out. Its just a real pain when trying to position objects at a specific position on the page or set column width and row height to a specific mm measurement. In these days of WYSIWYG and DTP etc I hope they may be able to improve this aspect of printing spreadsheets. As I am so used to using DTP and CAD I am spoiled by being able to position text and objects exactly where I want and adjust many fine aspects of text formatting. I find it a bit archaic in the 21st century to have to resort to so much trial and error with many test prints and rulers! Ian. "Otto Moehrbach" <ottom@worldnet.att.net wrote in message news:eFrWdswFCHA.1744@tkmsftngp13... Ian What you will get when you print is determined by your printer driver more than anything else. To see what you will get, click File - Print Preview. HTH Otto "Ian Roberts" <Ian_Roberts@SPAMOFFBTinternet.com wrote in message news:aeo4nq$7of$1@nntp-m01.news.aol.com... Hi I'm trying to recreate a printed form in Excel. I'm just getting a bit confused trying to relate the screen row height and column width measurements to how it will appear printed on paper. Although it might say row height 20.25 (27 Pixels). 20.25 what? How many pixels =1cm. I'm sure this will vary according to screen res - but in order to speed up the process it would help me to have some guidelines. I hope its clear what I'm asking for. Thanks Ian |
| 238 | In working with the data further, I find it is just the Age field that is affecting the macro. Even if I format it as General or Number, I cannot get the macro to work. If I type the same number into each cell that was copied there, the macro works fine. ???? "Dave Trop" <dmtrop@cox.net wrote in message news:53QP8.58314$Hn4.1998285@news1.east.cox.net... I have recorded a macro to sort, copy and paste text data (Name, Sex, Age) in a defined dynamic range onto another worksheet. If I actually type the data into the defined range, the macro works fine, but if I copy and paste the data from another workbook into the defined range, the macro does not recognize any of the data I have pasted. I have even typed a few sets of data in, then pasted (and even tried "paste special - values") a few sets beneath them, then typed in a few more below those and when I run the macro, only the typed-in data is recognized. Can anyone explain this to me? I thought pasted or pasted-special data was treated the same as data that was keyed in directly. Thank you for any assistance. -Dave Trop- dmtrop@cox.net |
| 278 | David, Thanks for the information and links to your excel pages. Ralph K. ~~~~~~~~~~~~~~~~~~~ -----Original Message----- Hi Ralph, You will have to include ISERROR i.e. =IF(ISERROR(myformula),"error note", myformula) Might look something like this, since all you need is the error indicator: =IF(ISERROR(SEARCH("Inactive",B12,1)),IF(TRIM(B12) ="","","active"),"inactive") HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel Macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Ralph K." <thermometer@excite.com wrote in message news:d987c0b7.0206190920.5d224e76@posting.google.com... I have two columns. Col A is blank. Col B will have something like: Alamance Community College Asheville-Buncombe Community College - Inactive Account Some cells will say Inactive. I want to flag those inactive entries by putting the word "Inactive' or "Active" in Col A. Here's my formula: =IF(SEARCH("Inactive",B12,1)<0,"inactive","active") It does put the word "inactive" in col A, but if the word "active" is not present, it gives me this: #VALUE! Any ideas? The FIND command does not work either. Thanks. . |
| 292 | You are super. Thanks for all your wonderful code. "" <dmcritchie@msn.com wrote in message news:#iu1L46FCHA.2272@tkmsftngp09... Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Column < 3 And Target.Column < 5 Then Exit Sub If UCase(Target.Value) = "XXX" Then _ Target.Value = "'" & Format(Date, "yyyy-mm-dd") End Sub Target is a variable name. See Help -- Index -- ByVal look at Sub Statement about half way down. That is about as well as I can answer what Target is you don't want to substitute activecell for anything there. The activecell for me would be target.offset(1,0) since the cursor goes down after entry (tools option) and if you want to change other cells you want to turn off EnableEvents Application.EnableEvents = False '--ooo coding ooo--- Application.EnableEvents = True Worksheet Events and Workbook Events /dmcritchie/excel/event.htm HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel Macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Splash" <splash@mosquitonet.com wrote in message news:uh19i93l24r367@corp.supernews.com... Now I'm trying something different. If every time I am in column 4 I want to check to see if the text is "XXX", and, if it is, to run a little code, do I replace "Text" with "Activecell"? [That is, in "ByVal Target As Excel.Range", have "ByVal activecell as Excel.range", and then throughout the code, where Target occurred put activecell??] If so, this opens up all possiblities. |
| 297 | Aladin, Thank you very much. I didn't know about that site. At least that gives a one line formula way of doing it. I'm still astounded that there is not a simple way of setting the data type from the menu (like one can in access). In a way, being able to change the FORMAT of a cell is even worse as then you lose any clues that you had to the cell's data tpye Thanks to all who replied Howard "Aladin Akyurek" <akyurek@xs4all.nl wrote in message news:aeoadf$k2t$1@news1.xs4all.nl... If your VLOOKUP is of the exact-match type, you might want to have a look at a thread that discusses the issue you rise: /board/viewtopic.php?topic=10915&forum=2 Aladin "Howard" <hsm@sentech.freeserve.co.uk wrote in message news:aeo1th$mbc$1@newsg3.svr.pol.co.uk... Hi I often use vlookup looking up a key composed of 4 digits to find some associated data. Sometimes these keys and data are imported from access. The problem is that sometimes these 4 digits are treated as numbers and sometimes they are treated as text. Vlookup fails if the thing you are looking up is text but in the lookup table it is numeric (or it might be the other way round!) even though there is no way to tell the difference visually - especially if the data is centred so you cannot tell by the default justification. I get round this by adding a column with formulae something like = 1*A1 where A1 contains the text/numeric digits. This forces them all to numeric, then I copy/ paste special|values back over the original data. Do this for both source and lookup table and everything is OK. But.... There must be a better way !!! How can I force the CONTENTS of a cell as opposed to the display FORMAT to be numeric? Howard |
| 299 | Is there any way to have Excel interpret a csv file with a leading zero field as a character field so that it does not drop the zero? In the csv file, I have the character field enclosed in double quotes but it still treats it as general. I realize I can add the zero back in using the custom format option but I don't want users to do that each time. Any help is greatly appreciated. Thanks, Timothy Finn |
| 302 | I did that with no success. The Age field is being used as Criteria for sorting. Does that make a difference? Thanks. -Dave Trop- "Tom Ogilvy" <twogilvy@msn.com wrote in message news:uYKh8z4FCHA.2296@tkmsftngp05... I suspect the number in the Age field is being stored as Text. Changing the format after the value is in the cell will not change how it is stored. Format the cells as general before you do the paste special (although a straight paste should work - as you state). Regards, Tom Ogilvy "Dave Trop" <dmtrop@cox.net wrote in message news:hyQP8.58441$Hn4.2007994@news1.east.cox.net... In working with the data further, I find it is just the Age field that is affecting the macro. Even if I format it as General or Number, I cannot get the macro to work. If I type the same number into each cell that was copied there, the macro works fine. ???? "Dave Trop" <dmtrop@cox.net wrote in message news:53QP8.58314$Hn4.1998285@news1.east.cox.net... I have recorded a macro to sort, copy and paste text data (Name, Sex, Age) in a defined dynamic range onto another worksheet. If I actually type the data into the defined range, the macro works fine, but if I copy and paste the data from another workbook into the defined range, the macro does not recognize any of the data I have pasted. I have even typed a few sets of data in, then pasted (and even tried "paste special - values") a few sets beneath them, then typed in a few more below those and when I run the macro, only the typed-in data is recognized. Can anyone explain this to me? I thought pasted or pasted-special data was treated the same as data that was keyed in directly. Thank you for any assistance. -Dave Trop- dmtrop@cox.net |
| 307 | i'm trying to create a spreadsheet with 4 columns: red, green, blue, and color. the red, green, and blue cells are rgb values. i want the 4th cell to have its background color set to the rgb values in its row. help! this seems like it should be a simple thing to do in excel, but i can't find a way to make it happen. i've explored conditional formatting, script editing, and vb, but no luck. any suggestions on how to make this work? thanks! |
| 308 | Hi Will, Next time you may not be as fortunate as to wipe out an Excel builtin menu, it could have just as easily have been one of your own making. So I would highly recommend that you include backing your *.xlb file when you backup your data files. /dmcritchie/excel/backup.htm also suggest printing out a print screen shot of the top of your Excel window and one of your desktop as well so you can comfortably be assured that they look the same as before. More information on toolbars /dmcritchie/excel/toolbar.htm HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel Macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm |
| 311 | Excel is capable of displaying only 56 colors at a time (it's color palette). The default color palette contains 46 different colors - 10 of them are duplicated. If you use VBA to specify an RGB color that's not in the palette, Excel will use the closest match. In other words, if you write a macro to change a cell's color using RGB values, there is no guarantee that the actual color will be displayed. Here's a macro that will give you some info about the color palette in the active workboook. It adds a new worksheet and then write six columns of data. Sub DisplayColorPalette() Dim r As Long Worksheets.Add Range("A1") = "Index" Range("B1") = "Color" Range("C1") = "Value" Range("D1") = "Red" Range("E1") = "Green" Range("F1") = "Blue" Range("A1:F1").Font.Bold = True For r = 2 To 57 Cells(r, 1) = r - 1 Cells(r, 2).Interior.Color = ActiveWorkbook.Colors(r - 1) Cells(r, 3) = ActiveWorkbook.Colors(r - 1) Cells(r, 4) = ActiveWorkbook.Colors(r - 1) \ 256 ^ 0 And 255 Cells(r, 5) = ActiveWorkbook.Colors(r - 1) \ 256 ^ 1 And 255 Cells(r, 6) = ActiveWorkbook.Colors(r - 1) \ 256 ^ 2 And 255 Next r End Sub John Walkenbach For Excel tips, macros, & downloads... -walk.com/ss "Work Fast Party Horror" <news@busymonster.com wrote in message news:tQ5Q8.360$bK.67166@news.uswest.net... i'm trying to create a spreadsheet with 4 columns: red, green, blue, and color. the red, green, and blue cells are rgb values. i want the 4th cell to have its background color set to the rgb values in its row. help! this seems like it should be a simple thing to do in excel, but i can't find a way to make it happen. i've explored conditional formatting, script editing, and vb, but no luck. any suggestions on how to make this work? thanks! |
| 314 | You can put an apostrophe (') in front of the zero, but then Excel will treat the entry as text. "Timothy Finn" <timothy.finn@nytssc.com wrote in message news:ea8001c217cb$784eb820$2ae2c90a@hosting.microsoft.com... Is there any way to have Excel interpret a csv file with a leading zero field as a character field so that it does not drop the zero? In the csv file, I have the character field enclosed in double quotes but it still treats it as general. I realize I can add the zero back in using the custom format option but I don't want users to do that each time. Any help is greatly appreciated. Thanks, Timothy Finn |
| 323 | Thanks for the info. (50 meg might be worth it to find out if it can open files). Ron de Bruin wrote: 50 mb Dave I have download it for my brother two weeks ago.(10 minuts that is better than your puny modem) O yes I forgot somthing <vbg Regards Ron "Dave Peterson" <ec35720@msn.com schreef in bericht news:3D0FA860.66014496@msn.com... Just a question and guesses: Does excel blow up (with the same error) with any other workbook? If yes, then it's probably not this workbook. If no, then maybe the workbook is corrupted (like Dave suggested). I've had luck using xl2002 opening files that both xl97 and xl2k couldn't open. Sometimes formatting was lost, but the data/formulas/macros were there. Other people have reported that Sun's OpenOffice/StarOffice can sometimes open corrupted excel files. (It used to be between 80-110 meg download. I looked and decided that it's too big for my puny modem!) Not-for-free StarOffice. /software/download/desktop_app.html Free version /dev_docs/source/1.0.0/index.html IrelandA wrote: Dear All I have got a 3MB worksheet which I cannot open. When I try to open it, I get the following dialogue box: =================================== Application Error The instruction at "0x30099749f" referenced memory at "0x00000002". The memory could not be "read". Click on OK to terminate the application. Click on Cancel to debug the application. =================================== Even if I click on "Cancel", it doesn't open Excel, so I can't view the spreadsheet at all. Any help/advice gratefully received... Regards Alistair -- Dave Peterson ec35720@msn.com -- Dave Peterson ec35720@msn.com |
| 327 | One more slight change: =VLOOKUP(A2,INDIRECT("'"&A1&"'!a1:b100"),2,0) When I tested it, I put 'Jan-02 in A1. Excel liked to have this surrounded by single quotes. And the single quotes won't hurt if you really don't need them. === One more thing, do you really have the text Jan-02 in A1? If yes, then you can ignore the rest. But if you have a date in A1 (say 01/31/2002) and it's formatted to show just the month-YY, then you might have to do something like: =VLOOKUP(A2,INDIRECT("'"&text(A1,"mmm-yy")&"'!a1:b100"),2,0) wrote: Hi Brian, and Dave, Correction to original formula and modified formula The range must include Column A through Column B in order to refer to column 2 in the table. =vlookup(A2,INDIRECT(A1&"!a1:b100"),2,0) Testing shows failure to include column B results in #REF! error. "" <DavidH@OzGrid.com wrote ... Hi Brian, vlookup(a2,INDIRECT(A1&"!a1:a100"),2,0) "Brian Ferris" <brian.ferris@go.com.mt wrote ... | I would like to use a Vlookup, but I would like the name | of the sheet to vlookup to be a variable: | | eg. | Cell a1= Jan-02 | Cell a2= Brian | | Therefore | Cell a5= =vlookup(a2,'Jan-02'!a1:a100,2,0) | | If cell a1= Feb-02 | Therefore | Cell a5= =vlookup(a2,'Feb-02'!a1:a100,2,0) | | I would like to reference the sheet to look into with cell | a1. I know this can be done with the Indirect Funcion | somehow. -- Dave Peterson ec35720@msn.com |
| 330 | Format the cell as text *before* you enter a cheque number. "Ted Heath" <tedheath@xtra.co.nz wrote in message news:Hf6Q8.524$K13.60733@news.xtra.co.nz... Hi, I have Excel version 4 for a list of cheques banked each month. It is the column which has the cheque number which is the problem. One banks cheques start with 00 but when I type these in and push enter the zeroes disappear. Can anyone help please? Cheers Ted Heath mailto:tedheath@xtra.co.nz |
| 337 | Hello Debra, this is going to sound stupid, but I have been known to ask stupid questions or make stupid remarks, here goes it anyways. I started out this letter with telling you that unfortunately the form is right on top of the page, so I can't follow your instructions. Then I figured, this is easy all I have to do is move the form down, and then do what you suggested. When I tried to move the form it warned me that I will move some formats in certain cells. That scared me enough to abandon the idea. I guess I have to learn to either move, delete, cut and paste, and whatever the other options are to move the form on the page we/of getting an error message about formatting. Can you help me again please "Debra Dalgleish" <dsd@contextures.com wrote in message news:3D10F74B.1000501@contextures.com... To get rid of the border at the top of you form, select the cells in the row above the form, then click the No Border button on the borders dropdown. I'm not sure how you're trying to set up the rows in your form. If you want to start a new page at a specific row, you can select that row, then choose InsertPage Break. PeterM wrote: I made a form that I use daily, I wish I could get rid of the to border. When I go to borders, I look at the pictures of borders, and I don't see the no top border setup. Do the pictures of the border mean that if I click on them they will be there, one after another. IE click on the left border first, then the right border, the bottom border, but I don't click on the top one. Maybe I'm looking at it the wrong way..................... I like to sneak in another question. My form has lots of rows. I took 6 of the rows and made them 40 in size. I thought that it would push the extra rows of the bottom of the first page, it made the rows real tight on the first page though. Is there a way to just run them off the bottom instead of using the same space, and not crunching the rest of the rows? If it would flow into a second page, I guess that would be OK, I could just delete them. I hope.Many thanks...........Peter -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 342 | Hi Peter, Instead of moving your form, try the following: 1. Select the cells at the top of the form (the ones from which you want to remove the top border 2. Choose FormatCells 3. On the Border tab, you'll see a diagram of your cells with borders: ________________________ | Text Text | 4. Click on the line at the top of the diagram, to remove the border. 5. Click OK Debra PeterM wrote: Hello Debra, this is going to sound stupid, but I have been known to ask stupid questions or make stupid remarks, here goes it anyways. I started out this letter with telling you that unfortunately the form is right on top of the page, so I can't follow your instructions. Then I figured, this is easy all I have to do is move the form down, and then do what you suggested. When I tried to move the form it warned me that I will move some formats in certain cells. That scared me enough to abandon the idea. I guess I have to learn to either move, delete, cut and paste, and whatever the other options are to move the form on the page we/of getting an error message about formatting. Can you help me again please "Debra Dalgleish" <dsd@contextures.com wrote in message news:3D10F74B.1000501@contextures.com... To get rid of the border at the top of you form, select the cells in the row above the form, then click the No Border button on the borders dropdown. I'm not sure how you're trying to set up the rows in your form. If you want to start a new page at a specific row, you can select that row, then choose InsertPage Break. PeterM wrote: I made a form that I use daily, I wish I could get rid of the to border. When I go to borders, I look at the pictures of borders, and I don't see the no top border setup. Do the pictures of the border mean that if I click on them they will be there, one after another. IE click on the left border first, then the right border, the bottom border, but I don't click on the top one. Maybe I'm looking at it the wrong way..................... I like to sneak in another question. My form has lots of rows. I took 6 of the rows and made them 40 in size. I thought that it would push the extra rows of the bottom of the first page, it made the rows real tight on the first page though. Is there a way to just run them off the bottom instead of using the same space, and not crunching the rest of the rows? If it would flow into a second page, I guess that would be OK, I could just delete them. I hope.Many thanks...........Peter -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 346 | Dear Debra............ I tried it and it still has a border around it. Maybe it is not a border, could it be looking the same, but it is something else? When I click on the sequence of the suggested procedure, it shows no borders in the place where the text box is. The box that shows the borders when you click on the icon with the different border settings I mean. Is that darn thing maybe a frame or something. I just don't know how else to describe it. I hope you don't mind helping me some more. My senior heart appreciates you a lot.............Peter "Debra Dalgleish" <dsd@contextures.com wrote in message news:3D1109F5.8090302@contextures.com... Hi Peter, Instead of moving your form, try the following: 1. Select the cells at the top of the form (the ones from which you want to remove the top border 2. Choose FormatCells 3. On the Border tab, you'll see a diagram of your cells with borders: ________________________ | Text Text | 4. Click on the line at the top of the diagram, to remove the border. 5. Click OK Debra PeterM wrote: Hello Debra, this is going to sound stupid, but I have been known to ask stupid questions or make stupid remarks, here goes it anyways. I started out this letter with telling you that unfortunately the form is right on top of the page, so I can't follow your instructions. Then I figured, this is easy all I have to do is move the form down, and then do what you suggested. When I tried to move the form it warned me that I will move some formats in certain cells. That scared me enough to abandon the idea. I guess I have to learn to either move, delete, cut and paste, and whatever the other options are to move the form on the page we/of getting an error message about formatting. Can you help me again please "Debra Dalgleish" <dsd@contextures.com wrote in message news:3D10F74B.1000501@contextures.com... To get rid of the border at the top of you form, select the cells in the row above the form, then click the No Border button on the borders dropdown. I'm not sure how you're trying to set up the rows in your form. If you want to start a new page at a specific row, you can select that row, then choose InsertPage Break. PeterM wrote: I made a form that I use daily, I wish I could get rid of the to border. When I go to borders, I look at the pictures of borders, and I don't see the no top border setup. Do the pictures of the border mean that if I click on them they will be there, one after another. IE click on the left border first, then the right border, the bottom border, but I don't click on the top one. Maybe I'm looking at it the wrong way..................... I like to sneak in another question. My form has lots of rows. I took 6 of the rows and made them 40 in size. I thought that it would push the extra rows of the bottom of the first page, it made the rows real tight on the first page though. Is there a way to just run them off the bottom instead of using the same space, and not crunching the rest of the rows? If it would flow into a second page, I guess that would be OK, I could just delete them. I hope.Many thanks...........Peter -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 358 | Thanks for replying but not sure what you mean Aladin when I go to format I have the following options in the drop down menu number, alignment, font, border, pattern, cell protection, style, auto format, row height, column height, justify. Right click the cell same contents. As I said I use version 4. Thanks again Ted "Aladin Akyurek" <akyurek@xs4all.nl wrote in message news:aeqv9g$otj$1@news1.xs4all.nl... Format the cell as text *before* you enter a cheque number. "Ted Heath" <tedheath@xtra.co.nz wrote in message news:Hf6Q8.524$K13.60733@news.xtra.co.nz... Hi, I have Excel version 4 for a list of cheques banked each month. It is the column which has the cheque number which is the problem. One banks cheques start with 00 but when I type these in and push enter the zeroes disappear. Can anyone help please? Cheers Ted Heath mailto:tedheath@xtra.co.nz |
| 361 | I have MS Excel spreadsheets that have multiple worksheets copied to a disk and am unable to open these files in Microsoft Works Spreadsheet with all the worksheets. How do I get my files in their original format? |
| 362 | It's been a long time since version 4, but check this: Select your range (column?) and then Format|Cells|Number tab Do you see an option called Text in the left hand box? If yes, then click on it. Any new entries you make will keep the leading 0's. === If you didn't see text (but I bet you did???), you can always prefix your typing with a single quote. '0001 will look like 0001 in the cell. === It's been a lonnnnnnnggggggg time for xl4--so I could be completely off base. Ted Heath wrote: Thanks for replying but not sure what you mean Aladin when I go to format I have the following options in the drop down menu number, alignment, font, border, pattern, cell protection, style, auto format, row height, column height, justify. Right click the cell same contents. As I said I use version 4. Thanks again Ted "Aladin Akyurek" <akyurek@xs4all.nl wrote in message news:aeqv9g$otj$1@news1.xs4all.nl... Format the cell as text *before* you enter a cheque number. "Ted Heath" <tedheath@xtra.co.nz wrote in message news:Hf6Q8.524$K13.60733@news.xtra.co.nz... Hi, I have Excel version 4 for a list of cheques banked each month. It is the column which has the cheque number which is the problem. One banks cheques start with 00 but when I type these in and push enter the zeroes disappear. Can anyone help please? Cheers Ted Heath mailto:tedheath@xtra.co.nz -- Dave Peterson ec35720@msn.com |
| 366 | I think that typing an apostrophe at the beginning of the entry is the only way to enter a number as text in Excel 4.0 (At least it was the only option on the Mac version) Dave Peterson wrote: It's been a long time since version 4, but check this: Select your range (column?) and then Format|Cells|Number tab Do you see an option called Text in the left hand box? If yes, then click on it. Any new entries you make will keep the leading 0's. === If you didn't see text (but I bet you did???), you can always prefix your typing with a single quote. '0001 will look like 0001 in the cell. === It's been a lonnnnnnnggggggg time for xl4--so I could be completely off base. Ted Heath wrote: Thanks for replying but not sure what you mean Aladin when I go to format I have the following options in the drop down menu number, alignment, font, border, pattern, cell protection, style, auto format, row height, column height, justify. Right click the cell same contents. As I said I use version 4. Thanks again Ted "Aladin Akyurek" <akyurek@xs4all.nl wrote in message news:aeqv9g$otj$1@news1.xs4all.nl... Format the cell as text *before* you enter a cheque number. "Ted Heath" <tedheath@xtra.co.nz wrote in message news:Hf6Q8.524$K13.60733@news.xtra.co.nz... Hi, I have Excel version 4 for a list of cheques banked each month. It is the column which has the cheque number which is the problem. One banks cheques start with 00 but when I type these in and push enter the zeroes disappear. Can anyone help please? Cheers Ted Heath mailto:tedheath@xtra.co.nz -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 367 | -- --- HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel Macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Ted Heath" <tedheath@xtra.co.nz wrote in message news:Bj9Q8.584$K13.65138@news.xtra.co.nz... Thanks for replying but not sure what you mean Aladin when I go to format I have the following options in the drop down menu number, alignment, font, border, pattern, cell protection, style, auto format, row height, column height, justify. Right click the cell same contents. As I said I use version 4. Thanks again Ted "Aladin Akyurek" <akyurek@xs4all.nl wrote in message news:aeqv9g$otj$1@news1.xs4all.nl... Format the cell as text *before* you enter a cheque number. "Ted Heath" <tedheath@xtra.co.nz wrote in message news:Hf6Q8.524$K13.60733@news.xtra.co.nz... Hi, I have Excel version 4 for a list of cheques banked each month. It is the column which has the cheque number which is the problem. One banks cheques start with 00 but when I type these in and push enter the zeroes disappear. Can anyone help please? Cheers Ted Heath mailto:tedheath@xtra.co.nz |
| 369 | Hi Ted, format (menu) , number (tab), text BTW you can see everything in format, number, custom so if you have an accounting format that is close to what you want, you could modify the cell format afterwards in custom to fine tune it. "Ted Heath" <tedheath@xtra.co.nz wrote ... Thanks for replying but not sure what you mean Aladin when I go to format I have the following options in the drop down menu number, alignment, font, border, pattern, cell protection, style, auto format, row height, column height, justify. Right click the cell same contents. As I said I use version 4. "Aladin Akyurek" <akyurek@xs4all.nl wrote in message news:aeqv9g$otj$1@news1.xs4all.nl... Format the cell as text *before* you enter a cheque number. "Ted Heath" <tedheath@xtra.co.nz wrote in message news:Hf6Q8.524$K13.60733@news.xtra.co.nz... Hi, I have Excel version 4 for a list of cheques banked each month. It is the column which has the cheque number which is the problem. One banks cheques start with 00 but when I type these in and push enter the zeroes disappear. Can anyone help please? Cheers Ted Heath mailto:tedheath@xtra.co.nz |
| 371 | When I select range no problems I go to Format the drop menu is as I said : number, alignment, font, border, pattern, cell protection, style, auto format, row height, column height, justify. no CELLS as a menu item. Thanks for replying but I repeat I use version 4 (four) Cheers Ted Heath mailto:tedheath@xtra.co.nz "Dave Peterson" <ec35720@msn.com wrote in message news:3D1124BF.874C52FD@msn.com... It's been a long time since version 4, but check this: Select your range (column?) and then Format|Cells|Number tab Do you see an option called Text in the left hand box? If yes, then click on it. Any new entries you make will keep the leading 0's. === If you didn't see text (but I bet you did???), you can always prefix your typing with a single quote. '0001 will look like 0001 in the cell. === It's been a lonnnnnnnggggggg time for xl4--so I could be completely off base. Ted Heath wrote: Thanks for replying but not sure what you mean Aladin when I go to format I have the following options in the drop down menu number, alignment, font, border, pattern, cell protection, style, auto format, row height, column height, justify. Right click the cell same contents. As I said I use version 4. Thanks again Ted "Aladin Akyurek" <akyurek@xs4all.nl wrote in message news:aeqv9g$otj$1@news1.xs4all.nl... Format the cell as text *before* you enter a cheque number. "Ted Heath" <tedheath@xtra.co.nz wrote in message news:Hf6Q8.524$K13.60733@news.xtra.co.nz... Hi, I have Excel version 4 for a list of cheques banked each month. It is the column which has the cheque number which is the problem. One banks cheques start with 00 but when I type these in and push enter the zeroes disappear. Can anyone help please? Cheers Ted Heath mailto:tedheath@xtra.co.nz -- Dave Peterson ec35720@msn.com |
| 373 | Then I think I'd use the apostrophe. Ted Heath wrote: When I select range no problems I go to Format the drop menu is as I said : number, alignment, font, border, pattern, cell protection, style, auto format, row height, column height, justify. no CELLS as a menu item. Thanks for replying but I repeat I use version 4 (four) Cheers Ted Heath mailto:tedheath@xtra.co.nz "Dave Peterson" <ec35720@msn.com wrote in message news:3D1124BF.874C52FD@msn.com... It's been a long time since version 4, but check this: Select your range (column?) and then Format|Cells|Number tab Do you see an option called Text in the left hand box? If yes, then click on it. Any new entries you make will keep the leading 0's. === If you didn't see text (but I bet you did???), you can always prefix your typing with a single quote. '0001 will look like 0001 in the cell. === It's been a lonnnnnnnggggggg time for xl4--so I could be completely off base. Ted Heath wrote: Thanks for replying but not sure what you mean Aladin when I go to format I have the following options in the drop down menu number, alignment, font, border, pattern, cell protection, style, auto format, row height, column height, justify. Right click the cell same contents. As I said I use version 4. Thanks again Ted "Aladin Akyurek" <akyurek@xs4all.nl wrote in message news:aeqv9g$otj$1@news1.xs4all.nl... Format the cell as text *before* you enter a cheque number. "Ted Heath" <tedheath@xtra.co.nz wrote in message news:Hf6Q8.524$K13.60733@news.xtra.co.nz... Hi, I have Excel version 4 for a list of cheques banked each month. It is the column which has the cheque number which is the problem. One banks cheques start with 00 but when I type these in and push enter the zeroes disappear. Can anyone help please? Cheers Ted Heath mailto:tedheath@xtra.co.nz -- Dave Peterson ec35720@msn.com -- Dave Peterson ec35720@msn.com |
| 375 | thanks, this is a great start! but, boy, the palette size is an annoying limitation. some more questions: 1. can i set up a new color palette by assigning my own specific rgb values to the 56 colors in the palette? 2. can i change color palettes when i change worksheets? that way i could give the "illusion" of having more colors by only having 56 per page...? thanks, mark "John Walkenbach" <john@j-walk.com wrote in message news:uQxYnG9FCHA.1424@tkmsftngp04... Excel is capable of displaying only 56 colors at a time (it's color palette). The default color palette contains 46 different colors - 10 of them are duplicated. If you use VBA to specify an RGB color that's not in the palette, Excel will use the closest match. In other words, if you write a macro to change a cell's color using RGB values, there is no guarantee that the actual color will be displayed. Here's a macro that will give you some info about the color palette in the active workboook. It adds a new worksheet and then write six columns of data. Sub DisplayColorPalette() Dim r As Long Worksheets.Add Range("A1") = "Index" Range("B1") = "Color" Range("C1") = "Value" Range("D1") = "Red" Range("E1") = "Green" Range("F1") = "Blue" Range("A1:F1").Font.Bold = True For r = 2 To 57 Cells(r, 1) = r - 1 Cells(r, 2).Interior.Color = ActiveWorkbook.Colors(r - 1) Cells(r, 3) = ActiveWorkbook.Colors(r - 1) Cells(r, 4) = ActiveWorkbook.Colors(r - 1) \ 256 ^ 0 And 255 Cells(r, 5) = ActiveWorkbook.Colors(r - 1) \ 256 ^ 1 And 255 Cells(r, 6) = ActiveWorkbook.Colors(r - 1) \ 256 ^ 2 And 255 Next r End Sub John Walkenbach For Excel tips, macros, & downloads... -walk.com/ss "Work Fast Party Horror" <news@busymonster.com wrote in message news:tQ5Q8.360$bK.67166@news.uswest.net... i'm trying to create a spreadsheet with 4 columns: red, green, blue, and color. the red, green, and blue cells are rgb values. i want the 4th cell to have its background color set to the rgb values in its row. help! this seems like it should be a simple thing to do in excel, but i can't find a way to make it happen. i've explored conditional formatting, script editing, and vb, but no luck. any suggestions on how to make this work? thanks! |
| 380 | I have a problem that appears to be fairly widespread. I haven't, however, found a solution to my exact problem on this site... Here's the trouble. I have a system of files that I need to be able to transfer over to a different drive. I've read that as long as everything is in the same directory and the sub-directory structure and names of the sub-directories are left intact that Excel will pretend the references are relative (aka, I can move the files and they will work). Alas, it doesn't appear to be working for me. Here's the magnitude of data that I'm worried about: 26 directories, 25 of which are designed to collect data from different users (the whole reason this all isn't just one file anymore), and 1 directory to compile this information (and also serve as a source of a single piece of information for every file in the other directories). In each of the 25 directories, I will have 9 or 36 (undecided) files that I need to reference. The collection folder contains two files - fetcher (which gets the data for me) and sesdata (which the input files need to reference). The collection folder is named "master", the other inputs are all numbered. All these directories are in the directory "qpa." I try to move the directory containing both master and the input folders (qpa), and all my links which, if relative, would read "../master/sesdata" break. I would like to be able to design this system on my pc, record it to a CD, then copy it onto different computers. Due to the number of links, the edit links option simply won't work. I am willing to move the directory structure (to some extent), try VBA (throw code at me, I don't do Basic), or whatever. Or if I need to try to use Quattro Pro, tell me. |
| 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! |
| 384 | I think that excel gets the format for dates from a Windows setting. It sounds like one pc is different than the other and not recognizing the date. I'm in the US and if I type 12/31/2001, I get a date (expected). But if I type 31/12/2001, I get the text 31/12/2001--not really a date. On the "bad" pc, if you format the cell as General (Format|Cells|Number Tab), do you get a number? (I get 37621 for 12/31/2001 (using the 1900 date system). If you don't get a number, then that cell really doesn't have a date in it (no matter what it looks like). And since the value isn't numeric (it's a string), the =text() has no affect on it. If this looks like the case, then close excel and try: Start|Settings|Control Panel|Regional Settings Applet|Date Tab. Check (change to) the format. Start Excel. I'm not sure if it'll react to the change or if you'll have to change something for it to notice. Hit F2 and then enter on one of the cells. Did it fix your problem? If yes, maybe you can convert a whole column of dates at once. Select your column and do a Data|Text to columns. Follow the wizard and make sure you choose the correct (mdy?) format on step 3 of the wizard. Good luck, James Goodchild wrote: Hi all We have a Windows 2000 server running terminal services and Citrix. Because of a requirement from a custom built program, MS Office 2000 was installed to the local hard drive of the Citrix server as well as to the Citrix neighborhood. Everything loads fine and other than one formatting issue, everything seems to work. Here is the problem, in Excel 2000 in the local copy if you type the following date 12/31/01 in cell A1 and then enter the formula =TEXT(A1,"Mmm. Dd yyyy")in cell B2, the result displays as 12/31/01. It should display as Dec. 01 2001. If we try the same formula from Excel 2000 under the Citrix neighborhood install on the same server, it works fine. Unfortunately, the custom application is a reporting tool that makes calls to Excel to generate graphs and charts for the report and requires the local version. Anyone got any ideas on how to address the format issue? Thanks in advance. -- Dave Peterson ec35720@msn.com |
| 410 | Pct Profit = Profit / Revenue Format as Percent. Regards, Fred. "SkipperTW" <SkipperTW@yahoo.com wrote in message news:aeu1pe$s6p$1@slb6.atl.mindspring.net... Hello all, I hope someone can help me. My boss is all over me about figuring out how to create a cell in Excel that will tell me the total percent earned on a particular job. Simple example: $1500.00 in expenses $2500.00 in revenue = $1000.00 total profit (my question ???) What is the percent of profit on the job and how do I write a formula in a cell for this so she can use it in the future? My brain is fried and I can't figure out the formula. Any help would be great. Thanks, SkipperTW SkipperTW@yahoo.com |
| 415 | Select a cell or a range, then: <Format <Cells <Alignment tab In the "Horizontal" window, scroll down to "Fill" And click <OK Now, anything you type in the cell will fill it. HTH RD "David Bauer" <davidbauer60@hotmail.com wrote in message news:10f1601c218d4$a0f80f60$3aef2ecf@TKMSFTNGXA09... How does one fill a cell with repeating characters to the width of the cell? In Lotus 123 for instance, the backslash followed by the character(s) does it: "\-" would fill the cell with dashes, regardless of the width of the cell. |
| 421 | David This custom format will do the same in Excel: @*- -- Best regards Leo Heuser MVP Excel "David Bauer" <davidbauer60@hotmail.com skrev i en meddelelse news:10f1601c218d4$a0f80f60$3aef2ecf@TKMSFTNGXA09... How does one fill a cell with repeating characters to the width of the cell? In Lotus 123 for instance, the backslash followed by the character(s) does it: "\-" would fill the cell with dashes, regardless of the width of the cell. |
| 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? |
| 430 | Eric One way: Use this UDF (UserDefinedFunction) to get the date. Copy and paste the code to a general module (<Alt<F11 Insert Module) In a cell enter =creadate() Remember the empty parentheses. Format the cell as Date. -- Best regards Leo Heuser MVP Excel Function CreaDate() As Date CreaDate = ActiveWorkbook. _ BuiltinDocumentProperties("Creation date") End Function "Eric" <eric.goodrich@paccoast.com skrev i en meddelelse news:11c9701c218a8$83d85380$35ef2ecf@TKMSFTNGXA11... Is there a function in Excel that will return the date a file is created into a cell? |
| 434 | Is there a way that a macro can revert to the saved version of a spreadsheet? ie. If a user has made lots of chnages to the spreadsheet, then clicks a "clear" button it reverts to its original template format. thanks |
| 435 | Pauline, With your permission, I will email you an excel file that demonstrates the FORECAST and TREND function. The file also includes the GROWTH function, which should be used if your sales are increasing exponentially. Results from the FORECAST and TREND functions should be the same if sales are relatively flat (i.e. not exponential). A $10 million difference does not sound right. Both functions involve linear relationships between variables (linear problems can be represented by a straight line on a graph). General comments: 1. Using sales history for the past 5 years may not be representative of your sales going forward. You may consider reducing your history sample to say the past 12 months. 2. I recommend that you analyze your sales history for any unusual item, transaction or event that may have effected past sales e.g. new product launch, plant closure, strikes, government regulations (tariffs, changes in the taxation regime etc.). You may wish to excise the financial effects of these items from your sales history. 3. You may consider using Excel's CORREL function to correlate say advertising expenditure with sales. Forecasting sales is one thing, but will your company maintain its advertising expenditure? Finally, using an Excel function for forecasting purposes is fine; however, nothing beats a knowledgeable person who knows your market, customers and products. I suggest that you bring such a person into your forecasting exercise. In particular, you need top level information on proposed plant closures, new product etc. Regards Mike cbs@vianet.net.au www.cobi.com.au |
| 444 | HI, This could help, used in a other file. Sub RevertFile() Workbooks("Test2.xls").Activate ActiveWorkbook.Close SaveChanges:=False Workbooks.Open FileName:="C:\MYdocs\Test2.xls" End Sub HTH Paul "Et il en est qui s'étonnent encore qu'un message qui ne comporte ni bonjour, ni merci, ne recoive pas de réponse" (Lao Tseu Atch Oum) "Bruce" <bruce@nha.co.za a écrit dans le message de news: 11e8201c218ff$3286fe30$35ef2ecf@TKMSFTNGXA11... Is there a way that a macro can revert to the saved version of a spreadsheet? ie. If a user has made lots of chnages to the spreadsheet, then clicks a "clear" button it reverts to its original template format. thanks |
| 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 |
| 463 | How do you format a cell so when you have a minus figure it shows up as a figure in brackets (i.e. accounting way) i.e. (2,500 |
| 465 | From the Menu Format | Cells | Number [tab] select Accounting in list HTH "Emma H-Byass" <emmahbyass@btopenworld.com wrote in message news:10e1401c21925$355d0c50$9be62ecf@tkmsftngxa03... How do you format a cell so when you have a minus figure it shows up as a figure in brackets (i.e. accounting way) i.e. (2,500 |
| 466 | Hi Dave I guess I wasn't as clear as I could have been. The issue is on the smae computer - there are two instances of Excel on it, one loaded directly on the computer and the other loaded under Citrix on the same computer. I did check the international settings, but wasn't sure how this would effect one and not the other instance of Excel. Anyone else have any ideas? -----Original Message----- I think that excel gets the format for dates from a Windows setting. It sounds like one pc is different than the other and not recognizing the date. I'm in the US and if I type 12/31/2001, I get a date (expected). But if I type 31/12/2001, I get the text 31/12/2001--not really a date. On the "bad" pc, if you format the cell as General (Format|Cells|Number Tab), do you get a number? (I get 37621 for 12/31/2001 (using the 1900 date system). If you don't get a number, then that cell really doesn't have a date in it (no matter what it looks like). And since the value isn't numeric (it's a string), the =text() has no affect on it. If this looks like the case, then close excel and try: Start|Settings|Control Panel|Regional Settings Applet|Date Tab. Check (change to) the format. Start Excel. I'm not sure if it'll react to the change or if you'll have to change something for it to notice. Hit F2 and then enter on one of the cells. Did it fix your problem? If yes, maybe you can convert a whole column of dates at once. Select your column and do a Data|Text to columns. Follow the wizard and make sure you choose the correct (mdy?) format on step 3 of the wizard. Good luck, James Goodchild wrote: Hi all We have a Windows 2000 server running terminal services and Citrix. Because of a requirement from a custom built program, MS Office 2000 was installed to the local hard drive of the Citrix server as well as to the Citrix neighborhood. Everything loads fine and other than one formatting issue, everything seems to work. Here is the problem, in Excel 2000 in the local copy if you type the following date 12/31/01 in cell A1 and then enter the formula =TEXT(A1,"Mmm. Dd yyyy")in cell B2, the result displays as 12/31/01. It should display as Dec. 01 2001. If we try the same formula from Excel 2000 under the Citrix neighborhood install on the same server, it works fine. Unfortunately, the custom application is a reporting tool that makes calls to Excel to generate graphs and charts for the report and requires the local version. Anyone got any ideas on how to address the format issue? Thanks in advance. -- Dave Peterson ec35720@msn.com . |
| 474 | "Lepchen" wrote I REALLY need to know if how to "shrink to fit" in a cell. Format / Cells / Alignment / Shrink to fit Please email me the answer- I won't be checking the newsgroup... PLEASE! Then you haven't figured out what this Usenet thing's all about. |
| 475 | merci beaucoup! -----Original Message----- HI, This could help, used in a other file. Sub RevertFile() Workbooks("Test2.xls").Activate ActiveWorkbook.Close SaveChanges:=3DFalse Workbooks.Open FileName:=3D"C:\MYdocs\Test2.xls" End Sub HTH Paul "Et il en est qui s'=E9tonnent encore qu'un message qui ne=20 comporte ni bonjour, ni merci, ne recoive pas de r=E9ponse" (Lao=20 Tseu Atch Oum) "Bruce" <bruce@nha.co.za a =E9crit dans le message de=20 news: 11e8201c218ff$3286fe30$35ef2ecf@TKMSFTNGXA11... Is there a way that a macro can revert to the saved version of a spreadsheet? ie. If a user has made lots=20 of chnages to the spreadsheet, then clicks a "clear"=20 button it reverts to its original template format. thanks . |
| 480 | Hello. Please help. I want to pick a range of cells for a chart, but I want to do it automatically, by having the range reference a cell which contains the adress of the cell where the range should start. I have said cell adress in left-hand text format. Say, B15 contains the adress $A$11 as left hand text. I want the the range to be referenced to B15, which will make the range start at $A$11. Any ideas? Anticipated thanks, John |
| 489 | I am trying to troubleshoot a problem where the format cells dialog box will not appear when trying to format a cell with data in it or if the cell has had previous formatting applied to it. The dialog box appears normally when trying to format an empty cell or if the range selected included an empty cell. I've tried pasting the data into a clean workbook with no luck. Has anyone ever seen this behavior before? I am using Excel 97. Thanks in advance! Chris Lunde |
| 497 | Let me rephrase the first sentence: --Excel can only *open* Works spreadsheets that are saved as Works 2.0 files. (My thanks to the quality control team.) Debra Dalgleish wrote: Excel can only only Works spreadsheets that are saved as Works 2.0 files. Many computers come with a copy of MS Works. Maybe you can find someone who can open your file in Works, and save it as version 2.0. Then you'll be able to open it in Excel. Copy the file onto your hard drive -- don't work from the floppy disk, because it's very easy to damage the file that way. Connie wrote: I have an old but extensive Address Book made with a MicroSoft Works program that came with my computer. It is now only available on a floppy disk. I have tried to convert this floppy spread sheet file, to what appears to be the same spread sheet in Excel 97 (Book 1, Sheet 1) I've used MicroSoft Query with all the ODBC drivers installed but it will not recognize the .WKS OR .TXT format in which I have it. This is probably simple but so am I; I'm stumped! Please save me from a long input session with an old hard copy. |
| 498 | On the spreadsheet you have (old one) select all the data. copy. on the new spreadsheet - paste. did you try this? good luck. -----Original Message----- I have an old but detailed Address Book from an old Works program that came with the computer. I have tried to transfer this spread sheet file to Excel's Book1, Sheet 1 which appears to be the same spread sheet as that from Works. I've used MicroSoft Query with all the ODBC drivers and it will not recognize the .WKS or .TXT formats I can put it in. This is probably simple, like me, but I hope someone can save me the task of inputing all that data from an old hard copy. . |
| 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 |
| 502 | goto "insert" click on date&time select the format you want good luck -----Original Message----- So I am the first visitor. Can anybody tell me how to change the date format in footer. I am using WIN 98, Office 2000. Presently it is coming in dd-mm-yy format. The Regional Settings properties for date tab are as follow: dd-mmm-yy I want the date format to look like 21-Jun-02. Thanks in advance. Vittal . |
| 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 |
| 507 | I think you can use the acronym: QCT Debra Dalgleish wrote: Let me rephrase the first sentence: --Excel can only *open* Works spreadsheets that are saved as Works 2.0 files. (My thanks to the quality control team.) Debra Dalgleish wrote: Excel can only only Works spreadsheets that are saved as Works 2.0 files. Many computers come with a copy of MS Works. Maybe you can find someone who can open your file in Works, and save it as version 2.0. Then you'll be able to open it in Excel. Copy the file onto your hard drive -- don't work from the floppy disk, because it's very easy to damage the file that way. Connie wrote: I have an old but extensive Address Book made with a MicroSoft Works program that came with my computer. It is now only available on a floppy disk. I have tried to convert this floppy spread sheet file, to what appears to be the same spread sheet in Excel 97 (Book 1, Sheet 1) I've used MicroSoft Query with all the ODBC drivers installed but it will not recognize the .WKS OR .TXT format in which I have it. This is probably simple but so am I; I'm stumped! Please save me from a long input session with an old hard copy. -- Dave Peterson ec35720@msn.com |
| 508 | If cell a1 contains the date you are counting down to, then use: =a1-today() You need to format the cell as a number Tony "Pat" <glass_patrick@hotmail.com wrote in message news:af1ud3$auq$1@newsg1.svr.pol.co.uk... Hi, I want to place a countdown feature in excel to remind me how many days remain from a given date, can this be done? Pat |
| 509 | Hi Tony, Use this with the code you are already using: dname = "c:\mybackup\B" & Format(Now(), "yyyy_mmdd") HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel Macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "tony" <tony4fly@netscape.net wrote in message news:f6ba01c219ec$919bd380$37ef2ecf@TKMSFTNGXA13... I need help again. the macro saves a file with a given name. To the name I want to append today's date. What should be the command. Thanks a lot for the help. tony :-) |
| 511 | Dave, That's great, thanks. Is there a way I can ask for a password in order to run this macro - if not correct then it exits? Then I could leave this macro in the workbook and just protect the code. Rob. -----Original Message----- Hi Rob This code will toggle sheet protection on/off Sub ToggleProtectContent() Dim i As Integer For i = 3 To 50 If Sheets(i).ProtectContents = False Then Sheets(i).Protect Password:="rob1" Else Sheets(i).Unprotect Password:="rob1" End If Next i End Sub It only checks for Content protection. -- "Rob Pearce" <rob.pearce@consignia.com wrote in message news:f81c01c219bb$7dfb11a0$a4e62ecf@tkmsftngxa06... | I have a workbook with 50 worksheets. | | I protect sheets 3 to 50 [all being the same template for | data entry] with the same password. | | When I want to change some formula/formatting on these 48 | sheets I have to unprotect each sheet individually so that | I can eventually group them together to alter. | | Would it be possible to have some code that will loop from | shhet 3 to 50 and unprotect - the password is rob1 if | anyone could lend a hand - and conversely then to re | protect. | | I would obviously remove the macros from the workbook | afterwards! | | Thanks, | | Rob. . |
| 513 | Thanks very much for your help, now for the next question. can the time also include the date and decimal parts of a second? For example 06-22-02 08:17:54.379 , Chuck -----Original Message----- Hi Chuck You can copy paste special as values the Now() function over the top of itself, or use the simple macro: Push Alt+F11 Go to InsertModule Paste in the code below Push Alt+Q and Save Push Alt+F8 Click "AddTime" then Options assign a shortcut key Sub AddTime() ActiveCell = Time End Sub -- "chuck conroy" <cconroy@jps.net wrote in message news:115e801c219b4$76737140$39ef2ecf@TKMSFTNGXA08... | I would like to have Excel input the time in this format | hh:mm:ss | I have tried two different things. | 1. The now statement =now() this works fine however the | next time I enter it the previous time gets updated to the | new time. I want each cell to display the exact time that | is is entered and then to retain this time. | 2. I have also tried control shift : this does display | a different time in each cell, however it only displays | the hour and minute, the seconds are always 00 no mater | how I format the cell. | Does anyone have any suggestions? | Thanks, | Chuck Conroy . |
| 514 | Hi Ryan, One way would be to make it text by prefixing with a single quote. You could format the column as text. If the fraction will always be 26ths you could format as a custom format # ??/26 Text formats will not be numbers and will be left aligned by default. . HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel Macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Ryan" <oystreck@yahoo.ca wrote in message news:f6e601c219ff$777e8390$37ef2ecf@TKMSFTNGXA13... When entering a fraction into Excel, is there any way to prevent the fraction from going to lowest terms? For example, I am entering test scores, such as 13/26 and it breaks it down to 1/2. I need the data to read 13/26. Any help would be appreciated. |
| 516 | Try Sub EnterDate_Time() ActiveCell.Value = Now ActiveCell.NumberFormat = "mm-dd-yy hh:mm:ss" End Sub I don't think you can get excel to record less than a second although it is possible to enter in a cell.. -- Regards, Peo Sjoblom "chuck conroy" <cconroy@jps.net wrote in message news:1249c01c219ff$8b3e5c70$35ef2ecf@TKMSFTNGXA11... Thanks very much for your help, now for the next question. can the time also include the date and decimal parts of a second? For example 06-22-02 08:17:54.379 , Chuck -----Original Message----- Hi Chuck You can copy paste special as values the Now() function over the top of itself, or use the simple macro: Push Alt+F11 Go to InsertModule Paste in the code below Push Alt+Q and Save Push Alt+F8 Click "AddTime" then Options assign a shortcut key Sub AddTime() ActiveCell = Time End Sub -- FREE EXCEL NEWSLETTER /News/2home.htm Get the OzGrid Add-in /Services/AddinExamples.htm If it's Excel, then it's us! "chuck conroy" <cconroy@jps.net wrote in message news:115e801c219b4$76737140$39ef2ecf@TKMSFTNGXA08... | I would like to have Excel input the time in this format | hh:mm:ss | I have tried two different things. | 1. The now statement =now() this works fine however the | next time I enter it the previous time gets updated to the | new time. I want each cell to display the exact time that | is is entered and then to retain this time. | 2. I have also tried control shift : this does display | a different time in each cell, however it only displays | the hour and minute, the seconds are always 00 no mater | how I format the cell. | Does anyone have any suggestions? | Thanks, | Chuck Conroy . |
| 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 |
| 526 | I'm looking for a formula to make a cell return the average from one cell containing a date (in this format 06/22/02) using only the day (22) divided by another cell containing a total. Is this possible? The cell containing the date will change variably as will the total. Thank you. |
| 527 | Here is one approach. This will not re-protect the sheets. You will have to use the code from the previous response for that. Sub Get_Password() On Error GoTo errPassword Dim strPassword As String Dim i As Integer '*** GET THE PASSWORD FROM USER strPassword = InputBox("Enter Password", "Unprotection Password") '*** TRY TO UNPROTECT THE FIRST SHEET Sheets(3).Unprotect Password:=strPassword '*** IF NO ERROR THEN UNPROTECT THE REST OF THE SHEETS For i = 4 To 50 Sheets(i).Unprotect Password:=strPassword Next i Exit Sub errPassword: '*** DISPLAY A MESSAGE IF PASSWORD INCORRECT AND EXIT MsgBox "You entered and incorrect password!" End Sub -----Original Message----- Dave, That's great, thanks. Is there a way I can ask for a password in order to run this macro - if not correct then it exits? Then I could leave this macro in the workbook and just protect the code. Rob. -----Original Message----- Hi Rob This code will toggle sheet protection on/off Sub ToggleProtectContent() Dim i As Integer For i = 3 To 50 If Sheets(i).ProtectContents = False Then Sheets(i).Protect Password:="rob1" Else Sheets(i).Unprotect Password:="rob1" End If Next i End Sub It only checks for Content protection. -- "Rob Pearce" <rob.pearce@consignia.com wrote in message news:f81c01c219bb$7dfb11a0$a4e62ecf@tkmsftngxa06... | I have a workbook with 50 worksheets. | | I protect sheets 3 to 50 [all being the same template for | data entry] with the same password. | | When I want to change some formula/formatting on these 48 | sheets I have to unprotect each sheet individually so that | I can eventually group them together to alter. | | Would it be possible to have some code that will loop from | shhet 3 to 50 and unprotect - the password is rob1 if | anyone could lend a hand - and conversely then to re | protect. | | I would obviously remove the macros from the workbook | afterwards! | | Thanks, | | Rob. . . |
| 528 | =D1/DAY(A1) where A1 holds the date and D1 the total format result as general or number or it might be returned as date -- Regards, Peo Sjoblom "James Beam" <jimbeam@onemain.com wrote in message news:yY1R8.15928$Fv1.1178764@newsread2.prod.itd.earthlink.net... I'm looking for a formula to make a cell return the average from one cell containing a date (in this format 06/22/02) using only the day (22) divided by another cell containing a total. Is this possible? The cell containing the date will change variably as will the total. Thank you. |
| 534 | Hello folks, I'm trying to download data from a webpage into an excel sheet. The resultant excel sheet does not contain the data in the appropriate rows/columns. System: Microsoft Office 2000 Professional Internet Explorer version 5.00 with update SP2 1) access to webpage /p/_noncyc- bevnon.html 2) click on "Download Spreadsheet" 3) save as filename 'abc' with format 'txt' and encoding 'Western European (Windows)'. This file already has the data mixed up. (see all errors a to e below) 4) At excel, menu 'Data', submenu item 'Get External Data', with delimitor(s) of a comma selected. Errors The result contains the following errors: a) The first column does not alway contain the entire company's name. b) The last column does not always contain one piece of info. Sometimes it contains a value and a part of the company's name. c) The company's name enclosed with double quotes d) Some second columns contains the word 'In.' or 'S.A.' subsequent data are right shifted by one column. e) Some rows only contain a partial name and the associated data is found in the subsequent row. I suspect the company's name are too long and/or contain special characters (periods, comma, hyphen). Also, there is possibly no carriage return or tab key at the end of each row. How can one create an excel sheet that contains a given company and all its data on a single row (as in the original webpage above, but with the excel capabilities to delete rows and sort by several variables) at the same time.)? Note: the header line is nice to have but is not important as one can manually enter this with little time. Please show a simple procedure to achieve the correct data. Your assistance is most appreciated. Thank you, |
| 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! |
| 536 | Try dragging to select the HTML worksheet at yahoo and right-clickCopy. Open a fresh Excel worksheet and paste it. It seems to work for me. --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 "George" <george39672002@yahoo.com wrote in message news:112b501c21a18$94d59dc0$9be62ecf@tkmsftngxa03... Hello folks, I'm trying to download data from a webpage into an excel sheet. The resultant excel sheet does not contain the data in the appropriate rows/columns. System: Microsoft Office 2000 Professional Internet Explorer version 5.00 with update SP2 1) access to webpage /p/_noncyc- bevnon.html 2) click on "Download Spreadsheet" 3) save as filename 'abc' with format 'txt' and encoding 'Western European (Windows)'. This file already has the data mixed up. (see all errors a to e below) 4) At excel, menu 'Data', submenu item 'Get External Data', with delimitor(s) of a comma selected. Errors The result contains the following errors: a) The first column does not alway contain the entire company's name. b) The last column does not always contain one piece of info. Sometimes it contains a value and a part of the company's name. c) The company's name enclosed with double quotes d) Some second columns contains the word 'In.' or 'S.A.' subsequent data are right shifted by one column. e) Some rows only contain a partial name and the associated data is found in the subsequent row. I suspect the company's name are too long and/or contain special characters (periods, comma, hyphen). Also, there is possibly no carriage return or tab key at the end of each row. How can one create an excel sheet that contains a given company and all its data on a single row (as in the original webpage above, but with the excel capabilities to delete rows and sort by several variables) at the same time.)? Note: the header line is nice to have but is not important as one can manually enter this with little time. Please show a simple procedure to achieve the correct data. Your assistance is most appreciated. Thank you, |
| 537 | Looking for any ideas on how to import, cut & paste whatever... From PDF into a workable text /numbers format. I need to manipulate the numbers in Excel. Thanks Ken |
| 539 | Hi Ken, In the unlikely event that you have the Full Acrobat you can use Copy Table on the Text drop down in the full acrobat. How to Export a Table From a PDF File into An Excel Spreadsheet /edrs/services/publications/howto/PDFtoXLS/PDFtoExcel.html This is doable though with the Free Acrobat Reader you have to select one column at a time from the table. Ctrl+v or look for boxed [T] for Text and use the drop down box to select Column Select Tool (Ctrl+v) Select the column from the table in acrobat, and paste into Excel. Carefully select the next column so everything remains lined up with the previous Column Select and Paste operation. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel Macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "KENJOHNSO" <KENJOHNSO@email.msn.com wrote in message news:Ok$VNuhGCHA.2532@tkmsftngp09... Looking for any ideas on how to import, cut & paste whatever... From PDF into a workable text /numbers format. I need to manipulate the numbers in Excel. Thanks Ken |
| 540 | Hi Ken, If you are talking about restoring from the CD that takes care of the most important part don't do anything to the existing file. If you have the file on the hard drive copy it to another file and work only from the copied file(s). Did you try opening the file without macros and without addins. (in Safe Mode) The most painless solution is obtain Java from Sun Microsystems www.sun.com and Open Office from www.openoffice.com The openoffice is the same as what Sun used to supply free in StarOffice but gave the source code free for open office and now charges for StarCalc. Believe it or not!, some companies prefer to pay, thinking they will get better service if it breaks -- I'm sure not is not your main concern. You would be downloading about 90-120MB of data from those two sites so you would definitely need to have broadband or have someone download them to a CD for you. Other painless solutions (only painless if they work) is to have someone try to read the file on their system preferable Excel 2002 which is supposed to have more recovery ability. But even if you can only find someone with Excel 97 or 2000 or a Mac comparable version they may be able to read your file. If they can they should make a copy and another copy without your most recent sheet worked on. BTW, anybody reading your file should be checking for viruses before attempting to read your file. The second half of my Backup and Recovery page is concerned with Recovery. /dmcritchie/excel/backup.htm Good Luck, these solutions are probably as good as commercial solutions. Please report back your results, including any information as to why you think the copy of the CD got messed up, or was it just copied after the file was destroyed. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel Macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Rusty" <rusty@theseams.com.au wrote in message news:3d14b62b$0$28007$afc38c87@news.optusnet.com.au... My daughter's PC crashed and she can't read the backup of her most important Excel file from a CD. Is there a freeware or shareware utility that I can use to recover the file? She can't afford to pay USD$90 for the commercial programs. Thanks for your suggestions. Ken Russell Sydney |
| 544 | Is the check box grey, or the words (Merge cells)? If there are different formats on the worksheet, the check mark in the check box will be grey. Click once in the check box to add a check mark Click again, to remove the check mark. Click OK Pat Kung wrote: I am trying to sort my spreadsheet, but it keeps telling me I have merged cells and can't do that. The problem is I can't find the merged cells. I tried to highlight the whole spreadsheet and uncheck merged cells, but it is grayed out when I highlight the whole spreadsheet. Anybody have a way to find out what cells are merged so I can unmerge them? Thanks in advance -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 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 |
| 547 | Hi Pat, Format, Cells is grayed out with sheet protection. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel Macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Pat Kung" <Pat.Kung@sdsmt.edu wrote in message news:1107f01c21a24$25330280$9ae62ecf@tkmsftngxa02... I am trying to sort my spreadsheet, but it keeps telling me I have merged cells and can't do that. The problem is I can't find the merged cells. I tried to highlight the whole spreadsheet and uncheck merged cells, but it is grayed out when I highlight the whole spreadsheet. Anybody have a way to find out what cells are merged so I can unmerge them? Thanks in advance |
| 554 | Mr. Taylor, Your suggestion works! Terrific! Thank you! Just one followup question: The 'sort' function creates strange results on only one particular column (market capitalization) where it appears all the 'values' are treated as characters. Sampling of data after a sort is 1.9B, 10.7B, 138.4B, 14.2B, 2.3B, 247.0M, etc. Note the 'B' is billions and 'M' is millions. How can one convert all these 'values' into the proper numbers? Thank you again, George -----Original Message----- Try dragging to select the HTML worksheet at yahoo and right-clickCopy. Open a fresh Excel worksheet and paste it. It seems to work for me. --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 "George" <george39672002@yahoo.com wrote in message news:112b501c21a18$94d59dc0$9be62ecf@tkmsftngxa03... Hello folks, I'm trying to download data from a webpage into an excel sheet. The resultant excel sheet does not contain the data in the appropriate rows/columns. System: Microsoft Office 2000 Professional Internet Explorer version 5.00 with update SP2 1) access to webpage /p/_noncyc- bevnon.html 2) click on "Download Spreadsheet" 3) save as filename 'abc' with format 'txt' and encoding 'Western European (Windows)'. This file already has the data mixed up. (see all errors a to e below) 4) At excel, menu 'Data', submenu item 'Get External Data', with delimitor(s) of a comma selected. Errors The result contains the following errors: a) The first column does not alway contain the entire company's name. b) The last column does not always contain one piece of info. Sometimes it contains a value and a part of the company's name. c) The company's name enclosed with double quotes d) Some second columns contains the word 'In.' or 'S.A.' subsequent data are right shifted by one column. e) Some rows only contain a partial name and the associated data is found in the subsequent row. I suspect the company's name are too long and/or contain special characters (periods, comma, hyphen). Also, there is possibly no carriage return or tab key at the end of each row. How can one create an excel sheet that contains a given company and all its data on a single row (as in the original webpage above, but with the excel capabilities to delete rows and sort by several variables) at the same time.)? Note: the header line is nice to have but is not important as one can manually enter this with little time. Please show a simple procedure to achieve the correct data. Your assistance is most appreciated. Thank you, . |
| 557 | Select all the numbers. Then EditReplace hold the Alt key and use the number keypad on the right of the keyboard to enter 0160 as the "Find what". Be sure the "Replace with" is empty. Replace All. That is a common HTML character that looks like a space. Alternatively, download Refinate and use it for free to convert to numbers by dragging and clicking (apply Refinate's Number-Values action). --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 "George" <george39672002@yahoo.com wrote in message news:112b301c21a32$52c03390$b1e62ecf@tkmsftngxa04... Mr. Taylor, Your suggestion works! Terrific! Thank you! Just one followup question: The 'sort' function creates strange results on only one particular column (market capitalization) where it appears all the 'values' are treated as characters. Sampling of data after a sort is 1.9B, 10.7B, 138.4B, 14.2B, 2.3B, 247.0M, etc. Note the 'B' is billions and 'M' is millions. How can one convert all these 'values' into the proper numbers? Thank you again, George -----Original Message----- Try dragging to select the HTML worksheet at yahoo and right-clickCopy. Open a fresh Excel worksheet and paste it. It seems to work for me. --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 "George" <george39672002@yahoo.com wrote in message news:112b501c21a18$94d59dc0$9be62ecf@tkmsftngxa03... Hello folks, I'm trying to download data from a webpage into an excel sheet. The resultant excel sheet does not contain the data in the appropriate rows/columns. System: Microsoft Office 2000 Professional Internet Explorer version 5.00 with update SP2 1) access to webpage /p/_noncyc- bevnon.html 2) click on "Download Spreadsheet" 3) save as filename 'abc' with format 'txt' and encoding 'Western European (Windows)'. This file already has the data mixed up. (see all errors a to e below) 4) At excel, menu 'Data', submenu item 'Get External Data', with delimitor(s) of a comma selected. Errors The result contains the following errors: a) The first column does not alway contain the entire company's name. b) The last column does not always contain one piece of info. Sometimes it contains a value and a part of the company's name. c) The company's name enclosed with double quotes d) Some second columns contains the word 'In.' or 'S.A.' subsequent data are right shifted by one column. e) Some rows only contain a partial name and the associated data is found in the subsequent row. I suspect the company's name are too long and/or contain special characters (periods, comma, hyphen). Also, there is possibly no carriage return or tab key at the end of each row. How can one create an excel sheet that contains a given company and all its data on a single row (as in the original webpage above, but with the excel capabilities to delete rows and sort by several variables) at the same time.)? Note: the header line is nice to have but is not important as one can manually enter this with little time. Please show a simple procedure to achieve the correct data. Your assistance is most appreciated. Thank you, . |
| 562 | Hi, Is the excel list a special feature or is it just a format like this: Type Price Food 34 Clothes 35 (this is just a sample). Fran |
| 565 | Hi Fran: What you have reproduced is just data that has been typed in. There is no special "list" format that I know of. -- Regards, Vasant. **No direct emails please--keep discussion in newsgroup.** "Francis" <fran@swiftnet.org wrote in message news:k_6R8.104873$831.63457@news01.bloor.is.net.cable.rogers.com... Hi, Is the excel list a special feature or is it just a format like this: Type Price Food 34 Clothes 35 (this is just a sample). Fran |
| 568 | Thanks a lot! Fran "Vasant Nanavati" <vasantn@aol.com wrote in message news:OLMzRRkGCHA.2516@tkmsftngp13... Hi Fran: What you have reproduced is just data that has been typed in. There is no special "list" format that I know of. -- Regards, Vasant. **No direct emails please--keep discussion in newsgroup.** "Francis" <fran@swiftnet.org wrote in message news:k_6R8.104873$831.63457@news01.bloor.is.net.cable.rogers.com... Hi, Is the excel list a special feature or is it just a format like this: Type Price Food 34 Clothes 35 (this is just a sample). Fran |
| 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 |
| 576 | Hi Pat: Use <Format <Conditional Formating. If the column is A, the condition should be: Cell Value Is equal to =A$1 -- Regards, Vasant. **No direct emails please--keep discussion in newsgroup.** "Pat Roehr" <texpat@cox-internet.com wrote in message news:uhaar21ak0fb98@corp.supernews.com... I'm embarrassed to ask this, because I used to know how to do it, but I've forgotten. I have rows full of numbers. I want to know which cells contain numbers that match the numbers in row 1. I'd like them to show up in color. Can someone help this idiot? :) |
| 578 | You could add some code that will insert the date in the footer before the sheet is printed. For example, the following code (in the ThisWorkbook module) adds the formatted date to the right footer: Private Sub Workbook_BeforePrint(Cancel As Boolean) Worksheets("Sheet1").PageSetup.RightFooter = _ Format(Date, "dd-mmm-yy") End Sub Vittal wrote: So I am the first visitor. Can anybody tell me how to change the date format in footer. I am using WIN 98, Office 2000. Presently it is coming in dd-mm-yy format. The Regional Settings properties for date tab are as follow: dd-mmm-yy I want the date format to look like 21-Jun-02. Thanks in advance. Vittal -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 580 | Well, this works if an extraneous nonprinting character makes Excel think there is text, not numbers, in the cells. But I see 'M's and 'B's, which are text, too. Here's how to change them into numbers that Excel understands: Select the numbers. Do a find and replace, and replace B with E9, then replace M with E6. Billions are 10^9 or 1E9, and Millions are 10^6 or 1E6. This converts the non-numbers into numbers (I did a little test just now), so they can be sorted normally. - Jon ------- Jon Peltier, Microsoft Excel MVP /jonpeltier/Excel/index.html _______ In article <a36R8.17345$Fv1.1219940@newsread2.prod.itd.earthlink.net, adetaylor said... Select all the numbers. Then EditReplace hold the Alt key and use the number keypad on the right of the keyboard to enter 0160 as the "Find what". Be sure the "Replace with" is empty. Replace All. That is a common HTML character that looks like a space. Alternatively, download Refinate and use it for free to convert to numbers by dragging and clicking (apply Refinate's Number-Values action). --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 "George" <george39672002@yahoo.com wrote in message news:112b301c21a32$52c03390$b1e62ecf@tkmsftngxa04... Mr. Taylor, Your suggestion works! Terrific! Thank you! Just one followup question: The 'sort' function creates strange results on only one particular column (market capitalization) where it appears all the 'values' are treated as characters. Sampling of data after a sort is 1.9B, 10.7B, 138.4B, 14.2B, 2.3B, 247.0M, etc. Note the 'B' is billions and 'M' is millions. How can one convert all these 'values' into the proper numbers? Thank you again, George -----Original Message----- Try dragging to select the HTML worksheet at yahoo and right-clickCopy. Open a fresh Excel worksheet and paste it. It seems to work for me. --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 "George" <george39672002@yahoo.com wrote in message news:112b501c21a18$94d59dc0$9be62ecf@tkmsftngxa03... Hello folks, I'm trying to download data from a webpage into an excel sheet. The resultant excel sheet does not contain the data in the appropriate rows/columns. System: Microsoft Office 2000 Professional Internet Explorer version 5.00 with update SP2 1) access to webpage /p/_noncyc- bevnon.html 2) click on "Download Spreadsheet" 3) save as filename 'abc' with format 'txt' and encoding 'Western European (Windows)'. This file already has the data mixed up. (see all errors a to e below) 4) At excel, menu 'Data', submenu item 'Get External Data', with delimitor(s) of a comma selected. Errors The result contains the following errors: a) The first column does not alway contain the entire company's name. b) The last column does not always contain one piece of info. Sometimes it contains a value and a part of the company's name. c) The company's name enclosed with double quotes d) Some second columns contains the word 'In.' or 'S.A.' subsequent data are right shifted by one column. e) Some rows only contain a partial name and the associated data is found in the subsequent row. I suspect the company's name are too long and/or contain special characters (periods, comma, hyphen). Also, there is possibly no carriage return or tab key at the end of each row. How can one create an excel sheet that contains a given company and all its data on a single row (as in the original webpage above, but with the excel capabilities to delete rows and sort by several variables) at the same time.)? Note: the header line is nice to have but is not important as one can manually enter this with little time. Please show a simple procedure to achieve the correct data. Your assistance is most appreciated. Thank you, . |
| 587 | How can I protect the format of a worksheet. For example; frame around a cell font size and color cell color I have protect all attributes of the worksheet using tools/protect but the users of the worksheet are still able to modify the items mentioned above. Thanks. |
| 593 | Are the Excel 97 Help files available on the Microsoft site? I can find the 2000 Help text, but in spite of a lot of searching, I can't find the Excel 97 help. I particularly need the VBA help information. If they're available, I'd be grateful for a URL. Thanks. Anne |
| 594 | You must install the help files Anne. If you install Office custom the vba helpwill not be installed I believe.?? So run your setup again and install it Regards Ron "Anne Lambert" <AnneTLambert@hotmail.com schreef in bericht news:uvAYyupGCHA.1772@tkmsftngp09... Are the Excel 97 Help files available on the Microsoft site? I can find the 2000 Help text, but in spite of a lot of searching, I can't find the Excel 97 help. I particularly need the VBA help information. If they're available, I'd be grateful for a URL. Thanks. Anne |
| 596 | Thanks for this. But I don't want to *use* XL 97: I just want to read the help files. I use XL 2002, and I can't remember all that's changed over the years. Anne "Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:O07bGzpGCHA.2992@tkmsftngp13... You must install the help files Anne. If you install Office custom the vba helpwill not be installed I believe.?? So run your setup again and install it Regards Ron "Anne Lambert" <AnneTLambert@hotmail.com schreef in bericht news:uvAYyupGCHA.1772@tkmsftngp09... Are the Excel 97 Help files available on the Microsoft site? I can find the 2000 Help text, but in spite of a lot of searching, I can't find the Excel 97 help. I particularly need the VBA help information. If they're available, I'd be grateful for a URL. Thanks. Anne |
| 598 | Hi Anne Type: What's new in Microsoft Excel? into the help -- "Anne Lambert" <AnneTLambert@hotmail.com wrote in message news:ODTBn7pGCHA.2448@tkmsftngp10... | Thanks for this. But I don't want to *use* XL 97: I just want to read the help files. I use XL 2002, and I can't remember all that's | changed over the years. | | Anne | | | "Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:O07bGzpGCHA.2992@tkmsftngp13... | You must install the help files Anne. | If you install Office custom the vba helpwill not be installed I believe.?? | | So run your setup again and install it | | Regards Ron | | "Anne Lambert" <AnneTLambert@hotmail.com schreef in bericht | news:uvAYyupGCHA.1772@tkmsftngp09... | Are the Excel 97 Help files available on the Microsoft site? I can find | the 2000 Help text, but in spite of a lot of searching, I | can't find the Excel 97 help. I particularly need the VBA help | information. | | If they're available, I'd be grateful for a URL. | | Thanks. | | Anne | | | | | | | | |
| 599 | In most versions of Excel, if the worksheet is protected, users will not be able to change the cell formatting, which includes the borders, font colour, font size and cell colour. In Excel 2002, you can choose options as you set the protection, and cell formatting can be allowed. Maybe you have protected the workbook, instead of the worksheet. arthur w. mohr, jr. wrote: How can I protect the format of a worksheet. For example; frame around a cell font size and color cell color I have protect all attributes of the worksheet using tools/protect but the users of the worksheet are still able to modify the items mentioned above. Thanks. -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 600 | Hi Dave Thanks for this. But this won't distinguish (as one example of several) the changes that have been made to parameters to the Worksheet.Protect command since 97. I can access the details from the Excel 2000 help files on the MS site. I'm trying to find out if the text of the XL 97 help files is also available somewhere. Anne "" <DavidH@OzGrid.com wrote in message news:pEhR8.18$kP5.5272@vicpull1.telstra.net... Hi Anne Type: What's new in Microsoft Excel? into the help -- FREE EXCEL NEWSLETTER /News/2home.htm Get the OzGrid Add-in /Services/AddinExamples.htm If it's Excel, then it's us! "Anne Lambert" <AnneTLambert@hotmail.com wrote in message news:ODTBn7pGCHA.2448@tkmsftngp10... | Thanks for this. But I don't want to *use* XL 97: I just want to read the help files. I use XL 2002, and I can't remember all that's | changed over the years. | | Anne | | | "Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:O07bGzpGCHA.2992@tkmsftngp13... | You must install the help files Anne. | If you install Office custom the vba helpwill not be installed I believe.?? | | So run your setup again and install it | | Regards Ron | | "Anne Lambert" <AnneTLambert@hotmail.com schreef in bericht | news:uvAYyupGCHA.1772@tkmsftngp09... | Are the Excel 97 Help files available on the Microsoft site? I can find | the 2000 Help text, but in spite of a lot of searching, I | can't find the Excel 97 help. I particularly need the VBA help | information. | | If they're available, I'd be grateful for a URL. | | Thanks. | | Anne | | | | | | | | |
| 603 | Zillion tks, it worked ! -----Original Message----- Hi Tony, Use this with the code you are already using: dname = "c:\mybackup\B" & Format(Now(), "yyyy_mmdd") HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel Macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "tony" <tony4fly@netscape.net wrote in message news:f6ba01c219ec$919bd380$37ef2ecf@TKMSFTNGXA13... I need help again. the macro saves a file with a given name. To the name I want to append today's date. What should be the command. Thanks a lot for the help. tony :-) . |
| 604 | AFAIK, the Excel 97 Help files aren't online, but the VBA Programmer's Guide may help: /library/default.asp?url=/library/en-us/office97/html/web/fulltoc.asp Anne Lambert wrote: Hi Dave Thanks for this. But this won't distinguish (as one example of several) the changes that have been made to parameters to the Worksheet.Protect command since 97. I can access the details from the Excel 2000 help files on the MS site. I'm trying to find out if the text of the XL 97 help files is also available somewhere. Anne "" <DavidH@OzGrid.com wrote in message news:pEhR8.18$kP5.5272@vicpull1.telstra.net... Hi Anne Type: What's new in Microsoft Excel? into the help -- "Anne Lambert" <AnneTLambert@hotmail.com wrote in message news:ODTBn7pGCHA.2448@tkmsftngp10... | Thanks for this. But I don't want to *use* XL 97: I just want to read the help files. I use XL 2002, and I can't remember all that's | changed over the years. | | Anne | | | "Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:O07bGzpGCHA.2992@tkmsftngp13... | You must install the help files Anne. | If you install Office custom the vba helpwill not be installed I believe.?? | | So run your setup again and install it | | Regards Ron | | "Anne Lambert" <AnneTLambert@hotmail.com schreef in bericht | news:uvAYyupGCHA.1772@tkmsftngp09... | Are the Excel 97 Help files available on the Microsoft site? I can find | the 2000 Help text, but in spite of a lot of searching, I | can't find the Excel 97 help. I particularly need the VBA help | information. | | If they're available, I'd be grateful for a URL. | | Thanks. | | Anne | | | | | | | | -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 605 | Hi Debra Yes, indeed. Thanks for this. Anne "Debra Dalgleish" <dsd@contextures.com wrote in message news:3D15B24B.2040004@contextures.com... AFAIK, the Excel 97 Help files aren't online, but the VBA Programmer's Guide may help: /library/default.asp?url=/library/en-us/office97/html/web/fulltoc.asp Anne Lambert wrote: Hi Dave Thanks for this. But this won't distinguish (as one example of several) the changes that have been made to parameters to the Worksheet.Protect command since 97. I can access the details from the Excel 2000 help files on the MS site. I'm trying to find out if the text of the XL 97 help files is also available somewhere. Anne "" <DavidH@OzGrid.com wrote in message news:pEhR8.18$kP5.5272@vicpull1.telstra.net... Hi Anne Type: What's new in Microsoft Excel? into the help -- FREE EXCEL NEWSLETTER /News/2home.htm Get the OzGrid Add-in /Services/AddinExamples.htm If it's Excel, then it's us! "Anne Lambert" <AnneTLambert@hotmail.com wrote in message news:ODTBn7pGCHA.2448@tkmsftngp10... | Thanks for this. But I don't want to *use* XL 97: I just want to read the help files. I use XL 2002, and I can't remember all that's | changed over the years. | | Anne | | | "Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:O07bGzpGCHA.2992@tkmsftngp13... | You must install the help files Anne. | If you install Office custom the vba helpwill not be installed I believe.?? | | So run your setup again and install it | | Regards Ron | | "Anne Lambert" <AnneTLambert@hotmail.com schreef in bericht | news:uvAYyupGCHA.1772@tkmsftngp09... | Are the Excel 97 Help files available on the Microsoft site? I can find | the 2000 Help text, but in spite of a lot of searching, I | can't find the Excel 97 help. I particularly need the VBA help | information. | | If they're available, I'd be grateful for a URL. | | Thanks. | | Anne | | | | | | | | -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 606 | Hi Jon & Brian, I tried the character 160 and wasn't getting any results, so I thought it was my computer and will=20 troubleshoot that over next little while. It is useful info to know of the character 160. The transfer of data into the excel sheet and the sort=20 function works well. Thanks to both of you. Much appreciated, =20 George -----Original Message----- Sorry George. Thanks Jon. I had to run earlier and didn't take enough time to understand the question properly but figured it might help to know about character 160. --Brian "Jon Peltier" <jonpeltier@yahoo.com wrote in message news:O8Yu7dmGCHA.2404@tkmsftngp08... Well, this works if an extraneous nonprinting character makes Excel think there is text, not numbers, in the cells. But I see 'M's and 'B's, which are text, too. Here's how to change them into numbers that Excel understands: Select the numbers. Do a find and replace, and replace B with E9, then replace M with E6. Billions are 10^9 or 1E9, and Millions are 10^6 or 1E6. This converts the non-numbers into numbers (I did a little test just now), so they can be sorted normally. - Jon ------- Jon Peltier, Microsoft Excel MVP /jonpeltier/Excel/index.html _______ In article <a36R8.17345$Fv1.1219940@newsread2.prod.itd.earthlink.ne t, adetaylor said... Select all the numbers. Then EditReplace hold the Alt key and use the number keypad on the right of the keyboard to enter 0160 as the "Find what". Be sure the "Replace with" is empty. Replace All. That is a common HTML character that looks like a space. Alternatively, download Refinate and use it for free to convert to numbers by dragging and clicking (apply Refinate's Number-Values action). --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 "George" <george39672002@yahoo.com wrote in message news:112b301c21a32$52c03390$b1e62ecf@tkmsftngxa04... Mr. Taylor, Your suggestion works! Terrific! Thank you! Just one followup question: The 'sort' function creates strange results on only one particular column (market capitalization) where it appears all the 'values' are treated as characters. Sampling of data after a sort is 1.9B, 10.7B, 138.4B, 14.2B, 2.3B, 247.0M, etc. Note the 'B' is billions and 'M' is millions. How can one convert all these 'values' into the proper numbers? Thank you again, George -----Original Message----- Try dragging to select the HTML worksheet at yahoo and right-clickCopy. Open a fresh Excel worksheet and paste it. It seems to work for me. --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 =CD{=9Dw=C0=1FE=05lgQ_4=FA*_<zS=0B =EC[=04 "George" <george39672002@yahoo.com wrote in message news:112b501c21a18$94d59dc0$9be62ecf@tkmsftngxa03... Hello folks, I'm trying to download data from a webpage into an excel sheet. The resultant excel sheet does not contain the data in the appropriate rows/columns. System: Microsoft Office 2000 Professional Internet Explorer version 5.00 with update SP2 1) access to webpage /p/_noncyc- bevnon.html 2) click on "Download Spreadsheet" 3) save as filename 'abc' with format 'txt' and encoding 'Western European (Windows)'. This file already has the data mixed up. (see all errors a to e below) 4) At excel, menu 'Data', submenu item 'Get External Data', with delimitor(s) of a comma selected. Errors The result contains the following errors: a) The first column does not alway contain the entire company's name. b) The last column does not always contain one piece of info. Sometimes it contains a value and a part of the company's name. c) The company's name enclosed with double quotes d) Some second columns contains the word 'In.' or 'S.A.' subsequent data are right shifted by one column. e) Some rows only contain a partial name and the associated data is found in the subsequent row. I suspect the company's name are too long and/or contain special characters (periods, comma, hyphen). Also, there is possibly no carriage return or tab key at the end of each row. How can one create an excel sheet that contains a given company and all its data on a single row (as in the original webpage above, but with the excel capabilities to delete rows and sort by several variables) at the same time.)? Note: the header line is nice to have but is not important as one can manually enter this with little time. Please show a simple procedure to achieve the correct data. Your assistance is most appreciated. Thank you, . . |
| 608 | Hi folks, Please excuse me if this is a FAQ, but I'm just terribly busy with finishing this project for school and I'd rather not waste time in Microsoft help documentation or searching the web. All I'm wondering is why all the dates that I have formatted as Custom: yyyy-mm-dd keep reverting to Custom: yy-mm-dd? I'm not sure why this is happening and it's getting really infuriating. My system format is yyyy-mm-dd, and I don't see any options for the default format or anything like that. Any ideas? Thanks, Doug |
| 609 | "Max Foo" <maxfoo@punkass.com wrote in message news:i9qbhu01v2lckrtn77r14ng8tqf4j73b04@4ax.com... On Sun, 23 Jun 2002 14:50:32 GMT, "Mike" <mike@nospam.com wrote: "Max Foo" <maxfoo@punkass.com wrote in message news:j5ibhuc7fqdidrd9j1ifvu8eau5ag3b3ps@4ax.com... What exactly was the probably you were having with Excel. Every one of the requirements you listed can be done in Excel. Not really. Engineering units, for example, in which the powers of 10 used are multiples of 3, aren't possible in Excel. It would be even nicer if yes, this can be done easily.. right click select 'format cell', go to custom or run this macro with the cell you want formatted changed. Selection.NumberFormat = "##0.0E+0" Hey, imagine that. It actually works... but there's certainly nothing obvious about it, is there? There's nothing in the help under "Engineering Notation," or under Number, or under Format... In fact, here's the complete text of the scientific number format help from the number format codes help page: ------------------- Scientific notation To display numbers in scientific format, use "E-," "E+," "e-," or "e+" exponent codes in a section. If a format contains a 0 (zero) or # (number sign) to the right of an exponent code, Excel displays the number in scientific format and inserts an "E" or "e". The number of 0's or #'s to the right of a code determines the number of digits in the exponent. E- or e- places a minus sign by negative exponents. E+ or e+ places a minus sign by negative exponents and a plus sign by positive exponents. ------------------- I know: I'm looking in the wrong place. I should have looked under number formats on the left side of the decimal point, and inferred what happens when I combine them with scientific notation. It sure would be easier if there was something like an "engineering notation" option, wouldn't it? Excel understood engineering suffixes the way SPICE does, and could use them in plots. As an engineer, 10u is much easier to read and understand than this too can be done.. define a function using VBA. The last thing anyone should have to do is learn a programming language to make a plot. 1E-5. Excel requires that log axes begin and end on powers of 10, so if your data extends from 1 to 5, the axis still extends from 1 to 10. That's just stupid, and it can't be changed. Excel also strangely wants linear axes to again it seems your too lazy to setup the graph manually, or automate it will VBA. Lazy my ass, buddy. I did try it manually, and verified that it works the way I stated, before I sent the first message. Maybe there is some macro that will do it, but it's certainly not obvious, and it certainly doesn't work by using the manual settings. Choose a maximum value of 5 on a log scaled axis, and Excel conveniently changes it to 10 when you click OK. Maybe you could write a macro to do this, but I can't find any documentation on it. Why not? If it's easy to do, shouldn't it be easy to find? ... Mike, you need to learn Excel VBA than anything is possible... Where do you get off with this crud? It'll do what you want, as long as you write the progam to do what you want? -- Mike -- |
| 619 | Thanks Debra, it worked. -----Original Message----- You can hide the zeros: 1. Choose ToolsOptions 2. On the View tab, remove the check mark from Zero Values 3. Click OK hovendick wrote: Fred, thanks for the "use 0" reply. I tried that but "0's" show up in that row/cells. I am using this for a presentation in which it needs to show nothing (like an empty cell). If I change my formula to use zeros, is there a way to also make the text color white (so it does not show) or some other way to format it to appear empty (and keep the other rows showing values)? Thanks. -----Original Message----- Use 0 rather than blanks. If row 2 is 1/1/91 8000 1/1/91 0 XIRR will calculate a result for you. Regards, Fred. "hovendick" <mitch.hovendick@elpaso.com wrote in message news:f05a01c218ab$570d2a30$37ef2ecf@TKMSFTNGXA13... i have a file with dates and values like 1/1/90 -10000 1/1/90 -10000 1/1/91 8000 1/1/92 7000 1/1/92 7000 1/1/93 9000 1/1/93 9000 Column c is if(col a = "1/1/91", "", col a) and column d is if(col a = "1/1/91", "", col b). I created columns c and d to run an xirr on all dates/numbers except 1/1/91. My problem is that xirr does not like the ""'s in row 2. If I create columns c and d by hand with original "empty" cells in row 2 xirr works. Can anybody tell me how to put "empty" cells in row 2 with my if formula? . -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html . |
| 625 | Formula is =(A1-A2)/A2 format as % HTH "Mandy" <mandy.walston@peci.com wrote in message news:11da501c21bb0$6c8f2630$19ef2ecf@tkmsftngxa01... I have a simple question that I have been trying to figure out all day. I have two figures, one in A1 and one in A2. The figure in A1 is 154411, this represents a total for 2001. The figure in A2 is 138690, this represents a total for 2000. I want to know what the percentage growth is from those two numbers. For example, how much did the number increase from 2000 to 2001 (in a percentage). Thanks so much for any help. Mandy Walston Pedernales Electric Cooperative, Inc. |
| 627 | One way: A1: 154411 A2: 138690 A3: =(A1-A2)/A2 === 11.34% (formatted as %). In article <11da501c21bb0$6c8f2630$19ef2ecf@tkmsftngxa01, Mandy <mandy.walston@peci.com wrote: I have a simple question that I have been trying to figure out all day. I have two figures, one in A1 and one in A2. The figure in A1 is 154411, this represents a total for 2001. The figure in A2 is 138690, this represents a total for 2000. I want to know what the percentage growth is from those two numbers. For example, how much did the number increase from 2000 to 2001 (in a percentage). Thanks so much for any help. Mandy Walston Pedernales Electric Cooperative, Inc. |
| 628 | =A1/A2-1 Format as %. HTH Jason Atlanta, GA -----Original Message----- I have a simple question that I have been trying to figure out all day. I have two figures, one in A1 and one in A2. The figure in A1 is 154411, this represents a total for 2001. The figure in A2 is 138690, this represents a total for 2000. I want to know what the percentage growth is from those two numbers. For example, how much did the number increase from 2000 to 2001 (in a percentage). Thanks so much for any help. Mandy Walston Pedernales Electric Cooperative, Inc. . |
| 646 | Hello, I am having trouble when I run a print preview. I do not see all the text in my cells. Word wrap is on. It is generally the bottom line or so I do not see. I have formatted and reformatted the sheet and specific cells turning word wrap on and off. All looks fine on the worksheet screen. I get the same chopped results from the printer as I get from a print preview. Any Help Would Be Appreciated. Thanks, ctballos |
| 648 | Check the dpi setting on the printer(s). I think you want it at 600. HTH, Greg "Gary Massengale" <garym_jacksonfurniture@hotmail.com wrote in message news:eYhHWG5GCHA.1716@tkmsftngp08... here is the strange problem. I get an excel file from someone in customer service, it has borders. On our deskjets it prints the borders, but not on the laser printer? I tried reinstalling the laser printer driver, thats not it. Went to another PC and got the same result. I now have another, unrelated excel file someone in accounting has, they are having the same problem, on some printers the border prints out, on others just part of the border. The weird thing is, you do a print preview, and it shows the border, but still doesnt print it out. Is there a setting I need to change? I know reinstalling the print driver did not help, inserting the file into another blank excel did work, but it ruins all the formatting, they have to reformat the cells. What can I try? gary |
| 652 | Is this coming in from a Word Table? if yes, maybe something like this would work: If your table has carriage returns (paragraph marks/line feeds), then this is what I do. Select the table in Word Change the paragraph marks to something unique (AAAAA). Same with manual line breaks. Then copy and paste your table into excel. Then select that range and do another Edit|replace: replace AAAAA with alt-key 0010 (using the number keys at the right--not top of the keyboard) It may not look like it took, but continue anyway. Did line wrap work ok. (if you see a square box, select your range and do Format|cells|alignment and select wrap text. (don't forget to close word w/o saving (or at least with an Undo).) Abby Wynne wrote: When I copy from word and paste into excel it splits the paste across several cells. How can I paste several bullet points of text into one cell only? -- Dave Peterson ec35720@msn.com |
| 657 | i have building security software that says i can use Microsoft Excel Data Exchange Wizard to retrive information. i have not been able to find this feature does it exist anymore. i use office XP Pro. |
| 661 | I like to do my engineering on Excel. One of the problems is showing the regulators which equations I'm using. I would like to show the equation in its mathametical format along side the solution. I have tried to use different fonts, but the results are not good. Any suggestions? |
| 662 | Tom Excel will allow 32,000+ characters in a cell. However, it will show and print only 1024 characters. Split your text over more cells or use a TextBox. HTH Gord Dibben Excel MVP - XL97 SR2 On Mon, 24 Jun 2002 20:39:16 GMT, "Tom Ballos" <ctballos@worldnet.att.net wrote: Hello, I am having trouble when I run a print preview. I do not see all the text in my cells. Word wrap is on. It is generally the bottom line or so I do not see. I have formatted and reformatted the sheet and specific cells turning word wrap on and off. All looks fine on the worksheet screen. I get the same chopped results from the printer as I get from a print preview. Any Help Would Be Appreciated. Thanks, ctballos |
| 667 | I have a problem that appears to be fairly widespread. I haven't, however, found a solution to my exact problem on this site... Here's the trouble. I have a system of files that I need to be able to transfer over to a different drive. I've read that as long as everything is in the same directory and the sub-directory structure and names of the sub-directories are left intact that Excel will pretend the references are relative (aka, I can move the files and they will work). Alas, it doesn't appear to be working for me. Here's the magnitude of data that I'm worried about: 26 directories, 25 of which are designed to collect data from different users (the whole reason this all isn't just one file anymore), and 1 directory to compile this information (and also serve as a source of a single piece of information for every file in the other directories). In each of the 25 directories, I will have 9 or 36 (undecided) files that I need to reference. The collection folder contains two files - fetcher (which gets the data for me) and sesdata (which the input files need to reference). The collection folder is named "master", the other inputs are all numbered. All these directories are in the directory "qpa." I try to move the directory containing both master and the input folders (qpa), and all my links which, if relative, would read "../master/sesdata" break. I would like to be able to design this system on my pc, record it to a CD, then copy it onto different computers. Due to the number of links, the edit links option simply won't work. I am willing to move the directory structure (to some extent), try VBA (throw code at me, I don't do Basic), or whatever. Or if I need to try to use Quattro Pro, tell me. Any help is greatly appreciated. |
| 674 | You can use conditional formatting. For example, if this formula is in cells C1:C10, 1. Select the cells to be formatted 2. Choose FormatConditional Formatting 3. From the dropdown, choose Formula Is 4. In the Formula box, type a formula that refers to the first cell in the selection, e.g.: =ISERROR(C1) 5. Click the Format button. 6. On the Font tab, select White as the font colour (or any colour that matches the cell background) 7. Click OK, click OK Stan Hilliard wrote: On Mon, 24 Jun 2002 21:41:33 -0400, Debra Dalgleish <dsd@contextures.com wrote: Change your formula to return an #N/A error instead of an empty string. For example, =IF(A1<=0,NA(),A1-B1) Stan Hilliard wrote: Excel 97 SR1. I have a column of data created by formula. When the data cannot be calculated the cell contains "". How can I prevent the empty cells from plotting as zeros on a scatter plot. Thanks, that worked for the chart. Is there a way that I can prevent the #NA from showing in the cells? (Only for cosmetic purposes.) -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 677 | On Mon, 24 Jun 2002 20:40:43 -0500, Stan Hilliard <usenetreplyMS@samplingplansNOTSPAM.com wrote: Excel 97 SR1. I have a column of data created by formula. When the data cannot be calculated the cell contains "". How can I prevent the empty cells from plotting as zeros on a scatter plot. While I was working with the program, it started giving the error "EXCEL caused an invalid page fault in module MSO97.DLL at 0167:30705766." This happens whenever I right click on an axis or area and try to format. Can this be fixed? |
| 687 | Could you use DataImport External Data, then choose either Import Data or New Database Query? layne wrote: i have building security software that says i can use Microsoft Excel Data Exchange Wizard to retrive information. i have not been able to find this feature does it exist anymore. i use office XP Pro. -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 695 | 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.htm -- "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 | | | . | |
| 696 | Give us an example of what you want. Dan Budsberg wrote: I like to do my engineering on Excel. One of the problems is showing the regulators which equations I'm using. I would like to show the equation in its mathametical format along side the solution. I have tried to use different fonts, but the results are not good. Any suggestions? |
| 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 | | | | | | . | | | | | . | |
| 700 | I have a problem that appears to be fairly widespread. I haven't, however, found a solution to my exact problem on this site... Here's the trouble. I have a system of files that I need to be able to transfer over to a different drive. I've read that as long as everything is in the same directory and the sub-directory structure and names of the sub-directories are left intact that Excel will pretend the references are relative (aka, I can move the files and they will work). Alas, it doesn't appear to be working for me. Here's the magnitude of data that I'm worried about: 26 directories, 25 of which are designed to collect data from different users (the whole reason this all isn't just one file anymore), and 1 directory to compile this information (and also serve as a source of a single piece of information for every file in the other directories). In each of the 25 directories, I will have 9 or 36 (undecided) files that I need to reference. The collection folder contains two files - fetcher (which gets the data for me) and sesdata (which the input files need to reference). The collection folder is named "master", the other inputs are all numbered. All these directories are in the directory "qpa." I try to move the directory containing both master and the input folders (qpa), and all my links which, if relative, would read "../master/sesdata" break. I would like to be able to design this system on my pc, record it to a CD, then copy it onto different computers. Due to the number of links, the edit links option simply won't work. I am willing to move the directory structure (to some extent), try VBA (throw code at me, I don't do Basic), or whatever. Or if I need to try to use Quattro Pro, tell me. |
| 703 | Hi drwinterton I am sure someone will come along with a possible answer for you. I just wanted to see if your area aware of the EditLinksChange source option available in Linked Workbooks. -- "drwinterton" <daniel_winterton@hk.ml.com wrote in message news:1223601c21c1a$285a9e30$3aef2ecf@TKMSFTNGXA09... | I have a problem that appears to be fairly widespread. I | haven't, however, found a solution to my exact problem on | this site... | | Here's the trouble. I have a system of files that I need | to be able to transfer over to a different drive. | | I've read that as long as everything is in the same | directory and the sub-directory structure and names of the | sub-directories are left intact that Excel will pretend | the references are relative (aka, I can move the files and | they will work). Alas, it doesn't appear to be working | for me. | | Here's the magnitude of data that I'm worried about: 26 | directories, 25 of which are designed to collect data from | different users (the whole reason this all isn't just one | file anymore), and 1 directory to compile this information | (and also serve as a source of a single piece of | information for every file in the other directories). In | each of the 25 directories, I will have 9 or 36 | (undecided) files that I need to reference. | | The collection folder contains two files - fetcher (which | gets the data for me) and sesdata (which the input files | need to reference). The collection folder is | named "master", the other inputs are all numbered. All | these directories are in the directory "qpa." | | I try to move the directory containing both master and the | input folders (qpa), and all my links which, if relative, | would read "../master/sesdata" break. I would like to be | able to design this system on my pc, record it to a CD, | then copy it onto different computers. Due to the number | of links, the edit links option simply won't work. I am | willing to move the directory structure (to some extent), | try VBA (throw code at me, I don't do Basic), or whatever. | Or if I need to try to use Quattro Pro, tell me. | | |
| 708 | My PC installed Office XP. Recently when I open Excel, and want to change the letters format to "Bold" or "underline" or "Slanting shape" or change letters color, then an ERROR message comes out. Say :- "EXCEL caused a general protection fault in module GDI.EXE at 0026:00001e4c." And then I have to close Excel and all my job is gone !! Cannot save it anymore !! I tried already uninstall Office XP and install again, still the same. Any Office Expert user can help me ....????? How to solve this problem ?? Urgently awaiting some assistance. Thanks. Regards Amen |
| 714 | Hi, Some questions if I may. - How did you copy your files? - Did you copy the complete tree with the files? - what is a correct link and what does it look like after copying you files (and opening the master copy) I guess simplest should be to simply copy the entire tree to another drive. But if the links travel with that also depends on their exact type. If they are direct cell to cell links, AFAIK they should adjust to the new location. But if any part of them are hardcoded (like in the INDIRECT function), well... But I guess you already knew that <g. Regards, Jan Karel Pieterse Excel TA/MVP -----Original Message----- I have a problem that appears to be fairly widespread. I haven't, however, found a solution to my exact problem on this site... Here's the trouble. I have a system of files that I need to be able to transfer over to a different drive. I've read that as long as everything is in the same directory and the sub-directory structure and names of the sub-directories are left intact that Excel will pretend the references are relative (aka, I can move the files and they will work). Alas, it doesn't appear to be working for me. Here's the magnitude of data that I'm worried about: 26 directories, 25 of which are designed to collect data from different users (the whole reason this all isn't just one file anymore), and 1 directory to compile this information (and also serve as a source of a single piece of information for every file in the other directories). In each of the 25 directories, I will have 9 or 36 (undecided) files that I need to reference. The collection folder contains two files - fetcher (which gets the data for me) and sesdata (which the input files need to reference). The collection folder is named "master", the other inputs are all numbered. All these directories are in the directory "qpa." I try to move the directory containing both master and the input folders (qpa), and all my links which, if relative, would read "../master/sesdata" break. I would like to be able to design this system on my pc, record it to a CD, then copy it onto different computers. Due to the number of links, the edit links option simply won't work. I am willing to move the directory structure (to some extent), try VBA (throw code at me, I don't do Basic), or whatever. Or if I need to try to use Quattro Pro, tell me. . |
| 715 | Hi John Try formatting the cells (before pasting) as Text. -- "John Ricketts" <master@westnet.com.au wrote in message news:erNSZ6CHCHA.2436@tkmsftngp11... | When copying and pasting ( say a table from FrontPage to Excel ) fractions | like 6/11 get translated into dates. I have a large number of data tables | to copy and paste and going through editing each cell with a fraction is not | an option. | | How does one prevent Excel from making this conversion? | | | | |
| 717 | Don't think INDIRECT works for closed Workbooks <bg -- "Jan Karel Pieterse" <pieterse_home@compuserve.com wrote in message news:1005401c21c35$215b2120$9ee62ecf@tkmsftngxa05... | Hi, | | Some questions if I may. | | - How did you copy your files? | - Did you copy the complete tree with the files? | - what is a correct link and what does it look like after | copying you files (and opening the master copy) | | I guess simplest should be to simply copy the entire tree | to another drive. But if the links travel with that also | depends on their exact type. If they are direct cell to | cell links, AFAIK they should adjust to the new location. | | But if any part of them are hardcoded (like in the | INDIRECT function), well... But I guess you already knew | that <g. | | | Regards, | | Jan Karel Pieterse | Excel TA/MVP | | -----Original Message----- | I have a problem that appears to be fairly widespread. I | haven't, however, found a solution to my exact problem on | this site... | | Here's the trouble. I have a system of files that I need | to be able to transfer over to a different drive. | | I've read that as long as everything is in the same | directory and the sub-directory structure and names of | the | sub-directories are left intact that Excel will pretend | the references are relative (aka, I can move the files | and | they will work). Alas, it doesn't appear to be working | for me. | | Here's the magnitude of data that I'm worried about: 26 | directories, 25 of which are designed to collect data | from | different users (the whole reason this all isn't just one | file anymore), and 1 directory to compile this | information | (and also serve as a source of a single piece of | information for every file in the other directories). In | each of the 25 directories, I will have 9 or 36 | (undecided) files that I need to reference. | | The collection folder contains two files - fetcher (which | gets the data for me) and sesdata (which the input files | need to reference). The collection folder is | named "master", the other inputs are all numbered. All | these directories are in the directory "qpa." | | I try to move the directory containing both master and | the | input folders (qpa), and all my links which, if relative, | would read "../master/sesdata" break. I would like to be | able to design this system on my pc, record it to a CD, | then copy it onto different computers. Due to the number | of links, the edit links option simply won't work. I am | willing to move the directory structure (to some extent), | try VBA (throw code at me, I don't do Basic), or | whatever. | Or if I need to try to use Quattro Pro, tell me. | | | . | |
| 718 | Thanks for the suggestion but I had already tried that. I had also tried formatting as Fraction but that didn't help the cause either. "" <DavidH@OzGrid.com wrote in message news:gTXR8.7$1h6.12510@vicpull1.telstra.net... Hi John Try formatting the cells (before pasting) as Text. -- FREE EXCEL NEWSLETTER /News/2home.htm Get the OzGrid Add-in /Services/AddinExamples.htm If it's Excel, then it's us! "John Ricketts" <master@westnet.com.au wrote in message news:erNSZ6CHCHA.2436@tkmsftngp11... | When copying and pasting ( say a table from FrontPage to Excel ) fractions | like 6/11 get translated into dates. I have a large number of data tables | to copy and paste and going through editing each cell with a fraction is not | an option. | | How does one prevent Excel from making this conversion? | | | | |
| 720 | You should get the Paste special option when you right click? Maybe one of these options will help, a normal paste would undo the Text format. -- "John Ricketts" <master@westnet.com.au wrote in message news:uE0TjlDHCHA.704@tkmsftngp08... | Thanks for the suggestion but I had already tried that. | I had also tried formatting as Fraction but that didn't help the cause | either. | | | "" <DavidH@OzGrid.com wrote in message | news:gTXR8.7$1h6.12510@vicpull1.telstra.net... | Hi John | | Try formatting the cells (before pasting) as Text. | | | -- | | | | | FREE EXCEL NEWSLETTER | /News/2home.htm | Get the OzGrid Add-in | /Services/AddinExamples.htm | If it's Excel, then it's us! | | | "John Ricketts" <master@westnet.com.au wrote in message | news:erNSZ6CHCHA.2436@tkmsftngp11... | | When copying and pasting ( say a table from FrontPage to Excel ) | fractions | | like 6/11 get translated into dates. I have a large number of data | tables | | to copy and paste and going through editing each cell with a fraction is | not | | an option. | | | | How does one prevent Excel from making this conversion? | | | | | | | | | | | | |
| 727 | Hi Dave, 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. I totally disagree on the last remark. Yes, they tend to be slow. But they are often an elegant solution to an indeed complex problem. They are far from superficial and seldomly a quick fix. The use of Excels database functions can often be used in place of arrays as can Pivot Tables etc That may go for extracting information from simple tables. As soon as one has to combine data from different subsets, XL's database functions just aren't good enough. Sometimes an array formula can then come to rescue. I still hold firm that any spreadsheet that requires manual calculation has a spreadsheet design problem. You run the risk of reading uncalculated results. Disagreed again. I just happen to have a lot of (very complex) data and calc sometimes. But there is always the risk of uncalculated data, indeed. It is a matter of knowing ones business. These workbooks are not for the masses, they are for me. Printouts are for my colleagues, which are always automatically calculated. Regards, Jan Karel Pieterse Excel TA/MVP |
| 731 | Jan, I agree to disagree. If your methods work for you then stick with them. -- "Jan Karel Pieterse" <pieterse_home@compuserve.com wrote in message news:11fa701c21c4e$f10c30d0$b1e62ecf@tkmsftngxa04... | Hi Dave, | | | | 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. | | I totally disagree on the last remark. Yes, they tend to | be slow. But they are often an elegant solution to an | indeed complex problem. They are far from superficial and | seldomly a quick fix. | | The use of Excels database functions | can often be used in place of arrays as can Pivot Tables | etc | | That may go for extracting information from simple tables. | As soon as one has to combine data from different subsets, | XL's database functions just aren't good enough. Sometimes | an array formula can then come to rescue. | | | I still hold firm that any spreadsheet that requires | manual calculation has | a spreadsheet design problem. You run the risk of reading | uncalculated | results. | | Disagreed again. I just happen to have a lot of (very | complex) data and calc sometimes. But there is always the | risk of uncalculated data, indeed. It is a matter of | knowing ones business. These workbooks are not for the | masses, they are for me. Printouts are for my colleagues, | which are always automatically calculated. | | | Regards, | | Jan Karel Pieterse | Excel TA/MVP | |
| 744 | You wrote: Hi again. Is there any way to make a cell change the contents of another cell or cells? for example, I'd like to have a column A verify whether column D is blank or has a value, and then make column C be blank or have a value (depending only on the D values). For what you're asking -- to make a cell blank or show a value depending upon the value in another cell -- there is a workaround. You could use conditional formatting. For example, set up conditional formatting for cell C1; in the conditional formatting window specify a formula; something like =IF(ISBLANK($D1),1,0) ;then in the format, if you want cell C1 to appear blank specify a white font on a white background in a tiny font. IF you want the cell value to appear then specify a normal font on a normal background. That's just a workaround solution that works because you want the cell value to appear or not... but, there is no normal way for a cell formula to cause a change in the value of another cell. Excel works that way so that it can 'know' how to recalculate all of the cell formulas in a worksheet in a specific sequence without circular or illogical references. |
| 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 |
| 748 | hello dave, thanks for the program. it works great. jj -----Original Message----- How about this: Option Explicit Sub testme() Dim FirstRow As Long Dim LastRow As Long Dim LastCol As Long Dim iRow As Long Dim iCol As Long Dim oRow As Long Dim curWks As Worksheet Dim newWks As Worksheet Set curWks = ActiveSheet Set newWks = Worksheets.Add With curWks FirstRow = 1 LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row oRow = 1 For iRow = FirstRow To LastRow LastCol = .Cells(iRow, .Columns.Count).End (xlToLeft).Column If LastCol 1 Then For iCol = 2 To LastCol newWks.Cells(oRow, 1).Value = .Cells (iRow, 1).Value newWks.Cells(oRow, 2).Value = .Cells (iRow, iCol).Value oRow = oRow + 1 Next iCol End If Next iRow End With With newWks .Range("A:A").NumberFormat = "mm/dd/yyyy" .UsedRange.Columns.AutoFit End With End Sub Adjust your firstrow to match your data (or clean it up after the macro runs). (also the date format. I like leading 0's <vbg.) james jing wrote: Hi, I have excel data as following format column 1 2 3 4 5 7/1/2002 event a event b event c event d 7/2/2002 event a1 event b1 event c1 event d1 . . . I would like to convert the data into two columns with the following format: 7/1/2002 event a 7/1/2002 event b 7/1/2002 event c ... 7/2/2002 event d1 james -- Dave Peterson ec35720@msn.com . |
| 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 |
| 760 | For some formatting reason I need to display the words in a cell flipped upside down. I know in format cell Alignment direction I can change the directions of the words but it limit me to +/- 90 degree. What I need is 180 degree. Can anybody help? Thanks a lot. Rhoda |
| 765 | Hi Maurice Got to Tools=Options=Custom Lists and create a new custom List Sep Oct Nov etc. through to Aug In the Pivot Table, double click on the field heading, click on Advanced and set Autosort to Manual using your date field. This should get the results in the correct order -- Roger Govier Technology 4 U W98SR2 XL2K On Mon, 24 Jun 2002 03:11:17 -0700, "Maurice O omen" <moomen@planet.nl wrote: Thanks, and sorry for my late reply. The format of the dates in the fields are actually dd-mmm-yy, so 9-Oct-01, 12-May-02, etc. And grouping months as such goes fine, it is just that it puts January 2002 before October 2001, so it says "Jan", "Feb", etc. till "Dec", appearantly ignoring the years. Maurice -----Original Message----- Maurice, Do you have actual dates? You indicate Sept-01, which is a date in Excel's book. Get it? Book. <g Oh well. Though it's September first to Excel, not Sep 2001. Then you indicate Sep(02), which isn't a date for sure. It sounds as if you do not. I think you need real dates. You can type them in as the first of their months (10/01/01). That will cause all of each month to be in a single group in the pivot table. To get them to appear as Sep(01), format them (Format, Number, Custom) as mmm(yy). If you already have actual complete dates, then make a column for first-of-month dates, as follows: =DATE(YEAR(B2),MONTH(B2),1) This converts the dates to the first of their months, so they'll group by month. Format as above. Use it in your pivot table. Regards from Virginia Beach, EarlK ---------------------------------------------------------- --- "Maurice Oomen" <moomen@planet.nl wrote in message news:ad0lhs$9fp$1@reader11.wxs.nl... I have created a pivot table with a time dimension having dates ranging from Sept-01 to Apr-02 (today). When I group these dates into months, Excel sorts the months wrong. I.e. it is not taking the years into account. It sorts like: Jan, Feb, Mar, Apr, Sep, Oct, Nov, Dec. Whereas it should sort like: Sep(01), Oct(01), Nov(01), Dec(01), Jan(02), Feb(02), Mar(02), Apr(02). Is this a bug? Or am I doing something wrong? Thx, Maurice -------------------------------------------------------- ------------------ -- ---- . |
| 774 | You could use Word Art, and rotate it 180 degrees. In Excel 2002 you can copy a picture of a cell (hold Shift key, choose EditCopy Picture) and rotate the picture. Rhoda Chow wrote: For some formatting reason I need to display the words in a cell flipped upside down. I know in format cell Alignment direction I can change the directions of the words but it limit me to +/- 90 degree. What I need is 180 degree. Can anybody help? Thanks a lot. Rhoda -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 780 | Yes, you need to format as hh:mm:ss.00 -- Regards, Peo Sjoblom "Tim Bregning Karlsen" <timmail@post6.tele.dk wrote in message news:3d199d4e$0$78800$edfadb0f@dspool01.news.tele.dk... Can Excel calculate with seconds divided in 100? Fx. I would like to calculate the difference on two times from a stopwatch STOP 08:16:00:04 minus START 08:15:00:06 equals? Thanks! Tim :-) |
| 783 | See Harlan's answer to transform data by ame in microsoft.public.excel.worksheet.functions NG. Cheers "james jing" <jjing@sleh.com wrote in message news:103a901c21c66$07650ca0$a5e62ecf@tkmsftngxa07... Hi, I have excel data as following format column 1 2 3 4 5 7/1/2002 event a event b event c event d 7/2/2002 event a1 event b1 event c1 event d1 . . . I would like to convert the data into two columns with the following format: 7/1/2002 event a 7/1/2002 event b 7/1/2002 event c ... 7/2/2002 event d1 james |
| 788 | radhakrishnan.j, What you see is not actually what is in the cell. The $ and the comma are part of a cell format, and not part of the cell value. Simply export to the CSV and the $100,000.00 gets changed to 100000, so you will not have any problem re-importing. If you are creating the CSV through code, simply leave out the comma from the export string and use 100000 instead. HTH, Bernie "Radhakrishnan Jankiraman" <jradhakr@in.ibm.com wrote in message news:afbv4b$h7a$1@news.btv.ibm.com... Hi !! I am trying to write data to a csv file. How shall i avoid ' $100,000.00 ' not getting split into two cells as ' $100 ' and ' 000.00 ' ? How to stop the ',' from splitting ? I had posted the same query sometime back. But i lost track of the query and any responses to it. Please help me out. Thanx radhakrishnan.j |
| 793 | thank you, that worked great! Those tips will save me a lot of time. --phill -----Original Message----- Try: =F1&CHAR(10)&F2&CHAR(10)&F3 Wrap text under Format Cells, set the row width, and autofit the column width. To format individual compenents of the concatenated cell, copy and paste special value over the formulas. HTH Jason Atlanta, GA -----Original Message----- Hello, I would like to Permanently merge/join 3 text fields. I have found the =concatenate and the join text help files, but these do not let me edit the actual text in the new field. After I join these 3 fields, I need to add spaces and other formatting. example f1: name f2: street f3: city-state-zip joined to a new field as wrapped text (f4) _____________________ f4: | phil smith | | 123 street | | st.joe mi 49022 | |____________________| the way the concatenate/join options work, I can not add extra spaces or other formatting characters that are unique for each Field_4. Since some names addresses and cities are different sizes I can not add a set number of spaces to my field_4 otherwise one field is exactly right and anothers formatting is off. thank you very much . . |
| 801 | I have a list by month and have tried to apply Autoformat but not all the formats apply. It colors the column headers but does not apply the bold or italics on the row headers. I have all the formatting choices checked under options. Any ideas??? Thanks! |
| 807 | Hello, thank you in advance for any information I might aquire. I am running Office XP professional on a Citrix Metaframe XP box. When a user copies and pastes with the caps lock on, all keyboard entry from that point is in lower case even though the caps lock light is on. And it stops the minus key from working on the number pad. Has anyone seen this before and if so how did you rectify it. Thanks again Ed Givens Net admin |
| 813 | May I take the opportunity to add my 2 cents worth? I started with Excel in Excel 95. I learned maybe 60% of what I know about Excel from the Help files, and the rest from these newsgroups. I now have Excel 2002. And I am hugely disappointed with the change in the Help files in both Word and Excel. If N. Squire (the OP) happens to be using Excel 2002 and went searching in Help, as you might, for "horizontal scroll bar" he would have been faced with 20 possible pages of information. On my system, the first few are: "Insert subtotals", "Include different data in an offline cube", "Troubleshoot security and protection". None of the 20, from what I can see, mentions the horizontal scroll bar on the side of a worksheet or mentions Tools | Options etc. No wonder he can't find what he needs! I used to feel confident that I could tell people that they could find the information they needed in the Help files. I'm no longer confident enough to say that and send them, instead, to the nearest bookshop in search of John Walkenbach's books. With due respect, thanks, praise and genuflection, such books should be an adjunct to, not a replacement for, Help files that are structured such that users can find what they need. Maybe it's just me. But I find the new Help file system imponderable. Perhaps those of you who have Microsoft's collective ear could point out that this is not supporting the company's publicly-stated corporate policy of being driven by the market and making it easy for the user. Anne "Harald Staff" <harald.staff@eunet.no wrote in message news:u6i6$MGHCHA.2604@tkmsftngp11... Hi John Some folks must fight (or worse; persuade) the paperclip to be allowed to enter Help. It is very exhausting, especially on international systems. So I'd prefer a MVP or a tech writer answering <g. Best wishes Harald "John Walkenbach" <john@j-walk.com wrote in message news:epCpV3FHCHA.2652@tkmsftngp11... FYI, answers to these types of questions are readily available in Excel's Help system. You might find that approach more efficient than posting to a newsgroup. |
| 814 | I have been asked to come up with a function to continuously display the column width in a spreadsheet that changes as the width of a column is changed. Using the information cell(width) function I can get a rounded width but I have to use the F9 key to recalculate. It would be better if I could get the exact width instead of a rounded width. Any suggestions? |
| 820 | Format as # ???/??? -- Regards, Niek Otten Microsoft MVP - Excel "Antonio" <amsnb@email.msn.com wrote in message news:12b4b01c21d1f$ec3122b0$39ef2ecf@TKMSFTNGXA08... Would anyone be able to let me know how to get excel to to provide the results of fractions in the LOWEST TERMS. |
| 821 | Hey! Thanks for the help, but I am still a bit confused. I am guessing when you say ,"Replace the first a1 with 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 need to be open for the function to work? Should I have one 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, and any more help you can offer is greatly appreciated. Scott A. Jones -----Original Message----- Hi Scott, =if(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 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 still having trouble with making it work. As I am not an experienced Excel User, I am still trying to learn 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 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. 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 compare the DoNotCallList to the Master List, and to either flag or delete any matching numbers. The match will be done using the phone number, in an xxx-xxx-xxxx format. I have tried many different variations on the Match Function line 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 duplicates. I suppose what the trouble is that I am having is 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 files, and how I go about applying the line to the lists, 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 follows: =if(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 . . |
| 825 | Hi, Is there a function in Excel 97 that is equivalent to Lotus 1-2-3's @cellpointer function which returns information of the current cell ? Thanks Andy |
| 842 | Thank you Steven for the information... They also mentioned Lookup......but I can't seem to be able to add that function in. It's an add-in. Thanks.... JR "Steven McDonald" <a@a.com wrote in message news:12a8c01c21d27$b9729720$3aef2ecf@TKMSFTNGXA09... Hi, You don't really need a macro for this. The VLOOKUP commmand should do the job nicely. Just search for vlookup in excel help for all the info you need. It would be easier if your sheet containing part numbers and descriptions was a different sheet in the same workbook, rather than in another workbook. HTH, Steve -----Original Message----- Hello, I have a form template (write-up rev 031902) and need help with the following: In my form I have B14......B29 ...in these cells I put in a part number... and would like the description for that part number to be added into C14....C29 For the description and part number I have created another workbook named "partdesc"......were column A..has the part numbers and they start from Cell 3 down and column B has the description for those part numbers. How can I write a macro for this... Thanks -- JR . |
| 844 | Lookup the "Cell" function in XL Help. It can tell you anything you could possibly need to know about a cell. HTH RD "Andy" <atkchung@hkbn.net wrote in message news:1384301c21d26$bed9e930$35ef2ecf@TKMSFTNGXA11... Hi, Is there a function in Excel 97 that is equivalent to Lotus 1-2-3's @cellpointer function which returns information of the current cell ? Thanks Andy |
| 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 . |
| 847 | There's no event triggered when cell width changes, so to make it continuous, you'd need to use an OnTime macro: Public gCWKeepGoing As Boolean Public Sub ContinuousCellWidth() With Range("A1") .Value = .EntireColumn.ColumnWidth End With If gCWKeepGoing Then _ Application.OnTime Now + TimeSerial(0, 0, 1), _ "ContinuousCellWidth" End Sub Public Sub ToggleContinuousCellWidth() gCWKeepGoing = Not gCWKeepGoing If gCWKeepGoing Then ContinuousCellWidth End Sub In article <129bc01c21d21$35771c80$19ef2ecf@tkmsftngxa01, Ed MIller <mmiller@kinyova.com wrote: I have been asked to come up with a function to continuously display the column width in a spreadsheet that changes as the width of a column is changed. Using the information cell(width) function I can get a rounded width but I have to use the F9 key to recalculate. It would be better if I could get the exact width instead of a rounded width. Any suggestions? |
| 856 | Here's one way to visually identify any cells that may have those unwanted garbage spaces you can't see: 1. Click on cell A1 and go to Format Conditional Formatting 2. Choose Formula Is and insert this: =EXACT(TRIM(A1),A1)=FALSE 3. Click on the Format button, select the Pattern tab, and pick a color. 4. OK, OK. 5. Then press Ctrl+C to copy A1, then ctrl+A to select the entire worksheet, right-click, Paste Special..., Format. Any cells that have the fill color you chose indicate that they contain extra spaces (extra spaces does NOT include single spaces between words). HTH Jason Atlanta, GA -----Original Message----- I have a very large Excel file. I want to be able to see if there are any blank spaces within the cells. For instance, one of the fields is labeled "fname". How do I know if there are any blank spaces after the first name? Is there a "view hidden characters" feature in excel or a "show/hide" feature like in MS Word? Thanks in advance. . |
| 872 | Hello, Roger, Elizabeth, Thanks you for your help.... I do understand how Vlookup works now. With the help of Wilson.. we did the following: You said you wanted it in AA14 can I have this Vlookup on a cell other from D14.......like AA14 This =IF(ISNA(VLOOKUP(Z14,partdesc!$A$2:$B$62749,2,FALSE)),"",VLOOKUP(Z14,partdes c!$A$2:$B$62749,2,FALSE)) prevents the #N/A errors if there is nothing to lookup or if the part number is not found This =IF(B14="","",B14) means If B14 is blank, make this cell blank, otherwise make it equal to B14 This =IF(AA14="","",AA14) means If AA14 is blank, make this cell blank, otherwise make it equal to AA14 If my part number list gets bigger......do I just increase the ranges.... ................. Now on my form..... How can I make it do the following: If a part number field.....B14 is not used and we decide to enter some information on cell D14....... This will cause the formula to be erased.... But then......I want to go back and enter a part number in B14 the description will not added to D14. In other words....I want to make it to were B14 .....a part number is added then it should override my notes in D14 and add the part number description. Could this be done. Thanks Jaime "elizabeth" <edavis@unch.unc.edu wrote in message news:10df201c21d4b$c21146a0$a4e62ecf@tkmsftngxa06... Your formula would be =vlookup($B14,partdesc!$A:$B,2,FALSE) enter the formulat into cell D14. Where $B14 = the part number you are trying to find the description for. partdesc! is the sheet where the description exists $A:$B are the columns where the item number and description exist in partdesc 2 represents column B where the description exists Hope this helps -----Original Message----- Hello, Need some help with Vlookup.... Very new to this...... If I have cell B14......I used this cell to enter a part number and if I was to enter a part number...... I would like the description for that part number to be added to cell D14. The decryption for these part numbers in a worksheet that I named "partdesc" My part numbers are in cells A2......A62749 and the descriptions for these part numbers are in cell B2...........B62749 How or what should I do to make this work... If I was to enter a part number in B14.......to pull the description for that part number (looking at my partdesc worksheet) and placing it on D14. I am very new to Vlookup and I was using the function option and that did not work. Help!! Thanks Jaime . |
| 882 | How can I convert a date in the format 2002-01-01 to dd/mm/yyyy easily? All help appreciated, deadline to meet! |