Inconsistent conditional formatting
Hi all Office XP service pack 2 (pack 3 not yet installed) I have a workbook that contains cells that are subject to conditional formatting. By way of example, Condition 1 of cell Q9 is set to =AND($D9>0,ISNA(MATCH($Q9,L_Charity,0))) If I select cell Q9 and then from the menu bar select Format/Conditional Formatting, then Condition 1 changes to: =AND(#REF!>0,ISNA(MATCH(#REF!,LCharity,0))) If I click on "OK", and then repeat the process, then it reverts back to =AND($D9>0,ISNA(MATCH($Q9,L_Charity,0))) It seems to alternate. When this sort of thing happens generally, my first thoughts are 1) Circular referencing? 2) Manual recalculation? 3) Iteration switched on? I have checked these and note that : Iteration is not switched on Calculation is set to fully automatic No "Circ" warning message is displayed. Is this a bug in Excel that might be solved by Service Pack 3? Is it likely that the workbook is in some way corrupt? Or finally Is there something in the formulae that could be creating this effect? If the last, any ideas about how to go looking for it? Thanks -- Return email address is not as DEEP as it appears
mind-the-gap
excel.misc
78881 articles. 
5 followers.
2 Replies
503 Views
I would guess that at some stage you changed the name of range LCharit (which produces the #REF) to L_Charity -- Message posted from http://www.ExcelForum.com
BrianB.14ah7aThanks for that. My post contained a misprint, sorry. L_Charity never got changed. Only references to specific cells got alternated between the cell ref and #REF! "BrianB >" <<BrianB.14ah7a@excelforum-nospam.com> wrote in message news:BrianB.14ah7a@excelforum-nospam.com... > I would guess that at some stage you changed the name of range LCharity > (which produces the #REF) to L_Charity. > > > --- > Message posted from http://www.ExcelForum.com/ >
Similar Artilces:
Preventing Excel Time format
An Excel (2000) spreadsheet that I use regularly has had its column formats (though fortunately not the data) corrupted following a PC crash. There's a couple of columns that I use for storing latitude and longitude data in the format eg 52:00:00. Since the corruption, Excel insists on treating this column format as Time (and adding a Date prefix if I try to edit one of the affected cells). I know I encountered this same problem previously and managed to fix it but can't remember the fix. If I select the colum and do Format Cells | Text then all the values immediately change to decima...
Can the IF() function loop through cells looking for a certain condition?
Let me first explain what I am trying to accomplish: I have 2 sheets; one named "Jobs", and the other named "Requests". Now on the "Jobs" sheet, each row contains a job, and the last column of each row, I enter an "R", if that job was a request. Here's an example of the "Jobs" sheet data: ------------------------------------------------- Date Invoice# Customer Name Request? 05/19/04 543218 Larry R 05/19/04 987345 Moe 05/19/04 343529 Fred R 05/19/04 652434 Bubba ...
keeping dates in UK format
hi, Is it possible to stop excel changing the date format when it is opene in another region. i.e from UK to US. i have created a template that is used by several people in differen regions, it uses the UK date format for filtering records by date. However when the template is opened in another region (ie USA) th dates change format, to the region (locale) set on the users computer. This stops the template working as intended. so i want the dates to stay in the UK format no matter where they ar opened. thanks -- jarv ----------------------------------------------------------------------...
Formatting toolbar in Publisher 2000
I can't expand the formatting toolbar in pub2000. I tried ghost, uninstall, reinstall, win and office updates but no luck. The icon is just a small grey square. Any ideas? I'm slow today - what do you mena by expand it? Do you mean it doesn't have all of the icons you want? Have you tried editing the toolbar? -- JoAnn Paules MVP Microsoft [Publisher] "Mikep" <anonymous@discussions.microsoft.com> wrote in message news:004701c4a0b8$dcd63930$a301280a@phx.gbl... >I can't expand the formatting toolbar in pub2000. I tried > ghost, uninstall, reinstall,...
How can I use the format function to change the stored value of a cell in excel?
How can I use the format function to change the stored value of a cel in excel? For example, In a colum I have data that is displayed like this: 19:59 12/31/69 But the actual stored data when you go to edit the cell is like this: 12/31/1969 7:59:00 PM I want the stored data to be like the displayed value so I can sort th column by the time and not the day. By the way, I do not know how to do macros or use VB.: -- Message posted from http://www.ExcelForum.com Hi you can search by date and time. No problem with this display. what are you exactly looking for -- Regards Frank Kabel Frankf...
Possible to change the default cell format?
Hello - I am constantly changing the format of my cells (when they are the number format) to include the comma separator and to use zero decimal places. Is there a way to change the settings so that by default the values will not have decimal places (and by default they will have the comma separator? Thanks for any suggestions! (FYI, I considered creating a simple macro to do this, but I believe that it would limit me from being able to use the UNDO command as often as I would like). To address the "default" number format, you would have to use a template as your default workbook...
Saving a custom format?
Greetings, I created a custom number format for use on a particular workbook. Now that custom format is available for use anywhere on that workbook. However, the custom format is not available in a new workbook or any other existing workbook. Is there a way to save the custom format so that it is available in any workbook? Thanks! Jim Orson... Hi Try putting the format in a blank workbook called Book1 and save this in your XLSTART folder -- Regards Frank Kabel Frankfurt, Germany Jim Orson wrote: > Greetings, > > I created a custom number format for use on a particular work...
Formatting Pivot Table Item Row Totals
Hello, Is there a way to automatically have the item row totals appear in bold while the detail remains unbold? For example, if I have a Pivot Table that displays the following data: State City Total Tennessee Nashville 500 Chattanooga 300 Memphis 200 Knoxville 100 Tennessee Total 1,100 I would want the number 1,100 to appear in bold while the other four numbers (the detail) are not ...
SUM of a range conditional on date
I've got 15 columns, A/c No, Description, Jan, Feb, Mar etc and YTD. I'm wanting to sum the columns conditional to a date, ie sum(C:E) when the date is 31/03/04. I've been using if statements but am limited by the maximum of 7 if's (I need 12). I'm open to any suggestions, Thanks Bria -- Message posted from http://www.ExcelForum.com Hi Maybe you are somewhat more specific. Which columns you want to sum. And how is the date you mentioned related with data in columns to sum. A general idea - you can sum every month separately, with some logical conditions controlling w...
Purchase Order Format
I would like to remove the barcode from the purchase orders and the inventory received report. I just started using RMS about 2 weeks ago and I am stuck. Please Help! open the template file in notepad and comment out the barcode section of the file: usual file location is c:\Program Files\Microsoft Retail Management System\Store Operations\ReceiptTemplates\PO.xml <!-- ========================= --> <!-- Barcode --> <!-- ========================= --> <!-- <Barcode> <STYLE> msSExtendedCode93 </STYLE> <UPCNOT...
Can Word Perfect 8 be convertee to Quattro format?
Would like to know if MS Office Pro Excell 2003 can convert Corel Word Perfect 8 Quattro spreadsheet files to it's format & if so, what is the procedure? .. Excel cannot read QP files but QP can save a file in XLS format. Would be happy to fix a few files four you. best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Coltt" <Coltt@discussions.microsoft.com> wrote in message news:BEF88A16-EC48-4B05-BC63-CCB7646E0231@microsoft.com... > Would like to know if MS Office Pro Excell 2003 can convert Corel Word > Perfect 8 Quattro s...
Find formatted cells
Hi Is there a way in VBA to scan a range of cells and pick up the references of any cells that have a particular format applied? The range may have more than one cell with the format applied and I want to be able to use the references elsewhere in the workbook. Many thanks. Jim xl2002 added the ability to find by format. Before that you could have a macro that would search through your sheets looking for cells that match a specific format. But there are lots of things that are in the format of the cell. If you limit your search criteria (font/boldness/fill color/font color), you may eve...
error C2661: 'CStdStr<CT>::Format' : no overloaded function takes 19 argumentswith[CT=char]
Hi, I am getting following error, error C2661: 'CStdStr<CT>::Format' : no overloaded function takes 19 arguments with[CT=char] If you have the solution please provide it. Thanks, Shilpa Did you compile as Unicode, but still put in an ANSI format string like ..Format("%d") rather than .Format(_T("%d")) Tom "shilpa" <bharatid@cybage.com> wrote in message news:1174467883.296564.83070@p15g2000hsd.googlegroups.com... > Hi, > > I am getting following error, > > error C2661: 'CStdStr<CT>::Format' : no overloaded ...
Unusual Date Format
I am writing an Excel 2002 template. Two columns of dates (date from and date to) are both imported from an external source. The date format is 'cyymmdd' so today would be '1100421'. The cells are formatted as text. I need to calculate the difference between the dates shown as a number of weeks, rounded down to the nearest whole number. Any help would be appreciated. =INT((DATE(LEFT(A3,3)+1900,MID(A3,4,2),RIGHT(A3,2))-DATE(LEFT(A2,3)+1900,MID(A2,4,2),RIGHT(A2,2)))/7) -- David Biddulph "Grey Old Man" <GreyOldMan@discussions.microsoft.com&g...
Formatting cells #5
I have a spreadsheet in which some cells are placing a decimal point in the numbers we enter. I think the cell thinks we are entering dollar amounts. I right click on the cell and choose format cell and on the Number Tab the Category Field is marked General and to the right it says "General format cells have no specifc number format". However in the sample box is shows 283802.07, it is placing a decimal point anyway. Any ideas on how to fix this? I just need to enter the numbers without a decimal point Thanks Anthony Tools/Options/Edit uncheck the Fixed decimal places checkbo...
Importing from Excel with HTML Formatting
A client of mine has been on a web based system for email and CRM. They now what to use Outlook and a proprietary CRM system. The online system has sent us a SQL file with all the emails and I can put those out so we can put them in Outlook, the problem is that the body of each message is in HTML and when we import the body of the message is plain text i.e. <html>text</html> which makes is super hard to read. Query: Does anyone know how to import text based messages into Outlook and have the message be in HTML format? thanks ...
Formatting a Date field into a form?????
I just know this is going to be easy. using the code below to place 2 dates into specific cells on a sheet the generates my autofilter to run a query between these dates, this bit works fine, apart from the date entry, i would like the date to show as "dd mm yy" but when i enter it into the form it appears as "mm dd yy" i know that it will be a case of adding the 'Format' to the code within the form but am having trouble exactly where to place it. Any help please Steve Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Da...
FORMAT CELLS #10
I have a worksheet. Within that worksheet I have a few cells which if I right click on gives me the menu options and when I select the format cells nothing happens. While it opens a dialog box for other cells. I wanted to protect some cells by selecting the format cells options. Can someone tell me what is happening and why nothing happens if I select the format cell option ? Thanks, San The only time I've seen this is when the original workbook was created in a different program--crystal reports or Lotus 123. I included a few "sacrificial" cells in my selection, made my...
Conditional Formatting 05-21-10
Hi, I have an input model which I have populated with data in each cell by entering =SUM(Balance_Sheet_Retrieve!A1), and so on. Some of the cells will be locked so they cannot be changed and these are coloured light green. Input cells - which can be changed are coloured light yellow. What I would like to do is use conditional formatting to change the background colour of any input cell which no longer uses the exact original formula - so, for example: if the cell just has a value OR has a different formula (even if it is the original formula plus a new one, or plus/minus a...
date in text format #2
I'm trying to do a mail merge in Publisher than has a date field. It converts it to text - and becomes a 5 digit number.Publisher is very limited in terms of mail merge power (unlike Word - but I can't use Word due to Text boxes and needing to get several on a page) . I then decided I'd convert the date field in excel into Text, so then Publisher would then read it in the correct format. However, I can't get away from the 5 digit number. I've read everything I can find about date and text. I"ve tried copy and "paste as values", etc - and I get the 5 di...
Custom Formula Requires Custom Formatting
As suggested from a previous post I am using the following formula to fill a merged cell. ="(AO) "&'Source Doc'!B49&REPT(" ",49-LEN('Source Doc'!B49&'Source Doc'!C49))&'Source Doc'!C49 I have a need to make the (AO) Bold & Italiacs, then the data from 'Source Doc'!B49 should be bold and dark blue. Leaving the data from C49 as formatted Cells that contain formulas can't have this kind of character by character formatting. Yogi_Bear_79 wrote: > > As suggested from a previous post I am using the...
get rid of time format w/msec
i have data column with the following format- 04:01:59:22 which means hh:mm:ss:msms I want to get rid of msec. They dont allow to import this csv to access db. How to convert hh:mm:ss:msms to hh:mm:ss ???????????????????? THANK YOU!! Highlight the data column with the time format. Go to Format, Cell, Number, Custom, and select h:mm:ss. Go up to Type (just above your selection) and change the h to hh. HTH, "agenda9533" wrote: > i have data column with the following format- 04:01:59:22 > which means hh:mm:ss:msms > I want to get rid of msec. They dont allow...
Multiple Conditional Formatting-HELP
I need the formula for--If K1 or L1 is less than 100, or if E1 is greater than 60, shade cell A1 to dark gray. Then I would need to carry it down the length of A column. Using Excel 2007. Thank you. Select all of the target cells and set the CF using a formula of =OR(K1<100,L1<100,E1>60) HTH Bob "Cindy" <Cindy@discussions.microsoft.com> wrote in message news:4C842BC6-ACDF-4E72-8F09-B5D2AA48987C@microsoft.com... >I need the formula for--If K1 or L1 is less than 100, or if E1 is greater > than 60, shade cell A1 to dark gray. Then I would need...
Problem With Text & number format
I hv this sample data in excel 019-1234567 019 1234567 019#1234567 %0191234567 My question 1) How to delete spacing, dashes, character etc leaving the number only..in my above sample the clean data that i want to get is 0191234567 2) I tried to use replace function (to delete dash,space etc) but the leading 0 will not be displayed. What will be the best approach? Thanks There are several options for displaying leading zeros depending on the way you want to use the strings. 1. Custom formatting the cells with format code "0000000000" (10 zero digits). 2....
Negative Cell Format
I would like to know if a cell can be fomatted negative so that any subsequent entry appears as a negative number with out having to enter the minus sign. If you only want it to "appear" to be negative: Format/Cells/Number/Custom -General;-General;0;@ Note that positive values displayed with the leading minus sign will not be treated as negative in calculations. If you want to have the values automatically be negative, try this event macro (right-click the worksheet tab and choose View Code): Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Ta...
An Excel (2000) spreadsheet that I use regularly has had its column formats (though fortunately not the data) corrupted following a PC crash. There's a couple of columns that I use for storing latitude and longitude data in the format eg 52:00:00. Since the corruption, Excel insists on treating this column format as Time (and adding a Date prefix if I try to edit one of the affected cells). I know I encountered this same problem previously and managed to fix it but can't remember the fix. If I select the colum and do Format Cells | Text then all the values immediately change to decima...
Can the IF() function loop through cells looking for a certain condition?
Let me first explain what I am trying to accomplish: I have 2 sheets; one named "Jobs", and the other named "Requests". Now on the "Jobs" sheet, each row contains a job, and the last column of each row, I enter an "R", if that job was a request. Here's an example of the "Jobs" sheet data: ------------------------------------------------- Date Invoice# Customer Name Request? 05/19/04 543218 Larry R 05/19/04 987345 Moe 05/19/04 343529 Fred R 05/19/04 652434 Bubba ...
keeping dates in UK format
hi, Is it possible to stop excel changing the date format when it is opene in another region. i.e from UK to US. i have created a template that is used by several people in differen regions, it uses the UK date format for filtering records by date. However when the template is opened in another region (ie USA) th dates change format, to the region (locale) set on the users computer. This stops the template working as intended. so i want the dates to stay in the UK format no matter where they ar opened. thanks -- jarv ----------------------------------------------------------------------...
Formatting toolbar in Publisher 2000
I can't expand the formatting toolbar in pub2000. I tried ghost, uninstall, reinstall, win and office updates but no luck. The icon is just a small grey square. Any ideas? I'm slow today - what do you mena by expand it? Do you mean it doesn't have all of the icons you want? Have you tried editing the toolbar? -- JoAnn Paules MVP Microsoft [Publisher] "Mikep" <anonymous@discussions.microsoft.com> wrote in message news:004701c4a0b8$dcd63930$a301280a@phx.gbl... >I can't expand the formatting toolbar in pub2000. I tried > ghost, uninstall, reinstall,...
How can I use the format function to change the stored value of a cell in excel?
How can I use the format function to change the stored value of a cel in excel? For example, In a colum I have data that is displayed like this: 19:59 12/31/69 But the actual stored data when you go to edit the cell is like this: 12/31/1969 7:59:00 PM I want the stored data to be like the displayed value so I can sort th column by the time and not the day. By the way, I do not know how to do macros or use VB.: -- Message posted from http://www.ExcelForum.com Hi you can search by date and time. No problem with this display. what are you exactly looking for -- Regards Frank Kabel Frankf...
Possible to change the default cell format?
Hello - I am constantly changing the format of my cells (when they are the number format) to include the comma separator and to use zero decimal places. Is there a way to change the settings so that by default the values will not have decimal places (and by default they will have the comma separator? Thanks for any suggestions! (FYI, I considered creating a simple macro to do this, but I believe that it would limit me from being able to use the UNDO command as often as I would like). To address the "default" number format, you would have to use a template as your default workbook...
Saving a custom format?
Greetings, I created a custom number format for use on a particular workbook. Now that custom format is available for use anywhere on that workbook. However, the custom format is not available in a new workbook or any other existing workbook. Is there a way to save the custom format so that it is available in any workbook? Thanks! Jim Orson... Hi Try putting the format in a blank workbook called Book1 and save this in your XLSTART folder -- Regards Frank Kabel Frankfurt, Germany Jim Orson wrote: > Greetings, > > I created a custom number format for use on a particular work...
Formatting Pivot Table Item Row Totals
Hello, Is there a way to automatically have the item row totals appear in bold while the detail remains unbold? For example, if I have a Pivot Table that displays the following data: State City Total Tennessee Nashville 500 Chattanooga 300 Memphis 200 Knoxville 100 Tennessee Total 1,100 I would want the number 1,100 to appear in bold while the other four numbers (the detail) are not ...
SUM of a range conditional on date
I've got 15 columns, A/c No, Description, Jan, Feb, Mar etc and YTD. I'm wanting to sum the columns conditional to a date, ie sum(C:E) when the date is 31/03/04. I've been using if statements but am limited by the maximum of 7 if's (I need 12). I'm open to any suggestions, Thanks Bria -- Message posted from http://www.ExcelForum.com Hi Maybe you are somewhat more specific. Which columns you want to sum. And how is the date you mentioned related with data in columns to sum. A general idea - you can sum every month separately, with some logical conditions controlling w...
Purchase Order Format
I would like to remove the barcode from the purchase orders and the inventory received report. I just started using RMS about 2 weeks ago and I am stuck. Please Help! open the template file in notepad and comment out the barcode section of the file: usual file location is c:\Program Files\Microsoft Retail Management System\Store Operations\ReceiptTemplates\PO.xml <!-- ========================= --> <!-- Barcode --> <!-- ========================= --> <!-- <Barcode> <STYLE> msSExtendedCode93 </STYLE> <UPCNOT...
Can Word Perfect 8 be convertee to Quattro format?
Would like to know if MS Office Pro Excell 2003 can convert Corel Word Perfect 8 Quattro spreadsheet files to it's format & if so, what is the procedure? .. Excel cannot read QP files but QP can save a file in XLS format. Would be happy to fix a few files four you. best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Coltt" <Coltt@discussions.microsoft.com> wrote in message news:BEF88A16-EC48-4B05-BC63-CCB7646E0231@microsoft.com... > Would like to know if MS Office Pro Excell 2003 can convert Corel Word > Perfect 8 Quattro s...
Find formatted cells
Hi Is there a way in VBA to scan a range of cells and pick up the references of any cells that have a particular format applied? The range may have more than one cell with the format applied and I want to be able to use the references elsewhere in the workbook. Many thanks. Jim xl2002 added the ability to find by format. Before that you could have a macro that would search through your sheets looking for cells that match a specific format. But there are lots of things that are in the format of the cell. If you limit your search criteria (font/boldness/fill color/font color), you may eve...
error C2661: 'CStdStr<CT>::Format' : no overloaded function takes 19 argumentswith[CT=char]
Hi, I am getting following error, error C2661: 'CStdStr<CT>::Format' : no overloaded function takes 19 arguments with[CT=char] If you have the solution please provide it. Thanks, Shilpa Did you compile as Unicode, but still put in an ANSI format string like ..Format("%d") rather than .Format(_T("%d")) Tom "shilpa" <bharatid@cybage.com> wrote in message news:1174467883.296564.83070@p15g2000hsd.googlegroups.com... > Hi, > > I am getting following error, > > error C2661: 'CStdStr<CT>::Format' : no overloaded ...
Unusual Date Format
I am writing an Excel 2002 template. Two columns of dates (date from and date to) are both imported from an external source. The date format is 'cyymmdd' so today would be '1100421'. The cells are formatted as text. I need to calculate the difference between the dates shown as a number of weeks, rounded down to the nearest whole number. Any help would be appreciated. =INT((DATE(LEFT(A3,3)+1900,MID(A3,4,2),RIGHT(A3,2))-DATE(LEFT(A2,3)+1900,MID(A2,4,2),RIGHT(A2,2)))/7) -- David Biddulph "Grey Old Man" <GreyOldMan@discussions.microsoft.com&g...
Formatting cells #5
I have a spreadsheet in which some cells are placing a decimal point in the numbers we enter. I think the cell thinks we are entering dollar amounts. I right click on the cell and choose format cell and on the Number Tab the Category Field is marked General and to the right it says "General format cells have no specifc number format". However in the sample box is shows 283802.07, it is placing a decimal point anyway. Any ideas on how to fix this? I just need to enter the numbers without a decimal point Thanks Anthony Tools/Options/Edit uncheck the Fixed decimal places checkbo...
Importing from Excel with HTML Formatting
A client of mine has been on a web based system for email and CRM. They now what to use Outlook and a proprietary CRM system. The online system has sent us a SQL file with all the emails and I can put those out so we can put them in Outlook, the problem is that the body of each message is in HTML and when we import the body of the message is plain text i.e. <html>text</html> which makes is super hard to read. Query: Does anyone know how to import text based messages into Outlook and have the message be in HTML format? thanks ...
Formatting a Date field into a form?????
I just know this is going to be easy. using the code below to place 2 dates into specific cells on a sheet the generates my autofilter to run a query between these dates, this bit works fine, apart from the date entry, i would like the date to show as "dd mm yy" but when i enter it into the form it appears as "mm dd yy" i know that it will be a case of adding the 'Format' to the code within the form but am having trouble exactly where to place it. Any help please Steve Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Da...
FORMAT CELLS #10
I have a worksheet. Within that worksheet I have a few cells which if I right click on gives me the menu options and when I select the format cells nothing happens. While it opens a dialog box for other cells. I wanted to protect some cells by selecting the format cells options. Can someone tell me what is happening and why nothing happens if I select the format cell option ? Thanks, San The only time I've seen this is when the original workbook was created in a different program--crystal reports or Lotus 123. I included a few "sacrificial" cells in my selection, made my...
Conditional Formatting 05-21-10
Hi, I have an input model which I have populated with data in each cell by entering =SUM(Balance_Sheet_Retrieve!A1), and so on. Some of the cells will be locked so they cannot be changed and these are coloured light green. Input cells - which can be changed are coloured light yellow. What I would like to do is use conditional formatting to change the background colour of any input cell which no longer uses the exact original formula - so, for example: if the cell just has a value OR has a different formula (even if it is the original formula plus a new one, or plus/minus a...
date in text format #2
I'm trying to do a mail merge in Publisher than has a date field. It converts it to text - and becomes a 5 digit number.Publisher is very limited in terms of mail merge power (unlike Word - but I can't use Word due to Text boxes and needing to get several on a page) . I then decided I'd convert the date field in excel into Text, so then Publisher would then read it in the correct format. However, I can't get away from the 5 digit number. I've read everything I can find about date and text. I"ve tried copy and "paste as values", etc - and I get the 5 di...
Custom Formula Requires Custom Formatting
As suggested from a previous post I am using the following formula to fill a merged cell. ="(AO) "&'Source Doc'!B49&REPT(" ",49-LEN('Source Doc'!B49&'Source Doc'!C49))&'Source Doc'!C49 I have a need to make the (AO) Bold & Italiacs, then the data from 'Source Doc'!B49 should be bold and dark blue. Leaving the data from C49 as formatted Cells that contain formulas can't have this kind of character by character formatting. Yogi_Bear_79 wrote: > > As suggested from a previous post I am using the...
get rid of time format w/msec
i have data column with the following format- 04:01:59:22 which means hh:mm:ss:msms I want to get rid of msec. They dont allow to import this csv to access db. How to convert hh:mm:ss:msms to hh:mm:ss ???????????????????? THANK YOU!! Highlight the data column with the time format. Go to Format, Cell, Number, Custom, and select h:mm:ss. Go up to Type (just above your selection) and change the h to hh. HTH, "agenda9533" wrote: > i have data column with the following format- 04:01:59:22 > which means hh:mm:ss:msms > I want to get rid of msec. They dont allow...
Multiple Conditional Formatting-HELP
I need the formula for--If K1 or L1 is less than 100, or if E1 is greater than 60, shade cell A1 to dark gray. Then I would need to carry it down the length of A column. Using Excel 2007. Thank you. Select all of the target cells and set the CF using a formula of =OR(K1<100,L1<100,E1>60) HTH Bob "Cindy" <Cindy@discussions.microsoft.com> wrote in message news:4C842BC6-ACDF-4E72-8F09-B5D2AA48987C@microsoft.com... >I need the formula for--If K1 or L1 is less than 100, or if E1 is greater > than 60, shade cell A1 to dark gray. Then I would need...
Problem With Text & number format
I hv this sample data in excel 019-1234567 019 1234567 019#1234567 %0191234567 My question 1) How to delete spacing, dashes, character etc leaving the number only..in my above sample the clean data that i want to get is 0191234567 2) I tried to use replace function (to delete dash,space etc) but the leading 0 will not be displayed. What will be the best approach? Thanks There are several options for displaying leading zeros depending on the way you want to use the strings. 1. Custom formatting the cells with format code "0000000000" (10 zero digits). 2....
Negative Cell Format
I would like to know if a cell can be fomatted negative so that any subsequent entry appears as a negative number with out having to enter the minus sign. If you only want it to "appear" to be negative: Format/Cells/Number/Custom -General;-General;0;@ Note that positive values displayed with the leading minus sign will not be treated as negative in calculations. If you want to have the values automatically be negative, try this event macro (right-click the worksheet tab and choose View Code): Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Ta...