Can i use conditional formating on a cell when it contains a formula? #2

=mid(text(a1;"0000000000.00");1;1

--
si
-----------------------------------------------------------------------
sit's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=262
View this thread: http://www.excelforum.com/showthread.php?threadid=26784

0
10/9/2004 9:08:26 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
342 Views

Similar Articles

[PageSpeed] 13

Hi
the reason for this is that thie formula returns a string and not a
numeric value. Change the formula to:
=--mid(text(a1;"0000000000.00");1;1)

--
Regards
Frank Kabel
Frankfurt, Germany


sit wrote:
> =mid(text(a1;"0000000000.00");1;1)

0
frank.kabel (11126)
10/9/2004 9:55:34 AM
OR ...

Change your Conditional formula to:

="0"

*IF* you really want the characters to remain as text.
-- 

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"Frank Kabel" <frank.kabel@freenet.de> wrote in message
news:%23GSsEYerEHA.2724@TK2MSFTNGP14.phx.gbl...
Hi
the reason for this is that thie formula returns a string and not a
numeric value. Change the formula to:
=--mid(text(a1;"0000000000.00");1;1)

--
Regards
Frank Kabel
Frankfurt, Germany


sit wrote:
> =mid(text(a1;"0000000000.00");1;1)


0
ragdyer1 (4060)
10/9/2004 3:36:32 PM
Reply:

Similar Artilces:

Store Operations crashes when viewing journal (F4) on register 2.
We have two registers, register 1 is the main machine where the database is located and register 2 is networked into it. Whenever you try to view the journal (F4) on register 2, the program crashes and gives this message just before closing: Run-time error '-2147417848 (80010108)': Automation error The object invoked has disconnected from its clients. Does anyone have any suggestions as to what the problem may be? Any help would be greatly appreciated. Install a default windows printer on register 2 - even if it's just something like the print to fax or MS Office Document...

if cell starts with characters formula
Hi I need to count cells in a column starting with certain characters. each cell's data varies in length. I have tried with @countif( but does not work if the cell contains other characters after the "prefix". eg. row 20 cell 5 apples row 21 cell 5 apples red row 22 cell 5 apples green row 23 cell 5 plums green row 23 cell 5 plums purple totals required for apples = 3 (regardless of colour) total required for plums = 2 (regardless of colour) @countif(C20:c30,"plums") gives answer of 1 require answer of 2 @countif(C20:c30,&quo...

Outlook 2003 keeps crashing #2
I get this message when I look in System information. Outlook keeps crashing quite often. I cannot for example delete two messages without outlook crashing. My colleagues do not have this problem. We are using the Microsoft mail exhange server. Faulting application outlook.exe, version 11.0.5510.0, stamp 3f1380f0, faulting module mso.dll, version 11.0.5606.0, stamp 3f334cce, debug? 0, fault address 0x003b6e16. Does anyone has a solution?=20 Best regards/ Bj=F6rn 1) I would definitely apply Service Pack 2for Office 2003 + applicable critical security updates. 2) Have you tried starting O...

Microsoft POS #2
I have a group of customers that I am working on setting up A bundled POS system for. I have looked at RMS and Microsoft POS. The customer's are small bulk food stores. They package all of the bulk food in various containers and weigh it with a Hobart scale producing a label with an system 2 price enbedded barcode. These are UPC A formats. They are for the most part simple single store installs and donot require the complex RMS system. I have setup the Microsoft POS version 2.0 and found it will work perfect for these customers. The only problem when you programed the system you l...

Rollup 2 and Office 2007
I already have Roll Up 1 and the Office 2007 compatibility patch installed. If I now install roll up 2 will I have to reinstall the office 2007 compatibility? yes -- Regards, MS CRM Certified Professional http://microsoftcrm3.blogspot.com Chat with me on MSN / Gmail / Skype : ID Is :.. mscrmexpert@gmail.com "LLoyd" wrote: > I already have Roll Up 1 and the Office 2007 compatibility patch installed. > If I now install roll up 2 will I have to reinstall the office 2007 > compatibility? ...

Exchange 2003 new install can not receive external email.
I have just setup a new Windows Server 2003 standard edition with Exchange 2003 standard edition on it. I have been working for a while trying to get it to receive external email. I can send out and send/ receive internal messages, but when someone trys to send me a message from outside our network they get the following returned mail message This Message was undeliverable due to the following reason: Each of the following recipients was rejected by a remote mail server. The reasons given by the server are included to help you determine why each recipient was rejected. Recipient: <**...

move cell contents
Is there a way to move a cell contents to another cell with a formula. ex: if a5="Name" then move g5 to j5? Also, I am using =INDEX(Sheet1!B3:B12,INT((RAND()*10)+1),1) to pick random names from a list. I have the formula in different place pick random names from different list. This does work, but I have different list with some of the same names and with the random pick I do not want the same name to appear. -- Thanks for any and all help. Davidl Hi David a formula can only affect the cell it is in, it can't move or change another cell for this you need some code ...

