I am currently using this format to display numbers within a pivot table (this put's in a comma and removes any decimal place):
_(* #,##0_);[Red]_(* (#,##0);_(* "-"??_);_(@_) Scenario1-If the cell evaluated is 0>1 the cell displays: 0 (a black 0) Scenario2-If the cell evaluated is 01 are insignificant and I would like them to remain as a black - so as not to draw attention to them while evaluating data within the PT.
Does anyone know of a way to display a black - in all three scenarios above short of editing the original data?
I was hoping there was a way to do this via the custom format option or within the pivot table as the data that produces the PT should remain untouched.
I have a column in excel which gets populated in the format "2/1/1980" when copied from a different source. I want a macro to run on this column and convert this in the below format. Required format: '02-01-80 (note the ' symbol before 02 which will keep it as text)
I need this macro to run on two columns and replace them with the required format. I can do a find and replace either in the sheet or through hard coding the VBA, but as the date format keeps growing, I would need to do this via code and hence requesting the same.
Can this be done automatically in a worksheet using the custom cell format function??.... because then it wud get formatted as necessary whenever someone enters a vehicle no. in that particular column.
I want to create a custom format for a cell for entering in "cup measurements". I want to be able to type in 5, 1/4, 1/16, etc. and have the word "cups" appear after the number in that cell. I formatted the cell to say the word cups after typing in the numbers in my cell using the code "cups".
ex: 0 "cups" works fine for whole number
so then if i change it to 0/0 "cups" is works great for only 1/8 but not 1/16 or whole numbers. Then I looked at other custom codes that already existed in my formatts so I attempted to try this code which I just kinda came up with
well it isn't doing what I want and If I use the 0/00 "cups code and I type in 4 cups the cell then says 4/01 cups.
What does my code need to be for me to show whole numbers and and size fraction without having it look weird?
Data in a cell is formatted in h:mm which is truly a result of a calculation of # of hours & minutes detained at a location. D
Data is result of microstrategy query so result is
Cell is formatted as custom h:mm, but there is actually a fictitious date of 1/1/1900 defaulting in front of h:mm when double clicking into cell or viewing in fx field above. How do I get rid of that date which is inhibiting me from converting 17:08 to minutes by using the formula of =TEXT(L3,"[m]")
In cell 'I6' if I enter the text "Balance" I would like it to display as "Actual" but if any other text is entered I would like it to display the text entered. How to do this using custom cell formatting. (Currently on Excel 2010).
I am trying to write a custom cell format that would allow me to replace the comma with a space after the thousand's place. Therefore, 1 million would look like: 1 000 000 instead of 1,000,000. I do NOT want to change my regional settings to accomplish this.
When I write ### ### ### it accomplishes this, however, there is one small glitch. When you apply an "underline" you get the following result:
I have a sheet which uses a 'Custom Format' in column A, '00000' to give each row a unique reference. Is there any way to do a 'Find' using this format, e.g. 00012, rather than just 12. Searching using '00012' doesn't find the appropriate cell, whereas using '12' does?
I can't format them as text because I'm getting them as an isbn with dashes (ie:978-05689-256-7) and when I remove the dashes (even if its formatted as text) excel changes the number to 9780568.9E+12 (or something like that)
I think the solution would be to create a custom format (ie. 0000000000) to preserve the zeros, but I'm having 2 problems:
1. Since the ISBN#'s have 2 different formats (ISBN-10 & ISBN-13) I need to somehow check if the first 3 numbers are 978 then based on that it should be formatted either as ISBN-10 (0000000000) or ISBN-13 (0000000000000)
2. In the post that I linked to above it said that I won't be able to do a vlookup if its custom formatted. Is there any way around this?
Anyone know if a wildcard can be used in Custom Formatting using text leading off. Example:
I have the above custom formatted as: "U"00-00-0000 which gives me U01-23-4567. This give me the format I need however; the U is not always constant and needs to be changed to different letters as required.
I have a custom format that is applied to column AU. Here is the worksheet code which is placed in the worksheet code area:
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count > 1 Then Exit Sub If Target.Column <> 47 Then Exit Sub If Target.Value = "" Then Exit Sub On Error Goto Errhndl Application.EnableEvents = False Target.Value = "Map " & UCase(Left(Target.Value, 4)) & " <" & UCase(Mid(Target.Value, 5, 2)) & "-" & Right(Target.Value, 2) & ">" Application.EnableEvents = True Exit Sub
Errhndl: Application.EnableEvents = True
It works well on the sheet, if I remember to only put three numbers, three letters and then two more numbers (allwithout any spaces. Lowewr case ok). (eg. 122rmm25 is then transformed into Map 122R <MM-25>). On the sheet this works. Coming off of the UserForm, it tries to apply this format to text already formated and messes up royally!!!
Is there any way to either reverse this proccess coming off the UserForm or to stop the worksheet from appling this format to the value of the cell in question if the source is in the wrong format to be changed? How do I detect what format the text is in? (Raw, formatted correctly or over formatted)
I'm looking at what appears to be a custom chart. It is a box divided into 4 equal quadrants. In each quadrant is a percentage with the total equaling 100%. In each of the quadrants there is fill equal to the percent that is numerically written in that quadrant. I'll post a picture with this, but if I was talking cells then....
A1 = 15% B1 = 66% A2 = 4% B2 = 15%
The backround image behind the number is filled up by volume equal to the percentage indicated. They are also color coded with red representing the highest percentage quadrant filled.
To top that, there are little arrows along the sides of each quadrant indicating an average score.
My first question is WHAT TYPE OF CHART IS THIS THING!, and second can this be done in excel?
I am trying to create a number format for formatting resistor value in their most commonly displayed form. For example 1,200 ohms would be displayed as 1.2kÙ and 5,000,000 ohms would be displayed as 5MÙ, and finally 5 ohms would be displayed as 5Ù. I have been able to create a format that formats any 2 contiguous formats but not all three.
for example: [>999999]###.0,,ÌÙ;[<1000000]###.0,kÙ; or: [>999]###.0,kÙ;[<1000]###Ù
both work.(other similar criteria work as well) But if I try to put 3 conditions describing the desired formatting I get and error (containing no useful info) as I try to close the formatting dialog. Is there a limit to the number of conditions that can be used. Or something else I'm overlooking.