Cell Format #4

Is there a way to have a cell format based on contents of an i
statement...

Example

if(C1="Input",and(C3,Format $#.##),if(C1="% of Revenue",and(C5,Forma
#.##%),na)

I want the If statement to test a condition, return contents of th
correct cell and format automatically.

Any help is appreciated

--
bforster
-----------------------------------------------------------------------
bforster1's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1177
View this thread: http://www.excelforum.com/showthread.php?threadid=26133

0
9/18/2004 11:29:33 PM
excel 39879 articles. 2 followers. Follow

1 Replies
658 Views

Similar Articles

[PageSpeed] 9

You can't change the format of the cell with a formula, but you could use a
formula like:

=IF(C1="input",TEXT(C3,"$#.##"),IF(C1="% of revenue",TEXT(C3,"#.##%"),C3))

You can also check some formats via:
=cell("format",C3)

See excel's help for the details.

bforster1 wrote:
> 
> Is there a way to have a cell format based on contents of an if
> statement...
> 
> Example
> 
> if(C1="Input",and(C3,Format $#.##),if(C1="% of Revenue",and(C5,Format
> #.##%),na)
> 
> I want the If statement to test a condition, return contents of the
> correct cell and format automatically.
> 
> Any help is appreciated.
> 
> --
> bforster1
> ------------------------------------------------------------------------
> bforster1's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=11771
> View this thread: http://www.excelforum.com/showthread.php?threadid=261339

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
9/19/2004 12:03:43 AM
Reply:

Similar Artilces:

macros in excel #4
Is it possible to set up a macro in excel to print report with conditions "Jai" wrote: > Is it possible to set up a macro in excel to print report with conditions Can you elaborate on "Conditions" and what kind of report it is? May be I can pitch in .. I am a beginner too, but I can look around. I am trying to set up a report from database of vendors. Each vendor has it's own number. Some vendors are grouped together. In report, there should be list of each vendors and all grouped vedors should have subtotal for its data. I know it's easy to do a subt...

CRM 3.0/4.0: Views to show record that is created X Days Ago
I have a simple requirement to create a View with condition that the record is created x days ago, for example the record that is created 3 days ago. The only operator that is available for datetime (e.g. createdon) is only Last X Days, which if i show Last 3 Days, will show the records that were created today, yesterday, and 2 days ago. Is there any operator or any way to show the record that were created 3 days ago? I try to put condition "createdon Last 3 Days" and "NOT createdon Last 2 days", but there is no "NOT" operator in CRM. I try to insert new attribut...

Preserving Cell Formats in Excel Query
I am doing queries on a large workbook of multiple Excel spreadsheets. When I query the data, the original data formats don't carry through to the query results. Is there a way to carry original formatting through to Excel Query results Any insight would be appreciated Karen S No, you can import the data, but not the formats. If you're importing programmatically, you could apply the formatting as part of the import procedure. Karen S wrote: > I am doing queries on a large workbook of multiple Excel spreadsheets. When I query the data, the original data formats don't car...

saving format
i have a spreadsheet that i backup & copy to a new folder but the cell formulas are replaced with the cell values. how do i keep the formula format. thanks Save two copies??? One with the formulas and one after the formulas have been converted to values. bob wrote: > > i have a spreadsheet that i backup & copy to a new folder but the cell > formulas are replaced with the cell values. how do i keep the formula > format. > > thanks -- Dave Peterson ec35720@msn.com Or are you saying that when you make the backup copy the formulas are somehow changed to values...

background image formatting
Hi All, How can I make a background image stretched instead of tiled? I am using a default picture in publisher of clouds and they of course don't look right being tiled. Thanks a lot David Resize the image? --- If you're asking about web bg's, you can but the result is u g l y -- Rob Giordano Microsoft MVP - FrontPage "David" <David@discussions.microsoft.com> wrote in message news:9E6E0F22-9511-4EEC-963B-0B359D511FA2@microsoft.com... | Hi All, | How can I make a background image stretched instead of tiled? | I am using a default picture in publisher ...

Format cells with dates
Is there a way to format cells so that dates would change when the lead date is changed. for example, when I input monday's date, tue, wed, thur, etc will follow suit. Pat, Assuming the first date is in A1 B1: =A1+1 C1: =B 1+1 etc. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Pat" <anonymous@discussions.microsoft.com> wrote in message news:115681B0-348E-447E-BB1F-F8347CFDB19B@microsoft.com... > Is there a way to format cells so that dates would change when the lead date is...

Conditional Formating Help
Hi I wonder if anyone could help me, I am after code for the following. cell e6 = Keys Sent Column f6 = Keys due Column g6 = Keys received What I want the script to do is if there is no value in g6 and todays date is greater then the date stated in F6, then the cell turns red (prompt to inform me that keys are late). Many thanks Dan Use a CF formula of =AND(G6="",TODAY()>F6) HTH Bob "housinglad" <housinglad@discussions.microsoft.com> wrote in message news:A5887369-33DA-489A-BEC9-8173707313C6@microsoft.com... > Hi > ...

Conditional Formating Furmula
I have to enter a 14 digit licence number in a field this is a mix of numbers and leters. can some one please give me a formula that i can use in conditional formating tol show if there are to few or to many digits. (Using excel 2003) Thaks in advance. CF/ Formula is/ =LEN(A1)<14 Format as desired for too few CF/ Formula is/ =LEN(A1)>14 Format as desired for too many Rather than using CF you could, of course, use Data validation to require LEN(A1) to be 14. -- David Biddulph "jockj215" <jockj215@discussions.microsoft.com> wrote in message news:A24...

Conditional Formatting
Hi All, I need help on conditional Formatting. I have a column of data with future DATE, such as 2 Jan 09, 4 Des 09, etc I would like to assign automatically different colors to those data that match these condition: If (TODAY's date < Data's date) And more than 30 days, COLOUR is Orange If (TODAY's date < Data's date) And more than 60 days, COLOUR is Yellow If (TODAY's date < Data's date) And more than 90 days, COLOUR is Green If (TODAY's date >= Data's date) And more than 30 days, COLOUR is Red I would like to gave it AUTO...

How to goto cell containing specific date
Thought I asked this before, but can't find the thread w/ my question or any replies... I have a worksheet wih a full year's dates in the cells running down a colum, with other data for each date in the the adjacent columns; Instead of scrolling up & down to a cell with a specific date I'm looking for, is there another way to goto a cell containing a specific date? (e.g., today(), or another specific date) In case this is pertinent: the date series begins with the entry of one date (e.g., 01/01/2010 in cell A1), with the dates in subsequent rows arrived at ...

Auto Recovery #4
Hello everybody I have in excel option the autorecovery function on and is saving in the following path : C:\Documents and Settings\mnikolaou\Application Data\Microsoft\Excel The problem is that i was working a file and make some chanes while my computer crashes. When i made a restart it was imposible to find out the file i was working. not even in a recovery format. Is that option works properly or i have to make some other changes in the options in order to have backup files while i am working? Thanks in advance Manos Auto Recovery is supposed to work automatically so that you do n...

How do I turn the pivot chart into a list with all cells filled?
I have the pivot chart and would like to copy and paste it so that all fields are filled Select the pivot table. Choose Edit>Copy Select the cell where you'd like to paste the copy Choose Edit>Paste Special Select Values, click OK There are instructions here for filling the blanks: http://www.contextures.com/xlDataEntry02.html gianna wrote: > I have the pivot chart and would like to copy and paste it so that all fields > are filled -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...

Report Manager #4
Hi I have created a cusomised toolbar but when I customise the toolbar I cannot find "Report Manager" anywhere to add to it. The odd thing is that it is on the main menu but does not appear in the customisable dialogue box. Does anyone know where to find it, so I can put it on my toolbar. Cheers -- All help much appreciated. Thanks I'm guessing you created this customized toolbar manually (not by code). If that's the case and you have "report manager" under the existing tools menu already (or is it under Data???). Tools|Customize (just to see that dialog ...

How do you copy a cell's content verses it's formula?
I have 2 cells and combined them into a third cell with the following formula... =a1&" "&b1. I was combining a person's first name (cell 1) with a person's second name (cell 2) so cell 3 included the first and second name. Now I want to copy and paste cell 3, but it copies the formula... I need to paste in the content (first and second name) not the formula. Hi Tammy, You could use a macro see JOIN macro on it's page http://www.mvps.org/dmcritchie/excel/join.htm not what you actually asked because if would change column A with the concatenated const...

Conditional Cell Fill?
Hello, Is there a way to use fill colors based on formulas? Thanks, Rusty Look at conditional formatting in help -- Regards, Peo Sjoblom "Rusty Williamson" <rusty@uno.sd.znet.com> wrote in message news:RUGlb.45764$Z86.33887@twister.socal.rr.com... > Hello, > > Is there a way to use fill colors based on formulas? > > Thanks, > Rusty > > ...

weird formatting
Hi all, Anyone ever seen this before? This was what happened to a mac excel spreadsheet the user doesnt know how she did this but i wondered if anyone might recognize this current format. In hopes we can change it back. Formatting is below. thx for any help ... Ed 2oC//9yIAGAf0L//2tC//9qQAAg= </data> <data> AG0AFgAAPz8/Pz8/Pz8BcF9AAAAA JQUubXBlZwAADHZpZGVvL3gtbXBl Zw9NUEVHIG1lZGlhIGZpbGVtc2Z0 AAAAMr//AAIRUXVpY2tUaW1lIFBs dWdpbgDagL//3IgAYB/Qv//a0L// 2pAACA== </data> <data> AGwAFgAAPz8/Pz8/Pz8B...

Formatting hyperlinks in an Excel cell 02-16-10
Two of the columns in a spreadsheet (Excel 2003) that I use record email and web addresses. All of them appear as hyperlinks i.e. blue and underlined but some occasionally seem to lose their hyperlink properties. This means that when one hovers over them, the cursor stays as the usual Excel cross rather than changing to the hand/finger symbol. Also, clicking on the former does not launch the browser. Is there any way to ensure they are formatted, and work, as hyperlinks please? TIA V ...

Formatting date fields after export
I am experiencing problems with my exported date fields into Excel from other applications. The data formats to "yyyy-mm-dd" and cannot be modified unless I double-click on each field. Has anyone else experienced this problem? And what solutions would you suggest? It is probably seen as text, select the imported dates, do data>text to columns, click next twice, under column data format select date and YMD click finish Regards, Peo Sjoblom "Raymond" wrote: > I am experiencing problems with my exported date fields into Excel from other > applications. The d...

calculation of cells
Periodically I open a work book and the calculation option has been changed to manual and I cannot figure out why. It seems that it would have to be done by a user and most of my spreadsheets are only used by me. Any ideas out there Mark, Calculation, auto or manual, is set by the first workbook that's opened. It is that way for any other workbooks opened in that instance of excel. Look for a workbook you might have opened first that's been set to Manual and saved that way. Go figure. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------...

returning vlookup values for blank cells
I have a spreadsheet that lists "soccer players" by name down the first colunm and "time in game" across the top and the position they play in array. I then use vlookup for another spreedsheet by "position" down the first column, time across the top and puts the players name into the positions. All this works fine. Since there are 5 more kids than positions, the orginal spreedsheet has blanks when the kids are out of the game. How do I use vlookup or other to extract the 5 sub'd out kids at the bottom of the 2nd spreadsheet? It only returns the nam...

Skip blank cells in diagrams
How do I exclude blank cells in diagrams. If I have an area of data and among these data some is blank. How do I get excel to not display these data as '0' but just to skip the cell. You can include the function NA() in that field and the zero value for the data won't be displayed. "hlp" <hlp@discussions.microsoft.com> wrote in message news:4FF83D9F-F13E-4815-BDDE-26F44F2E6BE1@microsoft.com... > How do I exclude blank cells in diagrams. If I have an area of data and among > these data some is blank. How do I get excel to not display these data as '0...

Automating transfer of data in cells
I have a time management spreadsheet with data stored against work type and date. I need to transfer this data into a similar but more comprehensive spreadsheet and wonder whether it is possible to automate this task by using the work types and dates in a macro (I have almost 10 months of data to transfer), along the lines of check date, check worktype, where argument is true enter data from cell. I think I need to use visual basic, but I can't find out how in the help screens. Any advice is much appreciated. This is not difficult providing you keep your data in simple tables...

CSV formatted text file to Excel
Hi all, I am writing a small VC++ application of how to import the CSV formatted ..txt file to excel. I am facing problem while parsing the text file. "TicketNo","CarNo","PersonAge" 12534 , 763534 , 23 12345 , 624333, 24 The problem is in MFC there is a SetValue2(CoeVariant:column data) method in which if i will pass an array(12534) then it will be imported to excel.For example IfI will search for the "employee number" field in text file then the values passed to SetValue2()should be 12543 and 12345.But Using C++ I cannot do so as I...

Place X in cell if criteria met`
Is there a formula to do this? If cell B2 = pencils Put an "X" in cell B7 If cell B2 = pens Put an "X" in cell B8 If cell B2 = erasers Put an "X" in cell B9 Thanks in advance in cells B7 put =if(B2="pencils","x","") in Cell B8 put =if(B2="pens","x","") In cell B9 put =if(B2="erasers","x","") "jhicsupt" wrote: > Is there a formula to do this? > > If cell B2 = pencils > Put an "X" in cell B7 > > If cell B2 = pens ...

can I find merged cells?
I'm trying to sort and get the message "merged cells must be the same size". How can I 'find' the merged cells? David, here is a macro by Dave Peterson that will do it Sub Found_Merged_Cells() 'macro looks for merged cells 'By Dave Peterson Dim myCell As Range Dim resp As Long For Each myCell In ActiveSheet.UsedRange.Cells If myCell.MergeCells Then If myCell.Address = myCell.MergeArea(1).Address Then resp = MsgBox(prompt:="found: " _ & myCell.MergeArea.Addre...