Lookup based on criteria in 2 columns
Hi, I am trying to use a vlookup or other function to return the value in the amount column based on the location and date. Here is a sample of my data: Location Date Amount 101 9/15/8 10 101 9/16/8 20 101 9/17/8 15 102 9/15/8 50 102 9/16/8 75 102 9/17/8 67 For example if I wanted to return the amount for location 102 on 9/15/8, what formula would I use? I tried using variations of vlookups but had no luck. Thanks, =SUMPRODUCT(--(A2:A50=102),--(B2:B50=DATE(2008,9,15)),C2:C50) -- Regards, Peo Sjo...

Auto-format in Microsoft Excel.
Each time i try to enter a number range, for example, 8- 10, in Excel, it constantly re-formats it to a date. If I change the formatting to "General" it turns it into a random number, usually 38209. I would like to turn off ALL auto-formatting, but that's probably asking too much. How do I disable this frustrating feature? thanks. Hi Paul When you enter "8-10" in a cell, that's not strictly a number but Excel thinks you mean a date so converts it as such. A date is a number and in your example the number 38209 represents August 10 2004 which is not a ra...

Can I send a recurring e-mail automatically each week
I want to send a e-mail with the same verbiage to the same person once a week and make it a recurrence with no end date. Can I set this up once in Outlook so it is scheduled automatically? -- Microsoft Office 2003 Version Students and Teachers Edition Windows Vista Home Premium Thank-you Happy new Year!! Carl R ...

How to keep format when importing Excel into SQL.
Can anybody help me please? When I import Excel file into SQL, a field that formated as 0000000000 (custom), loosing leading zeros. I tried to change data type in SQL after importing. No luck. I appretiate any help. See if using IMEX=1 helps: http://pratchev.blogspot.com/2007/10/importing-excel-data-into-sql-server.html -- Plamen Ratchev http://www.SQLStudio.com Plamen, I tried this, but got an error: "The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered." Thank you very much for looking into this. "Plamen Ratchev" wr...

Rewriting a formula...
I have a spreadsheet that calculates totals and has formulas in certain columns. Is is possible to have the formulas work, but not have any numbers in the cells before I input them? For example, in column D, I have the simple formula =D9+E9, and so on and so forth down to =D25+E25. I may only put itmes in until D11. So in D12 thru D25, I have $0.00. Can I rewrite this formula, possible an IF/THEN statement to add the two columns, but not show the $0.00 in D12 thru D25, since I did not have to do any addition? My main goal is to not have this info in D12 thru D25 print to make the sheet ...

visible cell only
I'd like to use the PERCENTILE function in a list that has been autofiltered and get the results based only on the visible cells. I've used SUBTOTAL in order to get count, average, min and max. But I need to get the .25 and .75 percentile figures for the filtered data (visible cells only). I've scoured these forums. I've scoured the web. I've found some vba code that was supposed to select only visible cells but it doesn't work for me. I posted last week in the programming section of these forums (and again this morning) but got no reply. I figure...

Using part of a cell in a chart title
I have a chart which should get a title. However, this should be partly be used from a cell e.g. "counted with 5%" 5% should be taken from the cell and used in the title. Is this possible? Hi, Yes it's possible but all of the chart title needs to be in the cell. So you may need to use a helper cell and concatenate text and value. http://www.andypope.info/tips/tip001.htm Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Nicole" <Nicole@discussions.microsoft.com> wrote in message news:5CB7A971-AA7F-4C34-BB42-7DC283AA2958@micro...

Parsing data from one spreadsheet into another format
The data that we dump out of one machine comes in like below. %AT_1300 Bottoms|Conductivity| (Water Out) InputRange VDC1to5 %AT_1300 Bottoms|Conductivity| (Water Out) Custom_Range_Low 0.0 %AT_1300 Bottoms|Conductivity| (Water Out) Custom_Range_Hi 0.0 %AT_1300 Bottoms|Conductivity| (Water Out) MinScale 0.0 %AT_1300 Bottoms|Conductivity| (Water Out) MaxScale 20.0 %AT_1300 Bottoms|Conductivity| (Water Out) EngUnits mhos %AT_1300 Bottoms|Conductivity| (Water Out) StepResponseTime 1.0 %AT_1300 Bottoms|Conductivity| (Water Out) DigFiltTimeCnst 0.016 And I need to convert this data to this f...

Using expression builder object
Hi, I'm developing a wizzard in Access which builds import templates for various data sources to a fixed set of tables. In step 3 the users must be able to build an expression ; for instance Left([Fieldx],20) . Now I would like to have a command button on my form which calls the Access expression builder to allow the users to use this to build the expression. This expression will then be stored in a text box linked to the templates table. Anybody know how to call and use this object from VBA code? -- Kind regards Noëlla DoCmd.RunCommand acCmdInvokeBuilder I th...

