Numeric Format Based On Cell Value
Nov 17, 2009
I am stuck on this one. In the attachment I have values pulling into N3:N7 based on a drop down choice. Some of the numbers are numeric (no decimals), others are currency (no decimals), others are percentages (2 decimals), etc.
Is there a way to pull values I'm currently pulling into column N and have those values match the format of the source? I've tried converting it into TEXT but then I cannot place conditional formatting data bars on the values and have it work.
View 4 Replies
ADVERTISEMENT
Feb 16, 2010
I thought I had this solved but an inconsistency has shown up. I have a long list of chemical formulas that I want to format (partially) as subscript.
Basically what I need the macro to do is look at each character within a cell and check to see if it is numeric. If it is AND it follows a non-numeric character it should be formatted as subscript.
Examples
H2O the 2 should be subscript
H2SO4 the 2 and the 4 should be subscript
2CCl4 only the 4 should be subscript
View 2 Replies
View Related
Feb 16, 2010
I have a long list of chemical formulas that I want to format (partially) as subscript.
Basically what I need the macro to do is look at each character within a cell and check to see if it is numeric. If it is AND it follows a non-numeric character it should be formatted as subscript.
Examples
H2O the 2 should be subscript
H2SO4 the 2 and the 4 should be subscript
2CCl4 only the 4 should be subscript
CuSO4 - 5H2O
View 2 Replies
View Related
Nov 28, 2013
I have several numeric values in one cell enetered using the alt return method, can these values be recognised as individual entries and therefore when the cell is formatted as $ it is applied on all entries?
View 2 Replies
View Related
Aug 24, 2007
I have uploaded a copy of the spreadsheet I am working on and have completed the user forms and coding for march the 1st only, so if you test, please use the options march and then the 1st!
The problems i have is, when I enter a number into a text box and press next page, the numbers fill where i want them to but the cells do not recognise them as numbers, therefore conditional formatting doesnt work! Even if I change the cell properties to numbers, this does not remove the error!
Second problem!! If a user forgetts to enter a value in a box, or they wish to edit just one value, and go back into the user form to change a figure, when they press next page, all the values in the column seem to disappear!
View 6 Replies
View Related
Dec 11, 2009
I have an excel 2008 sheet and want to add cells in an odd way. Here is the best way I can describe it.
I want a formula to add cells based off a number value in another cell.
So it would be something like
IF M3= 1 add C5 to SUM ; If not then go to next step
IF M3= 2 add F5 to SUM ; If not then go to next step
IF M3= 3 add I5 to SUM ; If not then go to next step
IF N3= 1 add C5 to SUM ; If not then go to next step
IF N3= 2 add F5 to SUM ; If not then go to next step
IF N3= 3 add I5 to SUM ; If not then go to next step....................
View 6 Replies
View Related
Apr 19, 2009
I have a spreadsheet that has (amongst other things) 2 charts based on dynamic data with ranges that redraw using vb... The charts will draw envelopes that correspond to an aircraft's moment or c of g envelope - there will typically be 2 envelopes drawn, "normal" and "utility" or "aerobatic".
Plotted on the charts are an aircraft's takeoff and landing weights and moment/C of G.
I'd like to be able to use a formula or VB to determine which envelope takeoff/landing information falls in (or if it falls outside all envelopes). This data would then be used to generate an error message/possibly also in conditional formatting, etc.
View 4 Replies
View Related
Dec 3, 2009
cell A1 is equals to B1 because the cell C1 contains the number 1.
Now if i change the value of the C1 to 2 how can i make the A1 shows the value of the B2 ?
Just like. [A1]=B(C1-content)
View 3 Replies
View Related
Jul 10, 2014
How do you shade a cell based on multiple ranges of data? e.g. A value of...
1 shades red
2-3 shades orange
4-5 shades yellow
6 shades green
View 4 Replies
View Related
Oct 22, 2009
I am trying to do is extract the volume size of products in 'ml'
from 10k plus products from a description field cell.
this description field could also contain the weight of the product in grams
so I cannot just do a search for a numeric string ,
it has to be associated with the milli-litres statement .
is is possible to do a sort of ' *ml ' search and then select and copy to another cell ???
View 9 Replies
View Related
Jun 10, 2006
how I can change the color of an autoshape based on a value in a cell?
View 4 Replies
View Related
Jun 12, 2008
I would like to have a single button that changes a range of cells from the USD to EURO to perhaps CAD symbol. Can this operation be performed, such that if I start in dollars, and I click the button once, it shifts by range to EURO (not formulas...just symbol)...and then if I click the same button again, it goes to CAD, and then back to USD with a third click?
View 5 Replies
View Related
May 14, 2009
I get data in csv format from an external source.
The problem is that it gives me dates in a lousy format of ddmmyy
For example 120706 or 050606.
Excel doesn't recognise them as dates and just displays them as numbers (120706 or 50606)
I want to see them as dates, so I select the column and change the format to date
BUT
excel sees my "numbers" as a julian date, and jumps me forward to the dates 24/06/2230 and 20/07/2038 respectively.
View 14 Replies
View Related
Oct 9, 2011
I need to convert data in HH:MM:SS format to normal numbers
for example if I have a time ( 10 hours 30 mins 30 sec )
10:30:30 then it should reflect as 10.3
I basically am not looking to convert the data to complicated hours format like say 10.9 and all as it complicates the other calculation.
View 9 Replies
View Related
Dec 17, 2012
How to use data validation to restrict the input format into a cell. I'm trying to limit a cell to the following format: either ###/###' or ##/##'. NOTE: the apostrophe at the end. Here is what I've been trying to get the thing to work for the 2nd case:
=AND(ISNUMBER(LEFT(H1,2)+0),MID(H1,3,1)="/",ISNUMBER(RIGHT(H1,2)+0),MID(H1,3,1)="'")
But I'm getting an error when I input a test into the cell, e.g. 12/45'.
View 9 Replies
View Related
Feb 12, 2012
I want to record the amount of people attending a venue over a given period. The information presents names and did they attend. In column "D' from D5 to D45 the information will be yes or no. I want a formula that calculates the amount of YES and shows the answer in a numeric format
View 4 Replies
View Related
Aug 21, 2013
I have a column of dates formatted as:
20130201
The cell format in the column is General.
I need to change it so that the format looks like this:
02/01/2013
Is there a quick/easy way to do this in Excel 2010?
View 2 Replies
View Related
Feb 18, 2009
Been racking brain, searching through the forum here, and my Excel 2003 Bible all day trying to figure out this problem to no avail. I would like to clear the contents of any cell in a given range if the cell immediately to the right of is formatted as bold.
View 2 Replies
View Related
Nov 24, 2013
let's say cell A1 is colored blue manually, how can i format cell b1 to be colored red? is it possible in conditional formatting?
View 7 Replies
View Related
Mar 24, 2007
Macro Based on an input cell’s (e.g. B12) setting of “yes” or “no”, format several other cells (e.g. B20:G20, B23:F23, C26:J26) as follows: keep the existing yellow shading if input cell is “yes”, change to grey shading if input cell is “no”. I would like this macro to run automatically whenever the input cell value is changed.
View 2 Replies
View Related
Sep 21, 2012
Y3:Y500 contains a range of years, 2014/2015 through to 2018/2019, for a total of 5 different values (So conditional formatting is out the window). I'd like code which highlights the rest of the row, Z:FN, in a certain colour depending on the value in Y3:Y500.
Also, where there is data in the cells between Z:FN I'd like that to highlight in yet another contrasting colour.
View 9 Replies
View Related
Nov 11, 2012
Can I conditions format a cell based on data from another cell?
View 8 Replies
View Related
Sep 12, 2006
way to conditionaly colour code all cells in a grid based on cells in other grid: i.e.
gridA
1 1 2
1 2 1
2 2 1
grid B
12 11 14
34 16 53
35 64 23
i want all cell in grid B to be red if the coresponding cell of GridA is 2.
so here "14" "16" "35" and "64" will be red!
View 9 Replies
View Related
Nov 6, 2013
If F2 is blank and E2 date is older than or equal to today, bold and color E2 RED.
View 2 Replies
View Related
Aug 9, 2007
I don't even know if this is possible but can you use some formula within Excel to return a value to a cell based on the cell formatting of another cell? Here is specifically what I am trying to do:
Cell A2 has a value of 10. If the font color of that cell is black (or "automatic") I want a formula in cell A1 that will return a value of "+10". If the font color of cell A2 is red, I want the formula in cell A1 to return a value of "-10".
View 13 Replies
View Related
Jan 17, 2014
With the click of a toolbar button, I've got my vba inserting a new tab, renaming it, inserting headers and now working on the code to insert all the formulas needed in 12 columns to copy down as far as there is data.
The 4th column D is the hardest. Currently, I'm using manual "Conditional Formatting" to colorize the cells in Col D but need the VBA to place that "Conditional Formatting" code into the sheet so that when the sheet is created, it will hold the conditional formatting feature.
Specifically, the formulas I'm using in the Conditional format wizard are:
Formula: =$B2$D2 then turn the cell red Applies to: =$D$2:D
Copy down (in other words, the colorizing should work all the way down Column D as long as there is data in Col D.
View 2 Replies
View Related
Jan 24, 2014
I want to format the cells in column B under the context of the cells in the corresponding row of column A. For example I want to color the cells in column B that the context of the corresponding row of column A is 1:
1
put
2
hot
4
dog
1
cat
1
yes
View 2 Replies
View Related
Jul 23, 2014
How to write a VBA that will, if the value of a cell in column "U" is TRUE, that row's FONT (for the row the TRUE cell is in) will turn red.
View 4 Replies
View Related
Nov 21, 2006
Is there a macro that can copy the information of any italicize item in Sheet1 to Sheet2 and automatically italicize the item in Sheet3 if the same item exist?
In my attached example:
In Sheet1, B3 to B8 contain information about a restaurant.
I want the macro to copy any restaurant (and its information) that is italicize to Sheet2. And in Sheet3, any restaurant that is in italic in Sheet2 will become italicize in Sheet3 if the name is match. Is there such a macro that can do these things?
View 5 Replies
View Related
Feb 19, 2007
I'm afraid as its a multiple conditional format and thus needs coding. At the moment I'm using the following
Private Sub Worksheet_Change(ByVal Target As Range)
' Conditional Formatting for more than 3 conditions
Dim rng As Range
' Target is a range::therefore,it can be more than one cell
' For example,,someone could delete the contents of a range,
' or someone could enter an array..
Set rng = Intersect(Target, Range("E27:E34,G27:G34,I27:I34,K27:K34,M27:M34,O27:O34,Q27:Q34,S27:S34,U27:U34,W27:W34,Y27:Y34,AA27:AA34"))
If rng Is Nothing Then
Exit Sub
Else.....................
This works very nicely, so that for example if I type the number 10 into cell M33, that cells fill colour becomes black (and the text turns white). Alas I would now also like to extend the fill and text colour to the cell one to the left too. To take this example I would type 10 into cell M33 and cells L33 and M33 would both be filled black and their text would be white. I'm not sure if this requires a change to the code, or if its possible to just have a formula or something which copies the fomatting of one cell into another
View 2 Replies
View Related
