add-in combo box date excel 2000 excel 2002 excel 97 excel XP format formatting formula grouping loan - amortization macros subtotal sum excel tutorial workbook worksheet vba
| Article | Body |
| 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 |
| 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 . |
| 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 |
| 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 |
| 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. |
| 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 |
| 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 |
| 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 |
| 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 |
| 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! |
| 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! |
| 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 |
| 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 |
| 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! |
| 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 |
| 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 . |
| 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 |
| 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! |
| 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. . |
| 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 |
| 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. . . |
| 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! |
| 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 |
| 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 |
| 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 |
| 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? | | | | |
| 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? | | | | | | | | | | | | |
| 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. |
| 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 |
| 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 |
| 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! |
| 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. . |
| 910 | You can use conditional formatting. 1. Select the cells with dates that you want to check 2. Choose FormatConditional Formatting 3. Leave the first dropdown as Cell Value Is 4. From the next dropdown, choose Less Than 5. In the next box, type: =TODAY() 6. Click the Format button 7. On the Pattern tab, select a colour. 8. Click OK, click OK Doug wrote: i know i've seen this done but can't figure out how to make dates that have come and gone highlight in a different color. anybody know the trick? -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 913 | Hi Doug One way to do this is Choose Format=Conditional Formatting Select the drop down arrow to give Formual Is In the white pane type =IF(A1-TODAY()<0,True,False) Click on Format and select the format you wish to apply to the cells. Change the reference A1 to suit your case. -- Roger Govier Technology 4 U W98SR2 XL2K "Doug" <doug@belmontebuilders.com wrote in message news:1115901c21df3$58382c50$36ef2ecf@tkmsftngxa12... i know i've seen this done but can't figure out how to make dates that have come and gone highlight in a different color. anybody know the trick? |
| 917 | I have a large list of 9 digit numbers down one column. I would like to be able to enter numbers down another column, and have the numbers change format IF they already exist in the other column. I was hoping I could set up CONDITIONAL FORMATTING so that the blank cells would be able to "look" into the original column and determine if I am entering a duplicate value (IE: I am hoping that any number that I am entering into the second column would go BOLD or COLOR-CODED if it is already entered into the first column). THANKS! |
| 920 | Hi Frank Choose Format=Conditional Formatting Select the drop down arrow to give Formula Is In the white pane type =IF(COUNTIF($A$1,B1)1,True,False) Click on Format and select the format you wish to apply to the cells. Change the ranges to suit -- Roger Govier Technology 4 U W98SR2 XL2K "Frank" <fjd@hitchcock.org wrote in message news:1118f01c21df8$cebbe330$2ae2c90a@hosting.microsoft.com... I have a large list of 9 digit numbers down one column. I would like to be able to enter numbers down another column, and have the numbers change format IF they already exist in the other column. I was hoping I could set up CONDITIONAL FORMATTING so that the blank cells would be able to "look" into the original column and determine if I am entering a duplicate value (IE: I am hoping that any number that I am entering into the second column would go BOLD or COLOR-CODED if it is already entered into the first column). THANKS! |
| 922 | Aplogies Formula should read =IF(COUNTIF($A$1:$A$100,B1)1,True,False) "Roger Govier" <roger@technology4u.co.uk wrote in message news:#8NDTpfHCHA.2932@tkmsftngp13... Hi Frank Choose Format=Conditional Formatting Select the drop down arrow to give Formula Is In the white pane type =IF(COUNTIF($A$1,B1)1,True,False) Click on Format and select the format you wish to apply to the cells. Change the ranges to suit -- Roger Govier Technology 4 U W98SR2 XL2K "Frank" <fjd@hitchcock.org wrote in message news:1118f01c21df8$cebbe330$2ae2c90a@hosting.microsoft.com... I have a large list of 9 digit numbers down one column. I would like to be able to enter numbers down another column, and have the numbers change format IF they already exist in the other column. I was hoping I could set up CONDITIONAL FORMATTING so that the blank cells would be able to "look" into the original column and determine if I am entering a duplicate value (IE: I am hoping that any number that I am entering into the second column would go BOLD or COLOR-CODED if it is already entered into the first column). THANKS! |
| 923 | I have a spreadsheet with a simple formula (example: +o23- o13) in several columns. The formatting category is Accounting, no symbol, 2 decimal places. The zero answers range in appearance from: 1) - 2) 0.00 3) (0.00) With an accounting format, I expect the "dash mark" appearance, but not the other two. The differences do not create any computation problems, but make the completed spreadsheet look odd. Although this spreadsheet was designed in an earlier Excel version, I see no dissimilarities in any other formulas. |
| 924 | Select the 2nd column (say column B), and, assuming the active cell in in row 1, enter this in the CF dialog: Formula is =COUNTIF(A:A, B1)0 (assuming the first column is A:A) then click on format and format Bold, color-coded, etc. In article <1118f01c21df8$cebbe330$2ae2c90a@hosting.microsoft.com, Frank <fjd@hitchcock.org wrote: I have a large list of 9 digit numbers down one column. I would like to be able to enter numbers down another column, and have the numbers change format IF they already exist in the other column. I was hoping I could set up CONDITIONAL FORMATTING so that the blank cells would be able to "look" into the original column and determine if I am entering a duplicate value (IE: I am hoping that any number that I am entering into the second column would go BOLD or COLOR-CODED if it is already entered into the first column). THANKS! |
| 942 | Gene, There isn't any "magic number" below which all is right with the world, and above which all hell will break loose. Moreover, there isn't even a good "range" to use, because it depends to a very large degree on what it is that makes up the size. For example, you can have a huge workbook consisting of primarily data, with relatively few formulas, that will work fine. You can also have a workbook of exactly the same file size, with a large proportion of formulas, that will not operate "acceptably" (usually in terms of recalculation time). Likewise, having a very large number of cell formats (every combination of font, font size, font color, background color, italics, bold, etc) can lead corruption of the file. Excel claims that it supports up to 4000, but I've run into corruption problems (in very complicated workbooks) when the number is around 2000 (client's penchant for formatting, not mine). Depending on the specific application, you may well find it useful to create a "hybrid" application of Access (or, better, SQL Server) for data storage and Excel for a calculation machine and front end. Access or another DB system is good for storage of very large amounts of data (Excel isn't that great at that), but databases are generally lousy at complex calculations, something that Excel is extremely good at. You may find it useful to take advantage of the strengths of both Excel and a DB. You can use Excel as the front end, and Access or SQL Server as the back end. Of course, this may require a lot of code, but there are ways to make all that work quite well. Your question is a good one. Unfortunately, there is not a good answer. It is sort of like asking "When I am too fat?". If you're single, probably now. If you are married, well maybe not yet. If you want a simple "rule of thumb", I wouldn't generally worry if the workbook file size is less than say 10-15 MB (assuming you are using a fairly new PC -- all bets are off if you have less than 64 MB RAM or a CPU less than, say, 600 MHz). When you get up around 15+ MB, it might be time to rethink the basic architecture of the application. But again, it depends greatly on specifically what it is that makes up that 15 MB. I guess, the real answer is "it depends". I'm willing to commit to that. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com chip@cpearson.com "Gene" <eugene.golden@motorola.com wrote in message news:1314e01c21e02$0d3ee9f0$19ef2ecf@tkmsftngxa01... Can someone please tell me what the recommended size limit is for Excel. Another words, when an Excel spread sheet is around xx mb in size you should either migrate to Access or bust up the spread sheet. Thanks |
| 958 | Hi! The reason you are getting 3 different formats is that one is representing "exactly" zero, one "slightly position" (e.g., .0000001), and the last, "slightly negative". You can avoid the last 2 by rounding the results in the cell by encasing whatever formula is in the cell(s) with a =round( FORMULA, 2) HTH, -- Earl K. Takasaki Please reply to the group. "Tim Nichols" <timothy.nichols@state.tn.us wrote in message news:f49c01c21dfb$460b7cf0$95e62ecf@tkmsftngxs02... I have a spreadsheet with a simple formula (example: +o23- o13) in several columns. The formatting category is Accounting, no symbol, 2 decimal places. The zero answers range in appearance from: 1) - 2) 0.00 3) (0.00) With an accounting format, I expect the "dash mark" appearance, but not the other two. The differences do not create any computation problems, but make the completed spreadsheet look odd. Although this spreadsheet was designed in an earlier Excel version, I see no dissimilarities in any other formulas. |
| 971 | Yep. the way it's written. If you wanted the cell formatting to revert to none when you clearcontents, you could do something like: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing _ And Date = DateSerial(2002, 6, 1) Then If Not IsEmpty(Target) Then Target.Interior.ColorIndex = 36 Else Target.Interior.ColorIndex = xlNone End If End If End Sub You could still change the cell background via the toolbar icon or by Format|cells if you want to have something in the cell and non-red fill. Garry Sabino wrote: Ooopss.... it seems that I cant undo any changes I made on the cells were the event is applied. Is it meant to work that way? Garry "Garry Sabino" <oas@brunet.bn wrote in message news:uYuYdraHCHA.1604@tkmsftngp09... Super duper. Thanks a lot Dave, you're a Hero. Garry "" <DavidH@OzGrid.com wrote in message news:lJyS8.38$eA6.34565@vicpull1.telstra.net... Hi Gary This one will apply to A1:A10 Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Not Intersect(Target, Me.Range("A1:A10")) _ Is Nothing And Date = DateSerial(2002, 6, 1) Then Target.Interior.ColorIndex = 36 End If End Sub If the cells are non-contiguous then use something like: If Not Intersect(Target, Me.Range("A1:A10,C5:D20")) -- FREE EXCEL NEWSLETTER /News/2home.htm Get the OzGrid Add-in /Services/AddinExamples.htm If it's Excel, then it's us! "Garry Sabino" <oas@brunet.bn wrote in message news:OZPKxcaHCHA.1604@tkmsftngp09... | That's super but it only applies to cell A1. How can I apply the same on a | range of cells. | | Thanks a lot, | | Garry | | | "" <DavidH@OzGrid.com wrote in message | news:lvxS8.27$eA6.30865@vicpull1.telstra.net... | Hi Gary | | Right click on the sheet name tab, select "View Code" and paste in the | code | below: | | Private Sub Worksheet_Change(ByVal Target As Range) | If Target.Cells.Count 1 Then Exit Sub | | If Target.Address = "$A$1" And _ | Date = DateSerial(2002, 6, 1) Then | Target.Interior.ColorIndex = 36 | End If | End Sub | | Just change A1 to suit your needs. I have a UDF here: | /VBA/ReturnCellColor.htm that you can use to easily | get | the ColorIndex Property of a cells fill color. | | | Hope this helps | | | -- | | | | | FREE EXCEL NEWSLETTER | /News/2home.htm | Get the OzGrid Add-in | /Services/AddinExamples.htm | If it's Excel, then it's us! | | | | "Garry Sabino" <oas@brunet.bn wrote in message | news:OAkolkZHCHA.2200@tkmsftngp08... | | Hi, | | | | How do I change the color of a cell value if it is entered or modified | after | | an specific date. | | | | Say I'd like all cells value that are modified or entered after June 1, | 2002 | | to become red in color. | | | | Any suggestions would be highly appreciated. | | | | Garry. | | | | | | | | -- Dave Peterson ec35720@msn.com |
| 1044 | Hi Jim Try formatting your number as follows - note the commas after the zeros "$"#,##0, "K";[Red]"$"(#,##0,)"K" Regards William willwest22@yahoo.com "Jim Maguire" <james.f.maguire@intel.com wrote in message news:f84901c21ed1$c8189e90$95e62ecf@tkmsftngxs02... | I am trying to create a custom number format for K$. For | example, if something costs $1,000.00 it would be $1K. I | figured out how to add the text (the leading $ and | trailing K) but can't figure out how to perform | the "divide by 1000" part. I know that formating a | number as a percent essentially multiplies by 100, I need | one that divides by 1000. | | |
| 1045 | Hi Jim Try formatting your number as follows - note the commas after the zeros "$"#,##0, "K";[Red]"$"(#,##0,)"K" Regards William willwest22@yahoo.com "Jim Maguire" <james.f.maguire@intel.com wrote in message news:f84901c21ed1$c8189e90$95e62ecf@tkmsftngxs02... | I am trying to create a custom number format for K$. For | example, if something costs $1,000.00 it would be $1K. I | figured out how to add the text (the leading $ and | trailing K) but can't figure out how to perform | the "divide by 1000" part. I know that formating a | number as a percent essentially multiplies by 100, I need | one that divides by 1000. | | |
| 1069 | Thanks to you both, works perfectly, now all I have to do is understand it. I'd better read up on Countif. Thanks! Neal. "" <DavidH@OzGrid.com wrote in message news:9uYS8.17$tW6.14652@vicpull1.telstra.net... Hi Neal No need for VBA, Conditional Formatting will do it nicely see here: /Excel/Formulas.htm And here to take this a step further: /download/default.htm Download "FormsControls.zip" -- FREE EXCEL NEWSLETTER /News/2home.htm "Neal" <nblackie@clear.net.nz wrote in message news:O5e93upHCHA.2480@tkmsftngp11... | I am making a spreadsheet for our Radio Control flying club. Each member | has some transmitters and each has a frequency assigned. If someone is | flying and another person turns on their transmitter with the same | frequency, the one in the air spirals down out of control and crashes, and | there's a lot of yelling etc. | | I have a simple table, names down the left, frequencies owned across the | top. I want to be able to enter them in (such as 40.85, and if that number | already exists, they BOTH should be highlighted in red. That way, if anyone | looks at the sheet, they can spot who they potentially clash with. | | How do I do that? I guess I am going to need some sort of Macro that | updates whenever a cells is updated? | | Regards, Neal Blackie. | | |
| 1217 | Recurring problem: Selected cells won't respond to formatting. The following problems happen: * Apply Border toolbar shorcut doesn't work. * A right-click from selected cells, choice of FORMAT CELLS... produces no sub-menu * Accessing the main menu Format/Cells... produces no submenu. I had this problem in one part of my sheet and I decided to insert a new leftmost column. After this I could format some cells that now had an empty column to the side. But other areas still don't respond. Pass the clue, please. -jc Using Excel 2000 (Version 5.0.2195 Service Pack 1 Build 2195) |
| 1228 | But you said the formatting was different in the 2 windows... were you wrong on that point? On Mon, 1 Jul 2002 12:38:58 -0500, "DRB" <drb@barkto.com wrote: Bingo! Overlooking the obvious, that's me... "Peo Sjoblom" <terre08@mvps.org wrote in message news:#jZXTwRICHA.2612@tkmsftngp08... If it wasn't for the different formatting I'd say that somebody opened two windows of the same file and saved it (windownew window), if that was the case you can open the file, press ctrl+F4 and save the file.. -- Regards, Peo Sjoblom "DRB" <drb@barkto.com wrote in message news:uzu$jmRICHA.1784@tkmsftngp12... I have a user with an Excel 97 workbook, and when we open it (in XL97 or XL2000) it opens 2 copies- but with different formatting. If I try to close on of the two copies, it closes both. I have macros turned off, so that wouldn't appear to be the answer. Any ideas? This is weird. David |
| 1234 | Is the sheet protected? HTH RD "jc" <halfmybrain@yahoo.com wrote in message news:140ec01c22150$75e97690$b1e62ecf@tkmsftngxa04... Recurring problem: Selected cells won't respond to formatting. The following problems happen: * Apply Border toolbar shorcut doesn't work. * A right-click from selected cells, choice of FORMAT CELLS... produces no sub-menu * Accessing the main menu Format/Cells... produces no submenu. I had this problem in one part of my sheet and I decided to insert a new leftmost column. After this I could format some cells that now had an empty column to the side. But other areas still don't respond. Pass the clue, please. -jc Using Excel 2000 (Version 5.0.2195 Service Pack 1 Build 2195) |
| 1238 | I'm trying to copy from html web page and paste to an excel worksheet. The numbers are all like 4 to the fourth power. a 4 and then a small 4 elevated. i try formatting the cell to paste to but excel always reads as a date. Any ideas. |
| 1240 | I have a spreadsheet that was created in Excel 2000 which displays an error condition, but only when opened in Excel 2002. Some cells containing numbers are displayed with a small green triangle in the upper left corner of the cell. When you place your mouse pointer in this cell an error box appears to the left of the cell which contains an exclamation point and a drop down menu. The drop down menu tells you that you have a number in the cell which has been stored as text, and asks whether you want to correct this "error" by converting it to a number. Once you do this the green triangle is removed, and you are presumably okay. I opened a new spreadsheet in 2002 and pre-formatted a cell to text before entering a number. The green triangle appeared, and reformatting the cell to general or number did not make it go away. You have to fix the "error" with the special drop down box to the left of the cell. However, if you format a cell as text after a number has been entered into it using general or number format this error does not occur. I also encountered this problem when trying to import this spreadsheet into Access 2000. These cells cause an import error to occur - error 3349 numeric overflow. Once the "error" cells are fixed this import error disappears. Is anyone aware of a general fix for this problem by way of a service pack for Excel 2002? Bill |
| 1241 | Hi jc It sounds like there might be some code in the Sheet Object, right click on the sheet name tab select "View Code" if there anything there post it here, or delete it if you feel comfortable doing so. -- \ Get 8 Add-ins in one! For less than the price of 1 /Services/AddinExamples.htm FREE EXCEL NEWSLETTER /News/2home.htm "jc" <halfmybrain@yahoo.com wrote in message news:140ec01c22150$75e97690$b1e62ecf@tkmsftngxa04... | Recurring problem: Selected cells won't respond to | formatting. The following problems happen: | | * Apply Border toolbar shorcut doesn't work. | * A right-click from selected cells, choice of FORMAT | CELLS... produces no sub-menu | * Accessing the main menu Format/Cells... produces no | submenu. | | I had this problem in one part of my sheet and I decided | to insert a new leftmost column. After this I could format | some cells that now had an empty column to the side. But | other areas still don't respond. | | Pass the clue, please. | | -jc | | | Using Excel 2000 | (Version 5.0.2195 Service Pack 1 Build 2195) | |
| 1245 | Just a guess, but maybe the second window had gridlines displayed. Maybe that was enough to make the OP think that the formats were different. (But that comment was enough for me not to guess (too?)) Myrna Larson wrote: But you said the formatting was different in the 2 windows... were you wrong on that point? On Mon, 1 Jul 2002 12:38:58 -0500, "DRB" <drb@barkto.com wrote: Bingo! Overlooking the obvious, that's me... "Peo Sjoblom" <terre08@mvps.org wrote in message news:#jZXTwRICHA.2612@tkmsftngp08... If it wasn't for the different formatting I'd say that somebody opened two windows of the same file and saved it (windownew window), if that was the case you can open the file, press ctrl+F4 and save the file.. -- Regards, Peo Sjoblom "DRB" <drb@barkto.com wrote in message news:uzu$jmRICHA.1784@tkmsftngp12... I have a user with an Excel 97 workbook, and when we open it (in XL97 or XL2000) it opens 2 copies- but with different formatting. If I try to close on of the two copies, it closes both. I have macros turned off, so that wouldn't appear to be the answer. Any ideas? This is weird. David -- Dave Peterson ec35720@msn.com |
| 1247 | Hi Bill ToolsOptionsError Checking. You can record a macro toggling the options to get the code. -- \ Get 8 Add-ins in one! For less than the price of 1 /Services/AddinExamples.htm FREE EXCEL NEWSLETTER /News/2home.htm "Bill Murphy" <wmurphy@NO*SPAM!houston.rr.com wrote in message news:377U8.25029$p85.681329@twister.austin.rr.com... | I have a spreadsheet that was created in Excel 2000 which displays an error | condition, but only when opened in Excel 2002. Some cells containing | numbers are displayed with a small green triangle in the upper left corner | of the cell. When you place your mouse pointer in this cell an error box | appears to the left of the cell which contains an exclamation point and a | drop down menu. The drop down menu tells you that you have a number in the | cell which has been stored as text, and asks whether you want to correct | this "error" by converting it to a number. Once you do this the green | triangle is removed, and you are presumably okay. | | I opened a new spreadsheet in 2002 and pre-formatted a cell to text before | entering a number. The green triangle appeared, and reformatting the cell | to general or number did not make it go away. You have to fix the "error" | with the special drop down box to the left of the cell. However, if you | format a cell as text after a number has been entered into it using general | or number format this error does not occur. | | I also encountered this problem when trying to import this spreadsheet into | Access 2000. These cells cause an import error to occur - error 3349 | numeric overflow. Once the "error" cells are fixed this import error | disappears. | | Is anyone aware of a general fix for this problem by way of a service pack | for Excel 2002? | | Bill | | |
| 1252 | If it isn't what RD wrote, I can commiserate with you. I've seen this type of problem on 2 or 3 workbooks. They were always created with another program (Lotus 123 or Crystal Reports (IIRC)). I don't have a fix, but I did notice that if I selected more of a range than I actually needed, then I could format the cells. (Then I'd go back and get the "extra" cells.) Maybe this would work for you, too. ps. I think I saw the problem with xl97, though. Haven't seen it in a longggg time. In fact, I kept one workbook and just tried it. It worked fine with xl2002. jc wrote: Recurring problem: Selected cells won't respond to formatting. The following problems happen: * Apply Border toolbar shorcut doesn't work. * A right-click from selected cells, choice of FORMAT CELLS... produces no sub-menu * Accessing the main menu Format/Cells... produces no submenu. I had this problem in one part of my sheet and I decided to insert a new leftmost column. After this I could format some cells that now had an empty column to the side. But other areas still don't respond. Pass the clue, please. -jc Using Excel 2000 (Version 5.0.2195 Service Pack 1 Build 2195) -- Dave Peterson ec35720@msn.com |
| 1254 | Dave, Thanks for this tip. I turned off this error check and it immediately resolved my problem, including the problem when importing to Access. Bill "" <DavidH@OzGrid.com wrote in message news:6o7U8.18$tm.19011@vicpull1.telstra.net... Hi Bill ToolsOptionsError Checking. You can record a macro toggling the options to get the code. -- \ Get 8 Add-ins in one! For less than the price of 1 /Services/AddinExamples.htm FREE EXCEL NEWSLETTER /News/2home.htm "Bill Murphy" <wmurphy@NO*SPAM!houston.rr.com wrote in message news:377U8.25029$p85.681329@twister.austin.rr.com... | I have a spreadsheet that was created in Excel 2000 which displays an error | condition, but only when opened in Excel 2002. Some cells containing | numbers are displayed with a small green triangle in the upper left corner | of the cell. When you place your mouse pointer in this cell an error box | appears to the left of the cell which contains an exclamation point and a | drop down menu. The drop down menu tells you that you have a number in the | cell which has been stored as text, and asks whether you want to correct | this "error" by converting it to a number. Once you do this the green | triangle is removed, and you are presumably okay. | | I opened a new spreadsheet in 2002 and pre-formatted a cell to text before | entering a number. The green triangle appeared, and reformatting the cell | to general or number did not make it go away. You have to fix the "error" | with the special drop down box to the left of the cell. However, if you | format a cell as text after a number has been entered into it using general | or number format this error does not occur. | | I also encountered this problem when trying to import this spreadsheet into | Access 2000. These cells cause an import error to occur - error 3349 | numeric overflow. Once the "error" cells are fixed this import error | disappears. | | Is anyone aware of a general fix for this problem by way of a service pack | for Excel 2002? | | Bill | | |
| 1256 | Someone probably has a better idea than this, but whenever I copy data from a webpage and paste into Excel. I copy the columns again and use the paste special command to past only the values. After that I format the columns the way I like. -----Original Message----- I'm trying to copy from html web page and paste to an excel worksheet. The numbers are all like 4 to the fourth power. a 4 and then a small 4 elevated. i try formatting the cell to paste to but excel always reads as a date. Any ideas. . |
| 1259 | ya i've tried that it still doesnt format right thanks -----Original Message----- Someone probably has a better idea than this, but whenever I copy data from a webpage and paste into Excel. I copy the columns again and use the paste special command to past only the values. After that I format the columns the way I like. -----Original Message----- I'm trying to copy from html web page and paste to an excel worksheet. The numbers are all like 4 to the fourth power. a 4 and then a small 4 elevated. i try formatting the cell to paste to but excel always reads as a date. Any ideas. . . |
| 1260 | Hi AKS If you can give me the URL of the page I will see what I can come up with. Are you aware of Excels Web query feature, this will do way with the need to do anything on your part once set up. -- \ Get 8 Add-ins in one! For less than the price of 1 /Services/AddinExamples.htm FREE EXCEL NEWSLETTER /News/2home.htm "AKS" <akskelly@mail.com wrote in message news:1213201c22175$2dfa16d0$37ef2ecf@TKMSFTNGXA13... | ya i've tried that it still doesnt format right thanks | -----Original Message----- | Someone probably has a better idea than this, but | whenever | I copy data from a webpage and paste into Excel. I copy | the columns again and use the paste special command to | past only the values. After that I format the columns | the | way I like. | | | -----Original Message----- | I'm trying to copy from html web page and paste to an | excel worksheet. The numbers are all like 4 to the | fourth | power. a 4 and then a small 4 elevated. i try formatting | the cell to paste to but excel always reads as a date. | Any | ideas. | . | | . | |
| 1334 | "Edward Thornton" <edward.thornton@btinternet.com wrote in message news:<afqe1u$9n6$1@helle.btinternet.com... Can anyone please advise on how to open spreadsheets created with MS Works Version 4 for Windows 95 using Excel 2002? I can use Works to save spreadsheets in Excel format and then open them in Excel 2002 but I'd prefer to copy them across to a new PC as *.wks files and open them in Excel 2002 as required. Thanks, Ed RL-Software's Works spreadsheet to Excel Converter does this perfectly with almost all formulae and formatting of the cells maintained. You can find a free evaluation copy of this Excel Add-Inn at www.rl-software.com HTH Rob |
| 1335 | "Edward Thornton" <edward.thornton@btinternet.com wrote in message news:<afqe1u$9n6$1@helle.btinternet.com... Can anyone please advise on how to open spreadsheets created with MS Works Version 4 for Windows 95 using Excel 2002? I can use Works to save spreadsheets in Excel format and then open them in Excel 2002 but I'd prefer to copy them across to a new PC as *.wks files and open them in Excel 2002 as required. Thanks, Ed RL-Software's Works spreadsheet to Excel Converter does this perfectly with almost all formulae and formatting of the cells maintained. You can find a free evaluation copy of this Excel Add-Inn at www.rl-software.com HTH Rob |
| 1365 | I think I have the same question as Ron: Do you want to get rid of the formatting (to make it look normal) or do you want to bring the hyperlinks back? If you want to make the style normal, you can select your range and do Format|Style|Normal. (You might even notice that some of the cells were formatted with a style of Hyperlink.) If you want to bring the hyperlinks back, then I think you have to give more info. Hyperlinks can be created with a worksheet function: =hyperlink() or you can add it via the menus (Insert|Hyperlink). Either way, the text to display doesn't have to be the same as what the hyperlink points to. If you've lead a clean life, then maybe you'll want to convert the text showing to a hyperlink. In my example, I didn't have http:// showing, so I added it to the address. (Change this if necessary--here's hoping you didn't have too many FTP://, http:// differences!) Option Explicit Sub testme1() Dim myCell As Range Dim myRange As Range Set myRange = Selection For Each myCell In myRange.Cells ActiveSheet.Hyperlinks.Add anchor:=myCell, _ Address:="http://" & myCell.Text Next myCell End Sub Shawn Hesselberg wrote: I have created a worksheet with several hundred hyperlinks. Today when opening the application I discovered the links were gone. The text appears to have a link as it is still blue in colour and is underlined. Please advise? Thanks, Shawn -- Dave Peterson ec35720@msn.com |
| 1422 | Using the File/Save as Web Page.../Publish command in Excel 2002 SP-1 on a cell range, everything works as advertised except that the heavy outline border that I created around the range does not appear in the published web page, only a thin line, although it is certainly present in the worksheet. The interior grid lines in the worksheet do transfer to the published web page. I tried creating an outer heavy border around adjacent surrounding cells, thinking that the now interior heavy border was just getting clipped, but that was not the case. The line thickness just gets lost and I just end up with a thin double line frame. Do I need to set some obscure flag somewhere to get the border formatting to completely carry through to the published data? I didn't see anything at Tools/Options/Web Options... that appeared to apply. Or is this a "feature"? John D. Peterson jdpeterson@glpwgroup.com |
| 1439 | Why does Excel only allow the entry of time as a specific 'time of day' instead of a 'unit' of time? For example, in a spreadsheet which records how many hours per day each staff member is on the telephone, entering "5:25" into a cell will calculate as "5:25 AM". What I'm trying to record is not a time of day, but 5 hours and 25 minutes of time. Entering "15:25" calculates as "3:25 PM". Evaluating the actual value shows me that Excel has automatically converted it into a date serial number (i.e. formatting the same value as a date gives me "1/0/1900 3:25 PM") To abate the confusion this causes, I've always suggested my coworkers separate 'time' data-entry into three columns: Hours, Minutes, and Seconds. Then summing or averaging the data of course requires a formula which adds each column and divides by 60 or 3600 accordingly to convert to hours. Then a separate formula is required to re-convert it to hours, minutes and seconds. Am I just not seeing how to enter time as a unit or is this a function that Excel doesn't do? Any suggestions? |
| 1442 | Cathryn Excel time can be formatted the way I think you want it by going to the Format Cells dialog and choosing the custom format [h]:mm:ss. You can amend this to [h]:mm if you don't want seconds. You should now be able to do arithmetic on the entries. HTH Mike "Cathryn" <cmeaders@magellanhealth.com wrote in message news:14c4901c222c4$edf3fdc0$9ae62ecf@tkmsftngxa02... Why does Excel only allow the entry of time as a specific 'time of day' instead of a 'unit' of time? For example, in a spreadsheet which records how many hours per day each staff member is on the telephone, entering "5:25" into a cell will calculate as "5:25 AM". What I'm trying to record is not a time of day, but 5 hours and 25 minutes of time. Entering "15:25" calculates as "3:25 PM". Evaluating the actual value shows me that Excel has automatically converted it into a date serial number (i.e. formatting the same value as a date gives me "1/0/1900 3:25 PM") To abate the confusion this causes, I've always suggested my coworkers separate 'time' data-entry into three columns: Hours, Minutes, and Seconds. Then summing or averaging the data of course requires a formula which adds each column and divides by 60 or 3600 accordingly to convert to hours. Then a separate formula is required to re-convert it to hours, minutes and seconds. Am I just not seeing how to enter time as a unit or is this a function that Excel doesn't do? Any suggestions? |
| 1446 | Thanks for your help. I used the custom format which did remove the "AM" from the cell (although it's still in the formula bar.) -----Original Message----- Cathryn Excel time can be formatted the way I think you want it by going to the Format Cells dialog and choosing the custom format [h]:mm:ss. You can amend this to [h]:mm if you don't want seconds. You should now be able to do arithmetic on the entries. HTH Mike "Cathryn" <cmeaders@magellanhealth.com wrote in message news:14c4901c222c4$edf3fdc0$9ae62ecf@tkmsftngxa02... Why does Excel only allow the entry of time as a specific 'time of day' instead of a 'unit' of time? For example, in a spreadsheet which records how many hours per day each staff member is on the telephone, entering "5:25" into a cell will calculate as "5:25 AM". What I'm trying to record is not a time of day, but 5 hours and 25 minutes of time. Entering "15:25" calculates as "3:25 PM". Evaluating the actual value shows me that Excel has automatically converted it into a date serial number (i.e. formatting the same value as a date gives me "1/0/1900 3:25 PM") To abate the confusion this causes, I've always suggested my coworkers separate 'time' data-entry into three columns: Hours, Minutes, and Seconds. Then summing or averaging the data of course requires a formula which adds each column and divides by 60 or 3600 accordingly to convert to hours. Then a separate formula is required to re-convert it to hours, minutes and seconds. Am I just not seeing how to enter time as a unit or is this a function that Excel doesn't do? Any suggestions? . |
| 1472 | There are some (many?) format/content related settings that XL will reset to the default values for a PT. You can control some through the 'Preserve formatting' option of a PT. Check XL help for more or check support.microsoft.com XL2000: Changing a PivotChart Removes Series Formatting (Q215904) /default.aspx?scid=kb;EN-US;Q215904 XL2000: PivotTable Does Not Preserve Conditional Formatting (Q215986) /search/preview.aspx?scid=kb;en-us;Q215986 XL2000: Data Number Formatting Is Lost After You Create a PivotTable (Q214021) /search/preview.aspx?scid=kb;en-us;Q214021 -- Regards, Tushar Mehta www.tushar-mehta.com Microsoft MVP -- Excel -- In <1052201c22353$ef7ba230$95e62ecf@tkmsftngxs02, Jacob Enevoldsen <jen@bankdata.dk wrote How du I prevent Excel from using the default colors on the matrix instead of my definitions whenever I update the sheet it's (the pivot table) using. Technical spec. Excel 2002 on Win NT Thanks Jacob |
| 1475 | Each week new numbers get entered into the next column. Using conditional formatting I am trying to make the new column hilite if it contains a lower number than the previous week. My problem is the next column ready for next weeks numbers is all empty and all hi lited. How can I have the next column just plain blank until numbers are actually entered into the cells? Thank You |
| 1479 | In cell B1, Format - Conditional Formatting from the main menu. First condition is: if cell value is "equal to" ="", format is nothing. Second condition is: if cell value is "less than" =A1, format is (pattern fill red); you put whatever format you want in parentheses. Copy this cell, and paste special thru the end of the column, selecting format only. Do this for all consecutive columns you expect to have data. Be sure that you don't use specefic cell reference (=$A$1), or all conditions will refer to cell A1, and not the cell on the corresponding row. Check this by checking conditional formatting on various cells, ensuring that the cell reference is one cell to the left of where you are now. This should do it for you. "James Beam" <jimbeam@onemain.com wrote in message news:Lg2V8.1390$Dh3.116485@newsread2.prod.itd.earthlink.net... Each week new numbers get entered into the next column. Using conditional formatting I am trying to make the new column hilite if it contains a lower number than the previous week. My problem is the next column ready for next weeks numbers is all empty and all hi lited. How can I have the next column just plain blank until numbers are actually entered into the cells? Thank You |
| 1482 | With this week in column A, and next week in column B, try: Check "Formula Is", and enter: =AND(A1B1,B1<0) HTH RD "James Beam" <jimbeam@onemain.com wrote in message news:Lg2V8.1390$Dh3.116485@newsread2.prod.itd.earthlink.net... Each week new numbers get entered into the next column. Using conditional formatting I am trying to make the new column hilite if it contains a lower number than the previous week. My problem is the next column ready for next weeks numbers is all empty and all hi lited. How can I have the next column just plain blank until numbers are actually entered into the cells? Thank You |
| 1489 | I have been trying to get excel to plot the string "NA" as "NA" within a graph (say for a data label). The problem is that excel interprets "NA" as 0, and consequently plots all 0's as "NA." This is with the following formatting/equation applied to the data label: [="NA"]"NA";0 Does anyone know of a way to get Excel to plot "NA" within the data sheet as "NA" in the graph, without using linked text or text boxes which disconnect the data from the data sheet, while maintaing the ability to plot zeros as zeros? |
| 1490 | I have been trying to get excel to plot the string "NA" as "NA" within a graph (say for a data label). The problem is that excel interprets "NA" as 0, and consequently plots all 0's as "NA." This is with the following formatting/equation applied to the data label: [="NA"]"NA";0 Does anyone know of a way to get Excel to plot "NA" within the data sheet as "NA" in the graph, without using linked text or text boxes which disconnect the data from the data sheet, while maintaing the ability to plot zeros as zeros? |
| 1496 | I don't think this is possible. Excel plots the position of numbers on the grid. What should it use as the position of a point with the text-value NA? If you want to display the text, I think you'll need a macro to update the data labels. On Thu, 4 Jul 2002 15:35:17 -0700, "Bear" <db@billsfan.net wrote: I have been trying to get excel to plot the string "NA" as "NA" within a graph (say for a data label). The problem is that excel interprets "NA" as 0, and consequently plots all 0's as "NA." This is with the following formatting/equation applied to the data label: [="NA"]"NA";0 Does anyone know of a way to get Excel to plot "NA" within the data sheet as "NA" in the graph, without using linked text or text boxes which disconnect the data from the data sheet, while maintaing the ability to plot zeros as zeros? |
| 1502 | I think you're right, particularly since as you put it, "excel plots the position of numbers on the grid," and I assume you're suggesting that it will not know what to plot a text string as, whetever that text string may be. So it instead just interprets a text string as zero. One of the things I thought of (as an experiment only) was using a value that would be extremely unlikely to be used otherwise in the datasheet in place of NA's (say 999999 for example), and then use custom number formatting as such: [=999999]"NA";0 That works in creating the desired effect, but it is obviously a nightmare for practical usage. It requires me using a duplicate and hidden data series so that we don't see that massive columns/lines that would create (that series data labels being the only visible sign of the series itself). Also EVERYONE else who uses and updates the sheet to recognize that this value is being used to refer to the char string "NA". It's silly, but a lot of times doing things like that leads us to a better understanding of how the software works and maybe even a better way of doing it. So, if its impossible I can sleep. I just thought maybe there was something I was missing. Thanks. -----Original Message----- I don't think this is possible. Excel plots the position of numbers on the grid. What should it use as the position of a point with the text-value NA? If you want to display the text, I think you'll need a macro to update the data labels. On Thu, 4 Jul 2002 15:35:17 -0700, "Bear" <db@billsfan.net wrote: I have been trying to get excel to plot the string "NA" as "NA" within a graph (say for a data label). The problem is that excel interprets "NA" as 0, and consequently plots all 0's as "NA." This is with the following formatting/equation applied to the data label: [="NA"]"NA";0 Does anyone know of a way to get Excel to plot "NA" within the data sheet as "NA" in the graph, without using linked text or text boxes which disconnect the data from the data sheet, while maintaing the ability to plot zeros as zeros? . |
| 1503 | Thanks Nick! I went back to Excel and selected the thickest border and got the desired result which appears to survive re-publishing. John D. Peterson jdpeterson@glpwgroup.com "Nick Hodge" <nick_hodge@btinternet.com wrote in message news:uwLu5XtICHA.2232@tkmsftngp12... John Borders don't seem to transfer well in XL to HTML. Open the resulting file in notepad and look for the Border setting. Increasing the number parameter may help. -- HTH Nick Hodge Southampton, England Microsoft MVP - Excel nick_hodge@btinternet.com Excel XP\WinXP "John Peterson" <johndpeterson@hotmail.com wrote in message news:Oxutl2rICHA.2060@tkmsftngp11... Using the File/Save as Web Page.../Publish command in Excel 2002 SP-1 on a cell range, everything works as advertised except that the heavy outline border that I created around the range does not appear in the published web page, only a thin line, although it is certainly present in the worksheet. The interior grid lines in the worksheet do transfer to the published web page. I tried creating an outer heavy border around adjacent surrounding cells, thinking that the now interior heavy border was just getting clipped, but that was not the case. The line thickness just gets lost and I just end up with a thin double line frame. Do I need to set some obscure flag somewhere to get the border formatting to completely carry through to the published data? I didn't see anything at Tools/Options/Web Options... that appeared to apply. Or is this a "feature"? John D. Peterson jdpeterson@glpwgroup.com |
| 1512 | Cells can be locked or unlocked. You can set this option for range of cells through the menubar: Format|Cells|Protection Tab. But setting the cells locked property has no effect until the worksheet is protected. That can be done via the menubar: Tools|protection|Protect sheet (with or without a password). By default, all the cells are locked. You have to unlock the cells that you want to be able to change. (But you can select all the cells (ctrl-A) and lock/unlock all of them. This might be useful if the number of locked cells is small, but the number of locked cells is large.) Be aware that if you protect the sheet there are a lot of things that can't be done (the list is different with different versions of excel). But even formatting (bolding/font color/fill color) might not be available on a protected worksheet. And (this is not part of your question), the protection scheme isn't really used as a security measure. It's used as a "don't do anything stupid to this cell" measure. There have been many posts on how to remove protection from a password protected worksheet. In fact, since my memory is terrible, I usually protect the sheet w/o a password. It stops the stupid stuff, but allows me to make changes later without finding that small postit note with the password scribbled on it. wj wrote: I'm trying to set up a form to use for "what if" scenario's to enter info and try different combinations of figures (a lot like a loan payment calculator). Is it possible to protect the formula so I don't overwrite it when I go back into the worksheet. I'm new to this, obviously. Thanks -- Dave Peterson ec35720@msn.com |
| 1617 | I'm trying to download and convert a database from html to Excel. I saved the html as a Word doc, then tried to convert the Word doc into Excel. Excel says the Word doc is not a valid file type. I can convert to WordPerfect 5.0 and convert with some success, but there's so much garbage and editing necessary that this is not a feasible option. I can't get Excel to recognize any of the Word formats. When I try to save as a text file the formatting is lost. Is it possible to convert HTML into Excel? I have WindowsXP H.E. and OfficeXP S.B.E. |
| 1622 | did you try just doing File=Open in Excel and put in the URL or the html file if it is on your local disk? Phil <pdonald@cox-internet.com wrote in message news:uieusbnm1c1md9@corp.supernews.com... I'm trying to download and convert a database from html to Excel. I saved the html as a Word doc, then tried to convert the Word doc into Excel. Excel says the Word doc is not a valid file type. I can convert to WordPerfect 5.0 and convert with some success, but there's so much garbage and editing necessary that this is not a feasible option. I can't get Excel to recognize any of the Word formats. When I try to save as a text file the formatting is lost. Is it possible to convert HTML into Excel? I have WindowsXP H.E. and OfficeXP S.B.E. |
| 1626 | I tried the File=Open ploy but all I got was an endless message saying "Connecting to web server.." . Never did connect. After searching through the menu bar I found Data=Import External Data=New Web Query that opened a dialogue box that allowed me to plug in the URL. Works like a champ! Thanks for the help. Phil "Tom Ogilvy" <twogilvy@msn.com wrote in message news:O9KpnJVJCHA.1876@tkmsftngp12... did you try just doing File=Open in Excel and put in the URL or the html file if it is on your local disk? Regards, Tom Ogilvy Phil <pdonald@cox-internet.com wrote in message news:uieusbnm1c1md9@corp.supernews.com... I'm trying to download and convert a database from html to Excel. I saved the html as a Word doc, then tried to convert the Word doc into Excel. Excel says the Word doc is not a valid file type. I can convert to WordPerfect 5.0 and convert with some success, but there's so much garbage and editing necessary that this is not a feasible option. I can't get Excel to recognize any of the Word formats. When I try to save as a text file the formatting is lost. Is it possible to convert HTML into Excel? I have WindowsXP H.E. and OfficeXP S.B.E. |
| 1720 | Try modifying this code for your use Dim sqlstring, connstring, Count, i As Integer 'Update the Part Group box with the appropriate group for the selected P/N Sheets("Query2").Activate sqlstring = "SELECT DISTINCT PART_GRP.F_NAME" _ & " FROM iqs.dbo.PART_GRP PART_GRP, iqs.dbo.PART_DAT PART_DAT" _ & " WHERE PART_GRP.F_PTGP = PART_DAT.F_PTGP AND ((PART_DAT.F_NAME='" & UserForm1.ComboBox1 & "'))" connstring = "ODBC;DSN=spc at ipt-sql;UID=Greg;APP=Microsoft® Query;WSID=GREG2;DATABASE=iqs;Trusted_Connection=Yes" With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("B1"), Sql:=sqlstring) .FieldNames = False .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlOverwriteCells '.RefreshStyle = xlInsertDeleteCells .SavePassword = True .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery:=False '.Refresh End With HTH, Greg "Calle" <calleo@hotmail.com wrote in message news:1740d01c226bc$4611bd30$35ef2ecf@TKMSFTNGXA11... Is it possible to execute sql code in oracle from Excel with VBA? If so, what's the code? Below is my atempt: With ActiveSheet.QueryTables.Add (Connection:= "ODBC;DSN=orcl;UID=rare;PWD=rare;DBQ=rms; DBA=W;APA=T;PFC=1;TLO=0;", _ Destination:=Range("A1")) .CommandText = "desc temprare1;" .Refresh |
| 1742 | Greg, you are a hero thank you very much!!! Calle -----Original Message----- Try modifying this code for your use Dim sqlstring, connstring, Count, i As Integer 'Update the Part Group box with the appropriate group=20 for the selected P/N Sheets("Query2").Activate sqlstring =3D "SELECT DISTINCT PART_GRP.F_NAME" _ & " FROM iqs.dbo.PART_GRP PART_GRP,=20 iqs.dbo.PART_DAT PART_DAT" _ & " WHERE PART_GRP.F_PTGP =3D PART_DAT.F_PTGP=20 AND ((PART_DAT.F_NAME=3D'" & UserForm1.ComboBox1 & "'))" connstring =3D "ODBC;DSN=3Dspc at ipt- sql;UID=3DGreg;APP=3DMicrosoft=AE Query;WSID=3DGREG2;DATABASE=3Diqs;Trusted_Connection=3DYes" With ActiveSheet.QueryTables.Add (Connection:=3Dconnstring, Destination:=3DRange("B1"), Sql:=3Dsqlstring) .FieldNames =3D False .RowNumbers =3D False .FillAdjacentFormulas =3D False .PreserveFormatting =3D True .RefreshOnFileOpen =3D False .BackgroundQuery =3D True .RefreshStyle =3D xlOverwriteCells '.RefreshStyle =3D xlInsertDeleteCells .SavePassword =3D True .SaveData =3D True .AdjustColumnWidth =3D True .RefreshPeriod =3D 0 .PreserveColumnInfo =3D True .Refresh BackgroundQuery:=3DFalse '.Refresh End With HTH, Greg "Calle" <calleo@hotmail.com wrote in message news:1740d01c226bc$4611bd30$35ef2ecf@TKMSFTNGXA11... Is it possible to execute sql code in oracle from Excel with VBA? If so, what's the code? Below is my atempt: With ActiveSheet.QueryTables.Add (Connection:=3D "ODBC;DSN=3Dorcl;UID=3Drare;PWD=3Drare;DBQ=3Drms; DBA=3DW;APA=3DT;PFC=3D1;TLO=3D0;", _ Destination:=3DRange("A1")) .CommandText =3D "desc temprare1;" .Refresh . |
| 1760 | PMFJI, Where is this macro? Does it reside in the same workbook that you want to send? If yes, then this line: ActiveWorkbook.Close SaveChanges:=False Closes the workbook (which contains the code) and these two lines: Kill ("C:\ProtectorQuote.xls") Workbooks.Open Filename:=wkname are never seen (as well as the "end if" and "end sub") ----- I was testing your macro (I had it in the same workbook as the one that needs to be sent) and it just disappeared on me when I was stepping through the code. ---- On the otherhand, if the activeworkbook is not the workbook containing the code, then you can ignore this answer! bruce wrote: Hi Dave I don't get any error messager - it just doesn't seem to do anything. I have included the macro below, but i'll explain it here: A user clicks on an email button, which prompts them for an address. once entered, the users gets prompted to save the file, then i do a whole lot of formatting so that the emailed version is not editable. I then save that file and email it. At this stage i want to close and delete the second file and reopen the original saved file. hope this makes some sense! thanks bruce Sub SendIt() Dim EmailAddress EmailAddress = InputBox("Please enter the email address below.", "Email Quote") If EmailAddress < "" Then Application.ScreenUpdating = False InitPath = "c:\" FName = Application.GetSaveAsFilename (InitialFileName:=InitPath, filefilter:="Excel File (*.xls), *.xls") ActiveWorkbook.SaveAs Filename:=FName wkname = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name ActiveSheet.Unprotect Password:="phxy" Range("B15:Z1015").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("B13:L13").Select Application.CutCopyMode = False Selection.ClearComments Range("H12:M12").Select Selection.ClearComments Range("J12").Select Selection.Font.ColorIndex = 2 Selection.Interior.ColorIndex = 2 Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With Selection.Borders(xlEdgeTop).LineStyle = xlNone With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With Selection.Borders(xlEdgeRight).LineStyle = xlNone Range("M12").Select Selection.Font.ColorIndex = 2 Selection.Interior.ColorIndex = 2 Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone Selection.Borders(xlEdgeLeft).LineStyle = xlNone Selection.Borders(xlEdgeTop).LineStyle = xlNone With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With Selection.Borders(xlEdgeRight).LineStyle = xlNone Range("M1:N6").Select Selection.ClearContents Range("P10:Z10").Select Selection.ClearComments ActiveSheet.Shapes("Button 65").Select Selection.Cut ActiveSheet.Shapes("Button 64").Select Selection.Cut ActiveSheet.Shapes("Button 63").Select Selection.Cut ActiveSheet.Shapes("Button 19").Select Selection.Cut ActiveSheet.Shapes("Button 26").Select Selection.Cut ActiveSheet.Shapes("Button 50").Select Selection.Cut ActiveSheet.Shapes("Button 20").Select Selection.ShapeRange.IncrementLeft -132 ActiveSheet.Shapes("Button 67").Select Selection.ShapeRange.IncrementLeft -190.5 Cells.Select Selection.Locked = True Selection.FormulaHidden = True Range("B16").Select ActiveSheet.Protect Password:="phxy" ChDir "C:\" ActiveWorkbook.SaveAs Filename:="C:\ProtectorQuote.xls", FileFormat:=xlNormal, _ Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _ CreateBackup:=False Application.Dialogs(xlDialogSendMail).Show arg1:=EmailAddress, arg2:="Protector Health Quote: " & Date ActiveWorkbook.Close SaveChanges:=False Kill ("C:\ProtectorQuote.xls") Workbooks.Open Filename:=wkname End If End Sub -----Original Message----- Hi Bruce As long as the names and paths are correct it should work. Maybe you need to use something like: ChDir "C:\" as you are in another directory? What is the error message you get? -- \ Get 8 Add-ins in one! For less than the price of 1 /Services/AddinExamples.htm FREE EXCEL NEWSLETTER /News/2home.htm "bruce" <bruce@nha.co.za wrote in message news:160a101c2265f$b87aa830$19ef2ecf@tkmsftngxa01... | I have these two lines at the end of a macro which don't | work. I am trying to delete the file called quote and | open the one called Quote2 (wkname=C:\Quote2.xls). Is | there something I am doing wrong? | | Kill ("C:\Quote.xls") | Workbooks.Open Filename:=wkname | | . -- Dave Peterson ec35720@msn.com |
| 1763 | As Gord wrote, 32k per cell (but only about 1000 are viewable). But try formatting your cell as General. I've had long entries show ### if the cell was formatted as text. (Might not be your problem, though.) Paul Dowling wrote: I set up a billing system in Excel. Some cells are formatted to word wrap. I enter the description of work performed into these cells. My understanding is that I can fit up to 65000 characters into these cells, but it is not working. If I enter more than two or three sentences, the cell show me ########## symbols instead of word wrapping the entire text. This also happens if I cut and paste large amounts of text from Outlook. Any ideas? -- Dave Peterson ec35720@msn.com |
| 1772 | Not really a strange question. In fact, xl2002 now has those options built into the worksheet protect dialog. The bad news is for the most part, you can't have the user change these things without unprotecting the worksheet. The one exception in your list is group/ungroup. If you protect the sheet in code like (I'll include the .enableautofilter, just for heck of it): ActiveSheet.EnableAutoFilter = True ActiveSheet.EnableOutlining = True ActiveSheet.Protect contents:=True, userInterfaceOnly:=True If you go with the .enable stuff, it has to be reset each time you open the workbook. So put it in your auto_open/workbook_open code. And by using the userinterfaceonly option, you could create macros that replicate the formatting capabilities. The macro will do the work. The user calls the macro (button/dropdown from a menu). Johnny Trubshaw wrote: Hello, I hide all of the formulas in my spreadsheets at work by password protecting, to stop other people overwriting the formulas by mistake, however I would still like to have all of the formatting and certain menu items restored. IE, Font colour, font size fill colour, column width, group and ungroup. Strange question I grant you, but there is a good reason for it. Thanking you in advance Cordially Yours Johnny -- Dave Peterson ec35720@msn.com |
| 1790 | When I open a .csv file, Excel often changes things slightly which causes problems. Examples 1. If the date in the .csv is displayed as yyyy-mm-dd excel will change this to m/d/yy. 2. A zip code or any number with a zero at the beginning will have the zero removed. Is there a way to stop Excel from making these changes to the data? If I resaved the file as a .csv and didn't make custom formats for everything, dates and numbers in the new .csv are changed and use Excel's default formatting. |
| 1799 | In the Main Document in Word, press Alt+F9 to reveal the field codes. In the number field, add a formatting switch after the field name. For example, if the field name is Average, the code would look similar to the following: { MERGEFIELD Average \# "#,##0.00" } Press Alt+F9 to hide the codes, and click the View Merged Data button to check the results. Richard Rosell wrote: I am using Excel data in a Word mail merge document. When the data being merged is derived from a formula, the number that appears in the word document has a long string of decimals even when I have set the number to two places in excel. For example: the number 78.50 in Excel may appear as 78.56666666666 in the Word document. Is there a way I can format the number in Word or Excel to remedy this problem? Thanks, Richard -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 1846 | If you mean a combo box from the Control toolbox or the Forms toolbar, make sure it's completely within the cell, then drag the Fill handle. Rows to which it's being copied should be the same height as the original cell's row. If it's a dropdown from Data Validation, just drag the Fill handle. You can use the right mouse button while you drag, and copy the formatting only. Ann Withington wrote: I know how to crete drop downs in Excel. But wanted to know if there was a quicker way to copy the drop down box to all the rows in the same column without the tediuos task of copying and pasting. Like an AutoFill for example. -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 1925 | I posted similiar - no help. First I had Office SBE installed. Then I installed Office Pro. I ended up uninstalling all and actually deleting windows directory (not subdirectories though). I reinstalled windows and only Office Pro. I get "COULD NOT OPEN MACRO STORAGE" when I go into Macro, Visual Basic when I have a template loaded that I have previously created. This is Win 98 SE. I want to avoid reformatting if possible. I'll try anything (just about anyway). By the way, Excel give me Class not registered Looking for object with CLSID. I did the regsvr32 c:\windows\system\msstkprp.dll - didn't work. |
| 1941 | Are you sure that you only have numbers in the cells in that Word table. If I included text 1234asdf1234, it (obviously) wouldn't change format or add to a =Sum(). If it turns out that you have extra spaces, has some code (I think he created it for working with html stuff) at: /dmcritchie/excel/join.htm#trimall That cleans spaces (and some other characters). One proc is named TrimAll and another one is named RemoveAllSpaces. ======== And this might work for you: after you copy from word, right click on the receiving cell and Paste Special. I got an option for Text. This loses all the formatting of the table, though. John Phillips wrote: Hi, I'm trying to save data from a table created in MSWord2000 into an MSExcel2000 file. A simple cut & paste gets the data into MSExcel OK but it then refuses to let me change the format of the columns into numbers or currency, hence I can't use the mathimatical functions within MSExcel (which is why I wanted it there in the first place !). I believe there are similar functions available in MSWord as in MSExcel, but I'm an old dog with no time for new tricks and prefer to use MSExcel for 'sum's'. Grateful for any help received. John P -- Dave Peterson ec35720@msn.com |
| 1963 | Hey! That works pretty well. The only problem is that I then I would have to use text boxes for the X category labels. Which I find better than disconnecting the data labels of the series from the data sheet. Let me see if I can come up with a similar trick that solved that problem without using text boxes, but that really is food for thought at least. Thanks! -----Original Message----- This might not be the best solution, but see if this idea will get you close. Don't know what type of chart you have. Select a column of data, and do a Bar chart. Some of your data have the text "na." On step 2 of the wizard, select the series tab. For "Category (x) axis labels" select your same range of data. On step 3 of the wizard, select the data labels tab, and check the "Category name." The text "na" should show up at the bottom of the chart of your missing data. See if this would be a good compromise. -- Dana DeLouis Windows XP & Office XP = = = = = = = = = = = = = = = = = "Bear" <db@billsfan.net wrote in message news:1523101c223b7$483a68d0$b1e62ecf@tkmsftngxa04... I think you're right, particularly since as you put it, "excel plots the position of numbers on the grid," and I assume you're suggesting that it will not know what to plot a text string as, whetever that text string may be. So it instead just interprets a text string as zero. One of the things I thought of (as an experiment only) was using a value that would be extremely unlikely to be used otherwise in the datasheet in place of NA's (say 999999 for example), and then use custom number formatting as such: [=999999]"NA";0 That works in creating the desired effect, but it is obviously a nightmare for practical usage. It requires me using a duplicate and hidden data series so that we don't see that massive columns/lines that would create (that series data labels being the only visible sign of the series itself). Also EVERYONE else who uses and updates the sheet to recognize that this value is being used to refer to the char string "NA". It's silly, but a lot of times doing things like that leads us to a better understanding of how the software works and maybe even a better way of doing it. So, if its impossible I can sleep. I just thought maybe there was something I was missing. Thanks. -----Original Message----- I don't think this is possible. Excel plots the position of numbers on the grid. What should it use as the position of a point with the text-value NA? If you want to display the text, I think you'll need a macro to update the data labels. On Thu, 4 Jul 2002 15:35:17 -0700, "Bear" <db@billsfan.net wrote: I have been trying to get excel to plot the string "NA" as "NA" within a graph (say for a data label). The problem is that excel interprets "NA" as 0, and consequently plots all 0's as "NA." This is with the following formatting/equation applied to the data label: [="NA"]"NA";0 Does anyone know of a way to get Excel to plot "NA" within the data sheet as "NA" in the graph, without using linked text or text boxes which disconnect the data from the data sheet, while maintaing the ability to plot zeros as zeros? . . |
| 1967 | I'm sure this is an easy fix but i just can't work it out. My default currency (ie when i click on the $ icon on the formatting bar) has somehow changed to the UK Pound symbol. I can't figure out how to change it back to the $ symbol. My location settings in the control panel are correct and the currency icon there shows a $. I've also tried deleting all the custom formats featuring the pound sign but all that did was give me no response at all when i click the $ formatting icon in a cell. Hope that all makes sense. Appreciate any advice thanks Brad |
| 1978 | Mark, For example, if your values are in column A, select column A, the select Format | Conditional formatting... Then from the dropdown choose "Formula is" with a formula of =AND(COUNTIF(A:A,A1)<1,NOT(ISBLANK(A1))) and set the format/ patterns tab to red. HTH, Bernie "Mark" <markbazzacco@hotmail.com wrote in message news:17d4701c2287d$9c2727b0$39ef2ecf@TKMSFTNGXA08... I have a spreadsheet of several hundred contacts, each with a unique ID number. Overtime a few duplicates have appeared. How can I set up the ID column so if an ID appears twice in that column, both ID numbers turn say red? Thanks Mark |
| 1992 | On Thu, 11 Jul 2002 11:01:31 +1000, "BC" <noneofyourbusiness@nospam.com wrote: I'm sure this is an easy fix but i just can't work it out. My default currency (ie when i click on the $ icon on the formatting bar) has somehow changed to the UK Pound symbol. I can't figure out how to change it back to the $ symbol. My location settings in the control panel are correct and the currency icon there shows a $. I've also tried deleting all the custom formats featuring the pound sign but all that did was give me no response at all when i click the $ formatting icon in a cell. Hope that all makes sense. Appreciate any advice The tool button applies whatever format that you have defined as the "Currency" STYLE. (NB: Not to be confused with the Currency FORMAT that you see in the Format-Cells dialog.) To modify the Currency style, go to the Format menu, select Style, choose Currency from the Style Name combo box, then click on the Modify button to select the format that you want. You should find that the button works correctly again. --------------------------------------------------------- Hank Scorpio apolloXVIII@ozemail.com.au Change XVIII to 18 for real address. |
| 2000 | If I have 3 different text values in 3 cells, how do I concatenate the 3 to form a date that is recognised as a date? For Example A1 = 31 A2 = Jul A3 = 02 A4=CONCATENATE(A1,A2,A3) = 31Jul02 but held as a text format. I want to be able to carry out some conditional formatting based on the date displayed but when in text format I don't get the result I need. Thanks in advance. Gina |
| 2001 | Sussed it. Need to use the DATEVALUE function Following on so.... A5=DATEVALUE(A4) "Gina Liddle" <grl@hta-arch.co.uk wrote in message news:#RTpAALKCHA.2548@tkmsftngp11... If I have 3 different text values in 3 cells, how do I concatenate the 3 to form a date that is recognised as a date? For Example A1 = 31 A2 = Jul A3 = 02 A4=CONCATENATE(A1,A2,A3) = 31Jul02 but held as a text format. I want to be able to carry out some conditional formatting based on the date displayed but when in text format I don't get the result I need. Thanks in advance. Gina |
| 2002 | If you reference your result (A4) in a formula from another cell (eg A5=A4+1) you will find that A5 will display correctly However it does not seem to work if you simply enter A5=A4 Hope this helps -----Original Message----- If I have 3 different text values in 3 cells, how do I concatenate the 3 to form a date that is recognised as a date? For Example A1 = 31 A2 = Jul A3 = 02 A4=CONCATENATE(A1,A2,A3) = 31Jul02 but held as a text format. I want to be able to carry out some conditional formatting based on the date displayed but when in text format I don't get the result I need. Thanks in advance. Gina . |
| 2003 | Gr=FCezi Gina Gina Liddle schrieb: = If I have 3 different text values in 3 cells, how do I concatenate the = 3 to form a date that is recognised as a date? = For Example = A1 =3D 31 A2 =3D Jul A3 =3D 02 = A4=3DCONCATENATE(A1,A2,A3) =3D 31Jul02 but held as a text format. = I want to be able to carry out some conditional formatting based on the= date displayed but when in text format I don't get the result I need. Try this formula: A4=3DVALUE(CONCATENATE(A1,"-";A2;"-",A3)) -- = Mit freundlichen Gr=FCssen Thomas Ramel - MVP f=FCr Microsoft-Excel - 4853.ch/Schlesinger |
| 2006 | enter Jul as 7 and try DATE function =DATE(A1, A2, A3) HIH U¿ytkownik "Gina Liddle" <grl@hta-arch.co.uk napisa³ w wiadomo¶ci news:#RTpAALKCHA.2548@tkmsftngp11... If I have 3 different text values in 3 cells, how do I concatenate the 3 to form a date that is recognised as a date? For Example A1 = 31 A2 = Jul A3 = 02 A4=CONCATENATE(A1,A2,A3) = 31Jul02 but held as a text format. I want to be able to carry out some conditional formatting based on the date displayed but when in text format I don't get the result I need. Thanks in advance. Gina |
| 2010 | Hi Dave (& Sam) Thanks for your reposnse(s) - I seem to have solved my problem by cutting & pasting into Excel, deleting the currency symbol (using the 'MID' function), then changing the format to 'number'. However, this doesn't take effect until I point to each cell, press f2, then hit return. I have been playing with other s/sheets where I've turned off the auto-calculation, but it's definately back on now. Bizarre !! Many thanks for your advice. R John P -----Original Message----- Are you sure that you only have numbers in the cells in that Word table. If I included text 1234asdf1234, it (obviously) wouldn't change format or add to a =Sum(). If it turns out that you have extra spaces, David McRitchie has some code (I think he created it for working with html stuff) at: /dmcritchie/excel/join.htm#trimall That cleans spaces (and some other characters). One proc is named TrimAll and another one is named RemoveAllSpaces. ======== And this might work for you: after you copy from word, right click on the receiving cell and Paste Special. I got an option for Text. This loses all the formatting of the table, though. John Phillips wrote: Hi, I'm trying to save data from a table created in MSWord2000 into an MSExcel2000 file. A simple cut & paste gets the data into MSExcel OK but it then refuses to let me change the format of the columns into numbers or currency, hence I can't use the mathimatical functions within MSExcel (which is why I wanted it there in the first place !). I believe there are similar functions available in MSWord as in MSExcel, but I'm an old dog with no time for new tricks and prefer to use MSExcel for 'sum's'. Grateful for any help received. John P -- Dave Peterson ec35720@msn.com . |
| 2042 | No. Controls from the Forms toolbar have very few formatting options. John Walkenbach For Excel tips, macros, & downloads... -walk.com/ss "Mike" <dmcsoco@mongol.net wrote in message news:1537c01c228ee$99ac0430$36ef2ecf@tkmsftngxa12... I'm using Excel 2000. When I insert a control button from the Forms Toolbar to run a macro in a spreadsheet is there any way that I can change the background color of the control button? Many thanks for help and suggestions, Mike. |
| 2072 | It might be just a little bit easier to format that *empty* cell as you wish, prior to copying. HTH RD "Karren" <kbeedonohoe@stny.rr.com wrote in message news:17a3501c2291b$b6e10050$19ef2ecf@tkmsftngxa01... Deborah, THANKS It worked!!! Karren -----Original Message----- To change the data into numbers: 1. Select an empty cell 2. Click the Copy button 3. Select the column of numbers 4. Choose EditPaste Special 5. Select Add, click OK Then apply the custom number format of 000 Karren wrote: Tim, When the data is brought into excel from an Oracle Database or Access, I lose the leading 0's. With the cells set to number and the formatting set to 000 or however many digits I needed, the 0's would re-appear in the cells. Now this no longer works. I don't know where to go to Complain or how maybe something else will do the same thing. As Text I can't get my leading 0's to come back. Karren -----Original Message----- It sounds like some of your data is numbers and some is text. A change in format won't affect text. (If they were all text with leading zeros, they would sort correctly, but there are other reasons for preferring numbers over text.) Tim C "Karren" <kbeedonohoe@stny.rr.com wrote in message news:1721b01c228f0$5973ab50$b1e62ecf@tkmsftngxa04... In Excel 97 I could use a custom format such as 000 to force a 1 to be 001. This is very important to a number of our codes. I can't use text for this purpose because numbers don't sort correctly as text, ie 021 wii before 003. In Excel 2000 this no longer works. Is there another way to do this?? Thanks Karren . -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html . |
| 2094 | Hi there, Maybe someone can give me a hand here... I am using the conditional formatting in excel but I would like to have more than 3 condition options. Lets say that I have 10 persons and when I insert the name of one of those persons in the sheet, the color of the text changes depending of the name. I can make it... but only with 3 options... Is there another way? Thank you so much Oscar |
| 2130 | Hi Oscar, You might try using filtering as an alternative, which would allow you to select an individual based on value in a column and see only those items. Select column A (for instance), data, filter, auto filter User drop down arrow to select person. Filtered lists can be copied/pasted, printed as if that is the only data in the worksheet. If you want the same effect as you had with Conditional Formatting you might want to use an Event macro. Look for topic Change Color of Cell depending on first letter (A to G) within Worksheet Events and Workbook Events /dmcritchie/excel/event.htm You may have trouble distinguishing text colors on laptops, probably want to use interior color instead. Example above uses interior color. More information on color and selecting a colorindex number. /dmcritchie/excel/colors.htm HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel Macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "UFA" <cpjustica.ufa@mail.telapc.pt wrote in message news:agm3q8$2lh$1@venus.telepac.pt... Hi there, Maybe someone can give me a hand here... I am using the conditional formatting in excel but I would like to have more than 3 condition options. Lets say that I have 10 persons and when I insert the name of one of those persons in the sheet, the color of the text changes depending of the name. I can make it... but only with 3 options... Is there another way? Thank you so much Oscar |
| 2173 | I understood the OP as wanting to add a button directly to the worksheet--not to a toolbar. I think both of you are correct but are answering different questions! So the question is now: Did the OP mean adding a forms button to a worksheet or to a commandbar? RagDyer wrote: I'm probably totally misunderstanding the question, but I *always* apply different colors to buttons I add to toolbars that fire macros. I add these buttons by right clicking in the toolbar and <Customize <Commands tab Scroll down in the "Categories" window and click on "Forms" Then, in the "Commands" window, click and drag the "button" icon to the toolbar. I then click on "Modify" and assign my macro, and then click on "EditButtonImage" and paint it, or the background, almost any color I wish. On some occasions, I change the button image itself. Is this not what the OP requested??? Regards, RD "John Walkenbach" <john@j-walk.com wrote in message news:O9kyY$OKCHA.2500@tkmsftngp10... No. Controls from the Forms toolbar have very few formatting options. John Walkenbach For Excel tips, macros, & downloads... -walk.com/ss "Mike" <dmcsoco@mongol.net wrote in message news:1537c01c228ee$99ac0430$36ef2ecf@tkmsftngxa12... I'm using Excel 2000. When I insert a control button from the Forms Toolbar to run a macro in a spreadsheet is there any way that I can change the background color of the control button? Many thanks for help and suggestions, Mike. -- Dave Peterson ec35720@msn.com |
| 2178 | This sounds like you could just use a regular worksheet formula (like =a1-b1). But after you put your formula in, take a look at Format|conditional Formatting. You'll get up to 3 conditions (and it sounds like you'll only need the first) to specify special conditional formatting. Fulvio wrote: I'd like to create a new function (excel 2000) that, for example, makes the difference between two cells and marks the cell red if the difference is <0. I can do it with a macro but i'd like to call it as a function so that any time I change a value, it will automatically calculate the new value. Any idea how to do it? Thanks, Fulvio -- Dave Peterson ec35720@msn.com |
| 2186 | Just to add One reason Dave isn't suggesting a User defined function which you could use in a worksheet is because such a function can only return a value to a cell - it can't change colors or otherwise affect the environment. Dave Peterson <ec35720@msn.com wrote in message news:3D3028C0.51E5AD7@msn.com... This sounds like you could just use a regular worksheet formula (like =a1-b1). But after you put your formula in, take a look at Format|conditional Formatting. You'll get up to 3 conditions (and it sounds like you'll only need the first) to specify special conditional formatting. Fulvio wrote: I'd like to create a new function (excel 2000) that, for example, makes the difference between two cells and marks the cell red if the difference is <0. I can do it with a macro but i'd like to call it as a function so that any time I change a value, it will automatically calculate the new value. Any idea how to do it? Thanks, Fulvio -- Dave Peterson ec35720@msn.com |
| 2187 | Col A: 0.000123456 0.00123456 0.0123456 0.123456 Col B is custom format 000000.E+0: 000123.E-6 001235.E-6 012346.E-6 123456.E-6 Note that the second value is 001235.E-6 and not 001234.E-6. If you want that: Col C is custom format 000000.E+0 with a formula =TRUNC(A2*10^6)/10^6 000123.E-6 001234.E-6 012345.E-6 123456.E-6 Note that the third value is 012345.E-6 and not 012346.E-6. If you want that, you'll need to play with the round/trunc formulas "RocketmanX34" <bhohl@bigfoot.com wrote in message news:17f3001c22a23$dceb1110$9ae62ecf@tkmsftngxa02... Anyone know how to get a range of numbers formatted with Scientific Notation to all have consistant exponents? For example, if I desire to report units in micro-radians (i.e. x10^-6 radians) for the numbers: .000123, .012345, 0.123456 (before formatting to Scientific Notation), they would format such that only one digit is in front of the decimal, i.e.: 1.23E-4, 1.2345E-2, and 1.23456E-1. What I would LIKE to see is 123.E-6, 12345.E-6, and 123456.E-6. Any insight would greatly be appreciated! |
| 2198 | Joe Try looking at Format Conditional formatting. Mike "joe8888" <joexie@myself.com wrote in message news:185a501c229fe$24fbf800$3bef2ecf@TKMSFTNGXA10... I have two colunm of data and want to compare them so that cells in first colunm will be "bold" if these cells do not match with any cells in the second colunm. thank you very much for your help Joe |
| 2199 | Closest to your request: custom format: #####0.E+0 That one suppresses the leading zeroes. "Joe" <Joe@bogus.net wrote in message news:u4y4ZxnKCHA.2512@tkmsftngp10... Col A: 0.000123456 0.00123456 0.0123456 0.123456 Col B is custom format 000000.E+0: 000123.E-6 001235.E-6 012346.E-6 123456.E-6 Note that the second value is 001235.E-6 and not 001234.E-6. If you want that: Col C is custom format 000000.E+0 with a formula =TRUNC(A2*10^6)/10^6 000123.E-6 001234.E-6 012345.E-6 123456.E-6 Note that the third value is 012345.E-6 and not 012346.E-6. If you want that, you'll need to play with the round/trunc formulas "RocketmanX34" <bhohl@bigfoot.com wrote in message news:17f3001c22a23$dceb1110$9ae62ecf@tkmsftngxa02... Anyone know how to get a range of numbers formatted with Scientific Notation to all have consistant exponents? For example, if I desire to report units in micro-radians (i.e. x10^-6 radians) for the numbers: .000123, .012345, 0.123456 (before formatting to Scientific Notation), they would format such that only one digit is in front of the decimal, i.e.: 1.23E-4, 1.2345E-2, and 1.23456E-1. What I would LIKE to see is 123.E-6, 12345.E-6, and 123456.E-6. Any insight would greatly be appreciated! |
| 2248 | I have a column of numbers. In the adjacent column, I want to bring each number over, but add 24% to each of the numbers. [Example: A2: "23.34" I want B2 to equal: C2+(.24*C2). ] Then I can just drag and copy the formatting. I just need to know what to put as the formula. Thanks |
| 2250 | You pretty much have the formula. If the data (numbers) are in column A (starting in A1), in B1 put this formula: =A1+(A1*0.24) HTH, Laura "Pat K" <krott5333@hotmail.com wrote in message news:15df801c22b68$8ebfe8d0$9ee62ecf@tkmsftngxa05... I have a column of numbers. In the adjacent column, I want to bring each number over, but add 24% to each of the numbers. [Example: A2: "23.34" I want B2 to equal: C2+(.24*C2). ] Then I can just drag and copy the formatting. I just need to know what to put as the formula. Thanks |
| 2251 | Pat Don't know where C2 came from. To increase a number in A2 enter in B2 =A2*1.24 and drag/copy down. Or copy column A to B then in an empty cell enter 1.24 Copy this cell, select your range of numbers in B and Paste SpecialMultiply HTH Gord Dibben Excel MVP - XL97 SR2 On Sun, 14 Jul 2002 11:59:19 -0700, "Pat K" <krott5333@hotmail.com wrote: I have a column of numbers. In the adjacent column, I want to bring each number over, but add 24% to each of the numbers. [Example: A2: "23.34" I want B2 to equal: C2+(.24*C2). ] Then I can just drag and copy the formatting. I just need to know what to put as the formula. Thanks |
| 2291 | I installed WindowsXP Pro a couple of months ago and then I installed OfficeXP to replace Office 2K. One fairly sizable (though not enormous) .xls file that I created under Windows 2K Pro and Office 2K contained 5 or 6 columns of notes; no formulae or anything tricky, just notes in text. Now, I'm trying to work with that file under WindowsXP and OfficeXP. However, when I open the file, all that's present is the heading that I created to identify the content of each column; content that represented a very extensive amount of work. Yet, ALL of the content is missing. The funny thing is that I installed WindowsXP and Office XP on a brand new hard drive, formatting that drive in the process. The disk drive that contained Win2k Pro is still in the computer, though the WINNT directory has been removed. Nevertheless, all of the files that existed under Win2k Pro are still on the old Win2k Pro hard drive and more still are on a third hard drive that I used under Win2K Pro. Having just run a search for any other instances of the .xls file in question, I have found nothing so far. If anyone here has any idea what may have become of the content of that.xls file I would be forever grateful for their help in recovering it. -- With kindest regards, Dick Smith Dick@Dallas.net |
| 2292 | Please ignore my original posting on this subject. -- With kindest regards, Dick Smith Dick@Dallas.net "Dick Smith" <dick@dallas.net wrote in message news:OIQSYBDLCHA.2436@tkmsftngp11... I installed WindowsXP Pro a couple of months ago and then I installed OfficeXP to replace Office 2K. One fairly sizable (though not enormous) .xls file that I created under Windows 2K Pro and Office 2K contained 5 or 6 columns of notes; no formulae or anything tricky, just notes in text. Now, I'm trying to work with that file under WindowsXP and OfficeXP. However, when I open the file, all that's present is the heading that I created to identify the content of each column; content that represented a very extensive amount of work. Yet, ALL of the content is missing. The funny thing is that I installed WindowsXP and Office XP on a brand new hard drive, formatting that drive in the process. The disk drive that contained Win2k Pro is still in the computer, though the WINNT directory has been removed. Nevertheless, all of the files that existed under Win2k Pro are still on the old Win2k Pro hard drive and more still are on a third hard drive that I used under Win2K Pro. Having just run a search for any other instances of the .xls file in question, I have found nothing so far. If anyone here has any idea what may have become of the content of that.xls file I would be forever grateful for their help in recovering it. -- With kindest regards, Dick Smith Dick@Dallas.net |
| 2302 | Jason, Thanks for your help - it worked! I now have another query. I would like my dates to change a different colour once the 4 monthly check has passed - so that I know work has been carried out on those dates and I don't need to worry about them anymore. I can get post 4 month dates to change colour for one day i.e. day(today())-1 but I would like them to stay permanently changed - is this possible/ Tks Vicky On Mon, 15 Jul 2002 13:06:18 -0700, "Jason Morin" <jason.morin@us.exel.com wrote: If your dates start in A1, select A1 all the way to the bottom of your date list. Go to Format Conditional Formatting, select "Formula is" and insert this: =A1=DATE(YEAR(TODAY()),MONTH(TODAY())+4,DAY(TODAY())) Format as desired. This will highlight the date that is exactly 4 months away from today's date. If you want to highlight all dates 4 months from today and beyond, use this: =A1=DATE(YEAR(TODAY()),MONTH(TODAY())+4,DAY(TODAY())) HTH Jason Atlanta, GA -----Original Message----- Hi, I would like to be able to highlight dates, in red, from a list when they are 4 months from today's date. Any help would be great Vicky . |
| 2304 | Otto, If you type the following in cells A1:A3: '1/1/02 '3/3/99 '4/4/00 and sort the cells, they'll stay in that order. If you enter the following formula in cell B1: =A1+1 you'll get 37258 as the result, even though A1 contains text. If you copy it down, each original date is increased by one, and the result is a number, not text. This new column sorts properly. From reading the OP's messages, I wasn't sure if she had tried sorting this new column, or if she had only tried creating the formula, so I thought it might at least be worth trying. I don't know if a corrupt sheet would do that, but she could copy those few cells to another sheet, and find out. Debra Otto Moehrbach wrote: Debra Appreciate your help, but the OP said that the dates resulting from the A1+1 wouldn't sort either. Any ideas? She said that other workbooks sorted fine. Could a corrupt sheet do this? Also, if the date 15 July 02 were text, wouldn't it be true that it would not have a 5-digit number base? And adding 1 to it would then equal 1? Thanks for your help. Otto "Debra Dalgleish" <dsd@contextures.com wrote in message news:3D332D68.7090008@contextures.com... Even if the dates are entered as text, when you use the formula: A1+1 in an adjacent cell, you'll get a date as a result. Try the following, which will convert 'text' dates to real dates, then sort the column: 1. Select an empty cell and copy it. 2. Select the dates 3. Choose EditPaste Special 4. Select Add, click OK amber wrote: I did try that...same thing. It doesn't do anything... amber -----Original Message----- Amber Do that "A1+1" thing for each of your date cells (or just a few) and then see if the new dates will sort. HTH Otto "amber" <amber_hancock@abicon.com wrote in message news:18c9e01c22c2a$b7ed8380$9be62ecf@tkmsftngxa03... Hi again. It is formatted as a date. If I put your formula in another cell (A1+1) it does go to the next date. I'm baffled. I've played with all different formatting, but it will not sort. The only odd thing about this data is this: I have a 3rd party add-in that I use to get my data. It automatically populates my columns and rows (e.g. A1:B2). I cannot sort this data, so I have another column where I have (=A1, =A2 etc). These are dates, but I cannot sort them. I get no error messages, just nothing happens. I do this same thing in other spreadsheets and it works fine (I've double checked). I'm getting a bit frustrated!! Thanks again, amber -----Original Message----- Amber I would say that your dates are not recognized as dates by Excel. Do this. Select another cell away from these cells. Say A1 is one of your date cells. Type in "=A1+1" without the quotes. If A1 contains a date instead of text, then the above formula should return the date of the next day. If not, then delete the dates, format the cells General, and enter the dates again. HTH Otto "amber" <amber_hancock@abicon.com wrote in message news:18f5801c22c1c$ddba5d80$3bef2ecf@TKMSFTNGXA10... Hello, I'm sure I'm doing something wrong, because this has to be simple... I have a 'date' column and a 'number' column. There are two rows of data, e.g. 15-July-02 0.0 04-July-02 0.0 I am trying to sort these 4 cells, so that the 4th of July is ABOVE the 15th, but nothing will work. What am I doing wrong???? Thanks, amber . . -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 2319 | Ooh. Good point. Maybe the formula to check should be closer to: =ISNUMBER(a1) (or just formatting the cells as general and see if they look like one of those big numbers) Debra Dalgleish wrote: Otto, If you type the following in cells A1:A3: '1/1/02 '3/3/99 '4/4/00 and sort the cells, they'll stay in that order. If you enter the following formula in cell B1: =A1+1 you'll get 37258 as the result, even though A1 contains text. If you copy it down, each original date is increased by one, and the result is a number, not text. This new column sorts properly. From reading the OP's messages, I wasn't sure if she had tried sorting this new column, or if she had only tried creating the formula, so I thought it might at least be worth trying. I don't know if a corrupt sheet would do that, but she could copy those few cells to another sheet, and find out. Debra Otto Moehrbach wrote: Debra Appreciate your help, but the OP said that the dates resulting from the A1+1 wouldn't sort either. Any ideas? She said that other workbooks sorted fine. Could a corrupt sheet do this? Also, if the date 15 July 02 were text, wouldn't it be true that it would not have a 5-digit number base? And adding 1 to it would then equal 1? Thanks for your help. Otto "Debra Dalgleish" <dsd@contextures.com wrote in message news:3D332D68.7090008@contextures.com... Even if the dates are entered as text, when you use the formula: A1+1 in an adjacent cell, you'll get a date as a result. Try the following, which will convert 'text' dates to real dates, then sort the column: 1. Select an empty cell and copy it. 2. Select the dates 3. Choose EditPaste Special 4. Select Add, click OK amber wrote: I did try that...same thing. It doesn't do anything... amber -----Original Message----- Amber Do that "A1+1" thing for each of your date cells (or just a few) and then see if the new dates will sort. HTH Otto "amber" <amber_hancock@abicon.com wrote in message news:18c9e01c22c2a$b7ed8380$9be62ecf@tkmsftngxa03... Hi again. It is formatted as a date. If I put your formula in another cell (A1+1) it does go to the next date. I'm baffled. I've played with all different formatting, but it will not sort. The only odd thing about this data is this: I have a 3rd party add-in that I use to get my data. It automatically populates my columns and rows (e.g. A1:B2). I cannot sort this data, so I have another column where I have (=A1, =A2 etc). These are dates, but I cannot sort them. I get no error messages, just nothing happens. I do this same thing in other spreadsheets and it works fine (I've double checked). I'm getting a bit frustrated!! Thanks again, amber -----Original Message----- Amber I would say that your dates are not recognized as dates by Excel. Do this. Select another cell away from these cells. Say A1 is one of your date cells. Type in "=A1+1" without the quotes. If A1 contains a date instead of text, then the above formula should return the date of the next day. If not, then delete the dates, format the cells General, and enter the dates again. HTH Otto "amber" <amber_hancock@abicon.com wrote in message news:18f5801c22c1c$ddba5d80$3bef2ecf@TKMSFTNGXA10... Hello, I'm sure I'm doing something wrong, because this has to be simple... I have a 'date' column and a 'number' column. There are two rows of data, e.g. 15-July-02 0.0 04-July-02 0.0 I am trying to sort these 4 cells, so that the 4th of July is ABOVE the 15th, but nothing will work. What am I doing wrong???? Thanks, amber . . -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html -- Dave Peterson ec35720@msn.com |
| 2376 | I don't think the UK is one of the countries for which Excel doesn't offer negative number formatting in parensthesis under accounting. You can enter the following under custom format. Format, cells, custom _(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_) if you can't get it under Format, cells, accounting, (2 places, no symbol) It would be best to have something like -500,000.00 in the activecell when you set up formatting as you will be able to see it formatted in you are in custom. Incidentally if you have a formatting close to what you want you can modify it by reselecting cell and looking/modifying it under custom. see some examples in /dmcritchie/excel/formula.htm HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel Macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "cathy reynolds" <cgreynolds@blueyonder.co.uk wrote in message news:1626a01c22c98$a46aa9a0$a5e62ecf@tkmsftngxa07... Can't believe this is not obvious. Although Excel treats figures typed between brackets as negative when calculating a formula, if the RESULT is negative I can't get it to appear in brackets. Have tried changing the number format (no option for negative figures in brackets) and also the Number options on Regional Settings, Control Panel. The latter does let me select negative figures in brackets, but appears to have no effect on the spreadsheet (even after rebooting!) Can someone help please? Thanks |
| 2401 | Have you tried formatting your cells as text *before* you import your data? HTH RD "WU" <wuxx0153@us.sina.com wrote in message news:18f5201c22c89$f8148030$9be62ecf@tkmsftngxa03... We try to summary our data from sveral output files into an Excel 2000 file. In some case our ID is "5-9976", but when this data transfer into Excel, it automatically change to "may-76". Its getting annoy if we have several hundreds data to summary. Is there anyway we can make Excel left my text data along and keep what it was? |
| 2428 | no, it is only coping and pasting, no formatting or printing involved. And the Task Pane Clipboard is disactivated -----Original Message----- Are you accessing any print options in the Macro? "elliot" <derybosi@noos.fr wrote in message news:1988e01c22d1a$ed089f00$3bef2ecf@TKMSFTNGXA10... I am running Excel macros that copy and paste data (lots of copy and paste) into an other workbook. Using XL97, this macro took 7 sec. to execute with a Pentium3 128Mb ram. Now with a Pentium 4 256Mb ram it takes at least 50 sec. for Excel XP to execute the same macro. I have tried many other macros and the result is always the same, XP much slower even with more powerfull PC (these macros have Application.Calculation = xlManual and screen updating turned off). Does anyone know a solution to this problem? Thanks Eric . |
| 2454 | This might possibly help... =IF(ISTEXT(A1),VALUE(A1),A1) Put into a new column, this formula will evaluate the contents of cell A1 (used as an example), detect if it is text and then replace it with the equivalent (correct) numeric value. If the data in A1 is already a number it just copies it. When testing this I entered 0025 in A1 and the formula resulted with 25. remember that in Access it is possible to enter numbers into a text field, but not the other way round. I guess it's that which is causing your problem. When converting the data to Excel, the system is reading the Data Type of the Access field and formatting the Excel cell accordingly. Thus any leading zeros (which canot be a number) are coming through as text. Anyhow, try the formula above, which Ihope helps Phil z <pzassoc@yahoo.com wrote in message news:1970401c22cfc$069fc570$3bef2ecf@TKMSFTNGXA10... I have data thatt I have exported from Access to Excel. The data design in Access is Text data type even though the data enetered in the field is numeric. The data also may have a leading zero in front the data. When brought into Excel I tried to change the leading zero in two records and sort by that colunm in ascending order. Two rows refused to be sorted no matter what I did. I custom formatted the numbers , cleared all formatting and re-typed the data,forced the data by cutting and pasting in the position they should be but no matter what those two rows would not sort when I sort it goes right back to not being sorted. What is going on here and how do I fix it? Z |
| 2481 | Use conditional formatting (menu: Format-Conditional Formatting). Should be self-explanatory. -- Earl K. Takasaki Please reply to the group. "Trevor Bishop" <Trev5084@yahoo.com.au wrote in message news:16ca501c22d5e$6ac227c0$9ee62ecf@tkmsftngxa05... Hi! I would like to know how to code a worksheet to do the following: If a number entered in a range of cells say ("B3:F9") to which I have named Week One If the number entered in activecell is between "3" and "11" then Enter as normal Elseif number entered in activecell is < 3 then font color = Red and Msgbox (Sugar level Too Low) Elseif number entered in activecell is 11 then font color = Red and Msgbox (Sugar level Too High) Endif I would appreciate your help Thank You Trev |
| 2488 | Without opening your workbook, I'm not sure if this will work for you. This solution assumes you can enter the year and month number somewhere on each worksheet. In this example, the year is in B2 and the month number in C2. Enter the following formula in cell A6: =DATE(B2,C2,1)-CHOOSE(WEEKDAY(DATE(B2,C2,1)),6,0,1,2,3,4,5) Use A6+1 in the the cell below, as you describe. If you want to hide the dates that are in the month before or after the current month, use conditional formatting. For example, Formula Is: Month(A1)<C2, with white font. David Burgess wrote: I want to have a file, reusable each year, that has 12 sheet for each month. In each month it is seperated by weeks, Monday through Sunday, totalling invoices and sales. I have set up each day as a date (=A6+1, =A7+1, etc.). Where A41 is Jan/31/02 (Thursday) and A42 is Feb/1/02 (Friday) in the month of January, how do I set it up to show Feb/1/02 starting on Friday in the month of Febuary? Right now I have to enter Mondays date into week one of each month. And what can be done about Leap Year? Any help would be great! Thanks! David -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 2494 | This is a problem which I am having at the moment and I will be interested in theresponses you get. However, I found that if you publish the pivottable as html with a limited number of fields actually used within the pivottable, the rest unused in the field list, the actual performance of drag-and-drop from the field list and the re-formatting of the fields is much improved. -----Original Message----- I currently have a pivottable generated by olap club, but as data size is huge (~ 50Mb). Does anybody knows any macros commands, add-in components or other tools that can help to improve the pivottable performance (i.e pivotitem change etc...)? Thanks! . |
| 2499 | Hi Craig For your future reference, email attachments should not be added to postings within the NG, for a number of reasons. The vast majority of people will not open them for fear of virus infection. They take up a huge amount of bandwidth and clog up the servers. They take a long time to download, and can be expensive for those readers paying by the minute for their connection time. However, I did look at your Workbook and you can achieve your result as follows. On your Landscape sheet cell B2 enter =Master!A2 cell B3 enter =OFFSET(Master!$A$2,COLUMN()-2,ROW()-1) and copy down through cells B4:B16 Use the Copy command, then Paste Special=Formulas to achieve this, so as to preserve the formatting of your cells. cell C1 enter the same formula and copy down through cells C2:C16 copy C1:C16 and Paste Special=Formulas through cells D1:AJ1 Under Tools=Options=View uncheck the Display Zero box -- Regards, Roger Govier Technology 4 U "Craig Westlake" <westlake.craig@saambulance.com.au wrote in message news:nocZ8.37034$Hj3.110759@newsfeeds.bigpond.com... I have a problem that I need to get resolved, but I am just getting frustrated. On the example workbook I have three sheets. The Master sheet is simply raw data that will be manipulated from time to time. The objective is to transpose that data onto a sheet that is formatted in a landscape format so that when the Master sheet is changed the landscape reflects that change. I thought that this would be a simple matter of transposing a formula (I thought I may have to create a intermediate sheet with a reference to the Master sheet.). But whatever I try I get odd results. Please not that this is just a very small subset of the data I will be using. Ultimately I will be using a large number of 35 cell blocks (in landscape) running down the page. I hope this makes sense and somebody can give me a solution. What seemed simple appears not to be. Thankyou Craig |
| 2501 | Hi Paul Mark the range of all of your cells Go to Format=Conditional Formatting and select the drop down arrow to use Formula Is In the white pane, type =A1=yournumber Choose Format and select either a Font Colour or Cell colour as appropriate. Substitute the number you are looking for in place of "yournumber" in the formula above. Better still, enter the number you are looking for in another cell e.g AA1 and make the formula =A1=$AA$1 Then, change the number in AA1 to have all of the cells in the table take on colour attributes you have set. -- Regards, Roger Govier Technology 4 U "Paul Simon" <psimon@yahoo.com wrote in message news:#YId3pYLCHA.2508@tkmsftngp08... I have a table of numbers. I also have a particular number that I want to locate within that table. There may be multiple iterations of that number. I want to find all of the incidences and highlight them (either change the background so they are visible or change the text color so they are visible). Any suggestions on how to go about doing this would be greatly appreciated.... Many thanks in advance.... Paul |
| 2503 | Then, change the number in AA1 to have all of the cells in the table take on colour attributes you have set. Apologies, that should have read Then, change the number in AA1 to have all of the cells in the table matching the value in cell AA1 take on the colour attributes you have set. -- Regards, Roger Govier Technology 4 U "Roger Govier" <roger@technology4u.co.uk wrote in message news:##3#MxYLCHA.2688@tkmsftngp11... Hi Paul Mark the range of all of your cells Go to Format=Conditional Formatting and select the drop down arrow to use Formula Is In the white pane, type =A1=yournumber Choose Format and select either a Font Colour or Cell colour as appropriate. Substitute the number you are looking for in place of "yournumber" in the formula above. Better still, enter the number you are looking for in another cell e.g AA1 and make the formula =A1=$AA$1 Then, change the number in AA1 to have all of the cells in the table take on colour attributes you have set. -- Regards, Roger Govier Technology 4 U "Paul Simon" <psimon@yahoo.com wrote in message news:#YId3pYLCHA.2508@tkmsftngp08... I have a table of numbers. I also have a particular number that I want to locate within that table. There may be multiple iterations of that number. I want to find all of the incidences and highlight them (either change the background so they are visible or change the text color so they are visible). Any suggestions on how to go about doing this would be greatly appreciated.... Many thanks in advance.... Paul |
| 2510 | Perhaps if you refused to look at his workbook he, and others, would be discouraged from sending attachments. -- Don Guillett SalesAid Software Granite Shoals, TX donaldb@281.com "Roger Govier" <roger@technology4u.co.uk wrote in message news:OzQ2VtYLCHA.2380@tkmsftngp09... Hi Craig For your future reference, email attachments should not be added to postings within the NG, for a number of reasons. The vast majority of people will not open them for fear of virus infection. They take up a huge amount of bandwidth and clog up the servers. They take a long time to download, and can be expensive for those readers paying by the minute for their connection time. However, I did look at your Workbook and you can achieve your result as follows. On your Landscape sheet cell B2 enter =Master!A2 cell B3 enter =OFFSET(Master!$A$2,COLUMN()-2,ROW()-1) and copy down through cells B4:B16 Use the Copy command, then Paste Special=Formulas to achieve this, so as to preserve the formatting of your cells. cell C1 enter the same formula and copy down through cells C2:C16 copy C1:C16 and Paste Special=Formulas through cells D1:AJ1 Under Tools=Options=View uncheck the Display Zero box -- Regards, Roger Govier Technology 4 U "Craig Westlake" <westlake.craig@saambulance.com.au wrote in message news:nocZ8.37034$Hj3.110759@newsfeeds.bigpond.com... I have a problem that I need to get resolved, but I am just getting frustrated. On the example workbook I have three sheets. The Master sheet is simply raw data that will be manipulated from time to time. The objective is to transpose that data onto a sheet that is formatted in a landscape format so that when the Master sheet is changed the landscape reflects that change. I thought that this would be a simple matter of transposing a formula (I thought I may have to create a intermediate sheet with a reference to the Master sheet.). But whatever I try I get odd results. Please not that this is just a very small subset of the data I will be using. Ultimately I will be using a large number of 35 cell blocks (in landscape) running down the page. I hope this makes sense and somebody can give me a solution. What seemed simple appears not to be. Thankyou Craig |
| 2528 | Micah In the ThisWorkBook object place your 2 macros. Sub WorkBook_Open() '''do your formatting End Sub Sub WorkBook_BeforeClose() '''do whatever End Sub If you use the Macro Recorder to record your steps while building the Macros, it will place the code in a General Module. Copy that code and stick it in ThisWorkBook where the ''' marks are. HTH Gord Dibben Excel MVP - XL97 SR2 On Wed, 17 Jul 2002 12:42:08 -0700, "Micah" <mmcowen@goldmanenvironmental.com wrote: How do I make a macro (dealing with internal file formatting changes) in my file that will run every time I open the file, and, similarly, one that will run every time I close the file? I want Excel to set itself up in a certain way to work with this file. Thanks |
| 2544 | This custom formatting worked for me: ##\.###\.###\/####-## This too. ##"."###"."###"/"####-## The backslash says to make the next character a literal (don't see a period as a decimal point--just see it as another character). Enclosing those characters in double quotes is about the same thing. Rubens wrote: Hi, I'm trying to create a pesonalized formatting for a cell ##.###.###/####-##. Excel is including dots I didn't type. Ex: 12.123.123./123.4-12 Thanks for any help -- Dave Peterson ec35720@msn.com |
| 2559 | I just recorded a macro with my home printer (doesn't support 11x17). I changed it A4 and got this in the macro: With ActiveSheet.PageSetup '<<snipped .PaperSize = xlPaperA4 '<<snipped End With But I think that using this parm varies with printers. Some support it and some don't. (That's the way I _think_ it works and your experience seems to confirm it.) One option might be to use sendkeys and a combination of tabbing and enters (good luck on that!). Another one would be to create a second printer driver that uses the same physical printer, but set up for 11x17 (or 8.5x11). Then you could swap printers. Print your stuff. Swap back. This doesn't seem too bad if you're the only user--it seems pretty ugly if there are multiple users and multiple printers, though. Ben wrote: I dont see where you change the paper size -- or paper print tray Neither do I. I recorded the macro (rather than writing the code for it), since I don't know how to write in VB. When I recorded it, though, the paper size change doesn't seem to show up in the code. Do you know the code that I would add to accomplish this? Thanks, -Ben "Ben" <ben@rageco.com wrote in message news:1751101c22dc4$ac5b4de0$2ae2c90a@hosti ng.microsoft.com... : I'm having problems with a macro. I recorded a macro : that I want to do several things: 1) Change the print : orientation of one worksheet from portrait to : landscape. 2) Change the paper print size of the : worksheet from 11x17 to 8-1/2x11 (letter). 3) Print the : worksheet at that size (8-1/2 by 11) from a number of : different custom views, and then 3) Change the paper : print size back to the original 11 by 17, and restore the : portrait orientation setting. : : All of the above is working fine except for one thing: : when I run the macro, it prints at the original paper size : (11x17), instead of 8-1/2x11. The macro DOES print in : landscape, though, so it's getting that part of the : formatting right. How can I make it recognize the : desired change in paper size, as well? I've pasted the : macro below for reference. Thanks for any help. : : -Ben : : : : Sub ProdOverviewSheet() : ' : Sheets("CurrentJobs").Select : With ActiveSheet.PageSetup : .PrintTitleRows = "$4:$4" : .PrintTitleColumns = "" : End With : ActiveSheet.PageSetup.PrintArea = "" : With ActiveSheet.PageSetup : .LeftHeader = "&C" : .CenterHeader = "&R&""Copperplate Gothic : Light,Italic""&24&D" : .RightHeader = "&""Copperplate Gothic : Light,Italic""&24&D" : .LeftFooter = "" : .CenterFooter = "Page &P of &N" : .RightFooter = "" : .LeftMargin = Application.InchesToPoints(0) : .RightMargin = Application.InchesToPoints(0) : .TopMargin = Application.InchesToPoints(0.5) : .BottomMargin = Application.InchesToPoints(0.5) : .HeaderMargin = : Application.InchesToPoints(0.25) : .FooterMargin = Application.InchesToPoints(0.25) : .PrintHeadings = False : .PrintGridlines = False : .PrintComments = xlPrintInPlace : .PrintQuality = -4 : .CenterHorizontally = True : .CenterVertically = False : .Orientation = xlLandscape : .Draft = False : .FirstPageNumber = xlAutomatic : .Order = xlDownThenOver : .BlackAndWhite = False : .Zoom = False : .FitToPagesWide = 1 : .FitToPagesTall = 1 : End With : : ActiveWorkbook.CustomViews("AdvantageFilter").Sho : w : ActiveWindow.SelectedSheets.PrintOut Copies:=1 : ActiveWorkbook.CustomViews("AdWearFilter").Show : ActiveWindow.SelectedSheets.PrintOut Copies:=1 : ActiveWorkbook.CustomViews("CPSFilter").Show : ActiveWindow.SelectedSheets.PrintOut Copies:=1 : : ActiveWorkbook.CustomViews("DesignsbyJFilter").Sh : ow : ActiveWindow.SelectedSheets.PrintOut Copies:=1 : ActiveWorkbook.CustomViews("EchoFilter").Show : ActiveWindow.SelectedSheets.PrintOut Copies:=1 : ActiveWorkbook.CustomViews("EncoreFilter").Show : ActiveWindow.SelectedSheets.PrintOut Copies:=1 : : ActiveWorkbook.CustomViews("Flappy'sFilter").Show : ActiveWindow.SelectedSheets.PrintOut Copies:=1 : ActiveWorkbook.CustomViews("PaganFilter").Show : ActiveWindow.SelectedSheets.PrintOut Copies:=1 : : ActiveWorkbook.CustomViewÍ{?wÀ ||!:rDk"rÌ ìés("StitchablesFilter").Sho : w : ActiveWindow.SelectedSheets.PrintOut Copies:=1 : Selection.AutoFilter : With ActiveSheet.PageSetup : .PrintTitleRows = "$4:$4" : .PrintTitleColumns = "" : End With : ActiveSheet.PageSetup.PrintArea = "" : -- Dave Peterson ec35720@msn.com |
| 2592 | hey try If Range("J54") < TimeValue("0:00") Then -OR- If Range("J54") < 0 Then although it says 0:00 in the cell, excel knows that time as a serial value which in the case of 0:00 is 0. 0:00 is just the formatting that you chose for that time. Timevalue converts the string (text) version "0:00" into the serial (actual) value of 0. so that above two options are roughly equivalent. take care patrick "Smiler" <stephen.a.evans@virgin.net wrote in message news:<gEmZ8.4178$Dh3.188538@newsfep1-win.server.ntli.net... Otto I'm sorry but I must be doing something wrong. You are right every time I complete a cell then the sheet recalculates and goes through the other motions for me. But cell J54 is still = 0:00 so I don't understand why the macro is running. Here's the full code I've entered so perhaps you can understand what I'm doing wrong ? Private Sub Worksheet_Calculate() If Range("J54") < "0:00" Then ActiveWindow.SelectedSheets.PrintOut Copies:=2, Collate:=True Sheets("2").Visible = True Sheets("1").Select ActiveWindow.SelectedSheets.Visible = False End If End Sub Many thanks for your continued assistance Steve |
| 2616 | John Reilly <jreilly@sprintmail.com wrote in message news:<3D3618FD.448FB1F1@sprintmail.com... I know a few slow ways. Who has the fastest way to check a row of say 25 cells verify they are all the same value in that row? . Then go to the next row and check that row to see if that row has all the same values...etc. Thank you. John You don't specify whether each row must match a pre-determined value. I assume not. If your data is numerical you can use the MODE function in conditional formatting to highlight each faulty cell (select row A2-Y2, then Format Conditonal Formatting Cell value is | not equal to | =MODE($A2:$Y2)... then select a red font colour ... then use the format painter to apply it down the sheet). Alternatively you can insert a new check column A. In cell A2 type =COUNTIF(B2:Z2,B2). Copy the formula down. If col A = 25, all is well, otherwise the row contains errant data. Or you can do both. It should take 5 minutes max. Cheers Andrew |
| 2625 | Thanks a lot, Dave. Regards "Dave Peterson" <ec35720@msn.com escreveu na mensagem news:3D35EE14.5A5FFC8A@msn.com... This custom formatting worked for me: ##\.###\.###\/####-## This too. ##"."###"."###"/"####-## The backslash says to make the next character a literal (don't see a period as a decimal point--just see it as another character). Enclosing those characters in double quotes is about the same thing. Rubens wrote: Hi, I'm trying to create a pesonalized formatting for a cell ##.###.###/####-##. Excel is including dots I didn't type. Ex: 12.123.123./123.4-12 Thanks for any help -- Dave Peterson ec35720@msn.com |
| 2653 | Do you mean after you copy it from the "master" sheet to the extracted sheet? I don't think this is what you mean, but you could do: With ActiveSheet ActiveSheet.ShowAllData .AutoFilter Field:=6, Criteria1:=.name .... But then this only works for the current name of the sheet. Here's a version that uses Data|Filter|advanced Filter to get the unique values in a column. Then it cycles through those values and extracts them to new worksheets. (It doesn't do any formatting at all!) Option Explicit Sub testme2() Dim curwks As Worksheet Dim newwks As Worksheet Dim filter_rng As Range Dim mycol As Range Dim myuniquecells As Range Dim myCell as Range Dim i As Long Set curwks = ActiveSheet If Not curwks.AutoFilterMode Then MsgBox "Please apply a filter!" Exit Sub End If If curwks.FilterMode Then curwks.ShowAllData End If Set filter_rng = ActiveSheet.AutoFilter.Range Set mycol = filter_rng.Columns(13) mycol.AdvancedFilter Action:=xlFilterInPlace, Unique:=True Set myuniquecells = mycol.Offset(1, 0).Resize(mycol.Rows.Count - 1, 1) _ .SpecialCells(xlCellTypeVisible) 'put back the autofilter filter_rng.AutoFilter For Each mycell In myuniquecells.Cells filter_rng.AutoFilter Field:=13, Criteria1:=mycell.Value Set newwks = Worksheets.Add On Error Resume Next newwks.Name = mycell.Value On Error GoTo 0 With filter_rng .SpecialCells(xlCellTypeVisible).Copy _ Destination:=newwks.Range("a1") End With Next mycell curwks.ShowAllData Application.CutCopyMode = False End Sub You apply the autofilter by hand. Adjust the Field:=13 to the one you want. And the Columns(13), too. I posted this awhile ago for someone else. Do it against a test version of your workbook (just in case!!!). Good luck, eric wrote: Any way to automate the autofilter? Say match the sheet name to apply to the filter? Thanks -----Original Message----- The first part: Try Data|Filter|Autofilter. Then they can filter by their records. If you want to copy that visible range to a new worksheet, just select, copy, paste. If you need a macro, you could record a macro when you do it once. But I'd think twice about separating the data. You may not have to separate it if you can live with the Data|filter|autofilter stuff. (But you might need to if your users have to update it at the same time.) The second part is much harder (in my opinion). If they add new rows, delete existing rows, sort it different ways, or just change the data, it really gets to be a bear really quickly. (And if there's no unique key into the files, then it becomes almost impossible (for me, anyway). If you must separate the data into different workbooks/worksheets, I think I'd recombine all the individual worksheets after they've finished updating their stuff. (Just copy|paste to the bottom. Remove any extra headers and sort nicely.) Eric wrote: I am trying to create a command button that will allow a user, on their own personal sheet, to isolate their records which are located on a separate "Master List" sheet. Above that, I would like any changes made to the returned values from the command button to be saved on the "Master List" sheet. Thanks for any help -- Dave Peterson ec35720@msn.com . -- Dave Peterson ec35720@msn.com |
| 2660 | #1. How about just two formulas: negative column: =if(sum(a1:x1)<0,sum(a1:x1),"") and positive (or 0) column if(sum(a1:x1)=0,sum(a1:x1),"") #2. You can by hitting alt-enter between the numbers. But this means the value is no longer a number--it's text. You'll have to go to special means to use it/them for subsequent calculations. (I wouldn't do it unless it was for display only--never to be used again.) And you'll have to format it yourself. Type the $ signs, the decimal point and any other formatting you want. Victor wrote: 1) it is possible to write a formula that adds a row across, and if the total is a positive number it goes to one column, if its negative it goes to a column next to it 3)can u put 2 numbers in one cell - for example $300 $500 and then those numbers included when u add a row or column? -- Dave Peterson ec35720@msn.com |
| 2740 | I'm trying to format my cells in social security format I;ve tried all the formatting like click on cell and gfo to format special, social security but it does not work it only works when i click on the cell individually twice. Is it an easier way to do it the daty is already typed in I await suggestions PT |
| 2768 | Ben Format A2 to Superscript also. Unfortunately Conditional Formatting does not allow Superscript or you could use CF. HTH Gord Dibben Excel MVP - XL97 SR2 On Sat, 20 Jul 2002 07:36:42 -0700, "Ben" <ben_landis@hotmail.com wrote: If I format the text in A1 to be superscript and then have A2 reference A1, the superscript text turns to normal script text in A2. Any ideas on how I can keep my superscript text? Thanks. |
| 2780 | Gord Thanks for the response. That works -- but what happens when I only want part of the cell to be superscript (i.e., I need to put a footnote in a cell and only need the "(1)" at the end of the rest of the text to be superscript)? Any thoughts for this problem? Thanks. -----Original Message----- Ben Format A2 to Superscript also. Unfortunately Conditional Formatting does not allow Superscript or you could use CF. HTH Gord Dibben Excel MVP - XL97 SR2 On Sat, 20 Jul 2002 07:36:42 -0700, "Ben" <ben_landis@hotmail.com wrote: If I format the text in A1 to be superscript and then have A2 reference A1, the superscript text turns to normal script text in A2. Any ideas on how I can keep my superscript text? Thanks. . |
| 2784 | If you leave it a formula, then you're out of luck. You can't format part of the results of a formula--constants (text) only. Depending on what you're doing, you could use an event (worksheet_change??) to copy from the original cell to the other cell. Ben wrote: Gord Thanks for the response. That works -- but what happens when I only want part of the cell to be superscript (i.e., I need to put a footnote in a cell and only need the "(1)" at the end of the rest of the text to be superscript)? Any thoughts for this problem? Thanks. -----Original Message----- Ben Format A2 to Superscript also. Unfortunately Conditional Formatting does not allow Superscript or you could use CF. HTH Gord Dibben Excel MVP - XL97 SR2 On Sat, 20 Jul 2002 07:36:42 -0700, "Ben" <ben_landis@hotmail.com wrote: If I format the text in A1 to be superscript and then have A2 reference A1, the superscript text turns to normal script text in A2. Any ideas on how I can keep my superscript text? Thanks. . -- Dave Peterson ec35720@msn.com |
| 2839 | Paul Take a look at Conditional Formatting. I'm sure that;ll do it. HTH Phil Paul Gooch <pgooch@knightowl.org.uk wrote in message news:O8nsbrNMCHA.2452@tkmsftngp08... Hello, Is it possible to do a rota in Excell. I would like to make a 6 week rolling rota with various locations within it, but what I am trying to work out is if a rota has either S for sick or H for holiday then the location that is not there to be hign lighted. Does that make sence? Paul |
| 2842 | Dear Paul One way to highlight specific cells is with conditional formatting. Let's say your rota code letter is in column A, and your locations are in column B. Select cell B1, then Format Conditional Formatting Formula is =A$1="S" (click the format button and select a red font colour) O.K. Click the Add button for Condition 2 where Formula is =A$2="H" (click the format button and select a bright blue font colour) O.K. O.K. With Cell B1 still selected, click on the format painter (yellow paintbrush), click in cell B2 and drag vertically down as far as you need to. Now all "Sick" locations should be in red and all "Holiday" locations should be in blue. Good luck Andrew "Paul Gooch" <pgooch@knightowl.org.uk wrote in message news:O8nsbrNMCHA.2452@tkmsftngp08... Hello, Is it possible to do a rota in Excell. I would like to make a 6 week rolling rota with various locations within it, but what I am trying to work out is if a rota has either S for sick or H for holiday then the location that is not there to be hign lighted. Does that make sence? Paul |