what's the formula for adding symbols in cells?
I have a chart that has blank info in the legend. I want to add an * to indicate something, but just inserting a symbol doesn't work. Any ideas? Thanks. Debi - To add information to the legend, you need to add to a series name. Right click on the chart, select Source Data from the pop up menu, click on the series tab, select a series, and either type something in the name box, or click in it and select a cell with the mouse. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ Debi wrote: > I have a chart tha...

formula based on format
Is there a way to have a formula that is based on a cell colour or based on the way a cell is formated? I need it to count the number of occurrences that this happens. -- Thank you for your time. Windows NT Office 97 Hi you'll need VBA. See for an example: http://cpearson.com/excel/colors.htm "James Kendall" wrote: > Is there a way to have a formula that is based on a cell colour or based on > the way a cell is formated? I need it to count the number of occurrences > that this happens. > -- > Thank you for your time. > Windows NT > Office 97 This ...

How do I use traffic lights in excel
I am wanting to use traffic lights in excel that change colour based on the result of a variance cell, ie if the result of the cell is 10 make the traffic light green, if it is 20 make the traffic light amber, if the result is 30 make the traffic light red. How do I do this? Shorty Format>Conditional Formatting>Cell Value is: Note: you can add up to 3 conditions(4 if you count default) Gord Dibben Excel MVP On Wed, 22 Dec 2004 16:35:03 -0800, Shorty <Shorty@discussions.microsoft.com> wrote: >I am wanting to use traffic lights in excel that change colour based on the &g...

How can I change 'Normal' Style for Word e-mails to 'Normal (Web)'?
Hi, I'm using Word as my e-mail editor in Outlook 2003 and want to change the default Style for e-mails from 'Normal' to 'Normal (Web)'. The problem is that new e-mails and replies in HTML format use the 'Normal' Microsoft Word 'Style', and this has no gap after paragraphs. The upshot of this is that when sending an e-mail, I have to press return twice to create a gap, but when the recipient views this, their software shows it as four gaps (the extra carriage return I typed + their correctly viewed HTML carriage return after each line). E.g. I type this: ...

make subreport2 visible based on condition
Help, I am running Access 2003 and have trouble getting this work right. I have an unbound subreport that contains 3 additional subreports. I want only one of the 3 to be visible based on the value in a combo box on a selection form. I can't seem to get the reference right and have tried several variations. If Forms!frmWeeklyGLrpt!cboCC = 1 Then Me!subrptGLWeekly.Report!subrptGLWeeklyCMRDA.Visible = True Me!subrptGLWeekly.Report!subrptGLWeeklyCMRDB.Visible = False Me!subrptGLWeekly.Report!subrptGLWeeklyCMRDC.Visible = False End If If Forms!frmWeeklyGL...

Phone number normalizing and auto-formatting
I really appreciate the way that Outlook auto-normalizes phone numbers - for example, here in the US, I can enter 5555555555 and Outlook automagically formats that to (555) 555-5555. Why CRM 3.0 doesn't do this out of the box is strange but if anyone has ideas on how to implement, that'd be greatly appreciated. I've worked very hard to normalize all my data before import, but I know it's a matter of time before our users (ok, our sales people) destroy all that hard work by entering garbage into every phone field entry that the system cannot auto-normalize and error ch...

Filter recordset using query results
Hi all I have a form based on a query called [qry Quarterly Planning], it lists all Itineraries on the system. On this form you can filter records by specifying a Start and End Date for the [ReviewDate] and/or [Specialist]. It is a subform on a main unbound form, lets call this Subform1. Along side this I have another subform (Subform2) which displays ReviewDates that exist against an Itinerary. In other words Subform1 has a start date of an activity and if the activity lasts longer than 1 day, then the additional dates are stored in Subform2 (ItineraryDates). Currently when I...

How to change font size on formula bar in Excell 2007
I don't find Tools>Options>General (as suggested in other post answers) in Excel 2007. On the Office button there is an Excel Options but it doesn't provide a method of changing font size on the office but. My font is so small I can barely see it. Office button>ExcelOptions>Popular tab>in the "When creating new workbooks" section, choose font and font size -- Kind regards, Niek Otten Microsoft MVP - Excel "jimwillie" <jimwillie@discussions.microsoft.com> wrote in message news:588AAC05-0F52-404E-AA01-128E70E02D0B@microso...

can't customize toolbar
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel all i see is document elements, quick tables, charts and smartart graphics. i do not see the main menu or the toolbar button. when i attempt to customize the toolbar, the to menu bar and format bar do not appear where they should. on a website i visited, they advised that i drag and drop the temporary toolbar into the real toolbar. but i can't drag and drop the toolbar. i can only move the temporary toolbar. how do i add the menu bar and format bar for go? You may have hidden the toolbar by accident. Click on the ti...