I have an if statement which shows either GP% or average revenue per product. The problem is that GP% needs to be in a percentage format and revenue per product needs to be in number format. 500 is now shown as 50000% or 50% will be shown as 0.5 for example. Is there anyway on changing the format of a cell based on the number?
View 1 RepliesI want to be able to do the following: I have data in column a and I want the text to change to strikethrough in, for instance, cell a1 when data is entered in b1.
I am trying to format certain cells to change colors depending on the date and the content of the next field to show due dates.
cell label
due date
date completed
c5
au5
be5
c6
au6
be6
c7
au7
be7
c8
au8
be8
What I need is for the contents of au5 to be green up until 10 days before the due date, yellow up until 5 days, and red with less than 5 days until the due date. If the date completed is entered into be5, then the contents of au5 should be black.
Is this possible, and if it is, how do I set this up? I tried to use
=$AU5-TODAY()>=10
And choose green formatting (something I found on yahoo answers), but when I enter the date the text is still the original black font.
I am having slight trouble with the formatting of some cells. Every month I will be copying data into a row, the problem is Excel thinks it's a date instead of an integer of some sort.
The value copied will look something like this:
79:42:00
(meaning 79 hours and 42 minutes)
Excel is taking that and interpreting it as:
1/3/1900 7:42:00 AM
Is there an easy way to format this so Excel thinks it's a number (even if 79.7).
I tried to change the format of the cell to "Text", but it changes it to: 3.32083333333333
I need change the color of the cell depending on the following conditions. (By the way the column will have names list)
1. single punctuations are allowed anywhere in name apart from first position.-->color should not change
2. If any name got consecutive punctuations --> color should change to RED.
3. Any punctuations apart from Dot(.), hyphen(-), apostrophe('), Space( ) are not allowed-->color should change to RED.
Example-
ShivakumaraNo Change
Shiva kumaraNo Change
Shiva'kumaraNo Change
shiva.kumaraNo Change
shiva kumarared
shiva''kumarared
shiva' kumarared
shiva. Kumarared
shiva.'kumarared
shiva:kumarared
I have a graph which displays information from a vlookup.. The vlookup can bring either numbers which are in percentages format or ordinary number format.
The question I have is is it possible for the graph to automatically change format depending upon the information who is looked up..
If so how can this be done..
I have used this forum periodically and it has saved my bacon on numerous occasions so I know someone can help with this!
I created a workbook for staff planning and it's becoming a behemoth!
I'm wanting a range of cells within a row to change the number format to "[hh]:mm" based on a value in the 2nd column of that row. I have played about with a few things and can't seem to get it to work. What I have so far is this:
Format the number in Column AL to '[hh]:mm' when Column C's value is ="P/T", otherwise format to 'General'
Column C's value is referenced from another sheet in the same workbook via a VLOOKUP function.
Column AL's value is based on an IF formula which goes like this:
=IF($C4="F/T",SUM($AJ4)+($AK4/2),SUM($D4:$AH4))
Basically the above formula asks if Column C's value is F/T then count (because if F/T then the corresponding values in that row are whole numbers). If not, then SUM (because if P/T the corresponding values in that row are set to [hh]:mm format).
I have another sheet in the same workbook which has code (quoted below) which does something similar but I don't understand it enough to get it doing what I want for the sheet in question.
My formula works fine, but I need the first number of the answer displayed with only two decimal places. The formula averages a set of numbers, then adds " of " and a cell reference.
I want the answer displayed as 2.43 of 3. Here is the formula.
=IF(AVERAGE(W12:W123)>0,(AVERAGE(W12:W123))&" of "& Z9,"") it works perfectly but displays 2.42857142857143 of 3.
The cell is formatted to NUMBER with two decimal places. . Changing the format has no effect, but changing the formula does.
If I use =IF(AVERAGE(W12:W123)>0,(AVERAGE(W12:W123)) or =AVERAGE(W12:W123) the result is 2.43. When I add the next portion, &" of "& Z9,"") it changes to 2.42857142857143.
I've tried reformatting cells and nothing changes. This is the only cell I'm having problems with. The worksheet seems to be working fine.
I have a few buttons on my page that I would like to change colors depending on the value in cell AI1. Right now this is the code I have:
[Code] .......
What I would like is to add formatting for "rounded rectangle 4" as well. if the cell = 1, number 4 changes color, if the cell = 2 both change color, and if the cell =3 then only 5 changes color.
The spreadsheet attached is a timesheet I use. The last column on the sheet (IV) keeps a running total of time spent on each project. I need a way that i can find out how much time was spent on projects in specific timeframes (eg 01/01/09 - 07/01/01, or 01/01/09 - 31/01/09). my initial thought is perhaps i can have a "from" cell and a "to" cell to input the dates i require and it will total the relevant cells in the rows below.
NOTE! This workbook uses VBA to auto hide any column which are dates previous to today's date so you may need to unhide them to view times logged.
Is there any way to have a cell change its colour depending on whether a condition is met. E.G., can i have it as green if the result is a plus number and red if its a negative?
View 2 Replies View RelatedHow to a change a cell colour to say red in B6 if cell b12 = 1 and if e6 = 1 to change to green. I thought I might be able to use conditional formatiing but no. I had set the spread sheet to do a cell just for a condition representing a sum from another worksheet and it was working fine but i have to incorporate the two together and am stock.
I have attached a spread sheet.
I have a spreadsheet with names and start / finish dates columns down the left hand side then a row of week commencing dates along the top.
for each name row I would like to change teh fill colour of a cell to green to represent the week in which they started and to red for the week in which they finished. I therefore need to cross reference the start and finish dates for each name with the relevant week commencing dates at the top. Somehow! I presume there is some kind of vlookup type formula that I need to use in conditional formatting, but I am not sure what.
i need to change the colour of 4 cells depending on the one cell.
i have found some vba codes on the net but they are all based on numbers and i need it on text.
so if a1 ="alert" make A1 to A4 red
if a1="passed" make a1 to a4 green... and so on
i cant used conitional formatting as i need more than 3 colours
In my spreadsheet cell g1 is a name and cells g3:g6 contain data for that name.
I have a macro that exports the data in g3:g6 to another workbook and then deletes it.
What I need is a macro that when I try to change g1, gives me a warning if there is unexported data in cells g3:g6 (ie the sum of them is greater than zero) and gives the option of either continuing or stopping (presumably using a YesNo box).
I am trying to create a sheet in XL 2010.
In Column A each cell will contain a date (differnet from other cells in that column) when inspection was last done.
Column B is when the weekly inspection is due.
Column C is when bi-weekly inspection is due.
Column D is when monthly inspection is due.
Column E is when 6 monthly inspection is due.
I need a formula to change the colour of cells B, C, D & E when each inspection is due depending on the date entered in A
I am hoping its possible that the cell colour can stay for 2 days after the due date and then return back to blank after the second day.
For example if cell A1 has a date of January 1 2013 then on January 8 2013 cell B1 turns red then on January 10 2013 the cell returns back to normal.
A1
B1
C1
D1
E1
Inspection Date
Weeekly Due
Bi-WeeklyDue
Monthly Due
6 Monthly Due
January 1 2013
Change red Jan 8 & return blank Jan 10
Change red Jan 15 & return blank Jan 17
Change red Feb 1 & return blank Feb 3
Change red June 1 & return blank June 3
Is it possible to use a formula to shade a cell dependent on a condition? I have tried an if formula (see below) but it is incorrect. =if('November 2006 SVOC'!B6>'March 2006 SVOC'!C6,'November 2006 SVOC'!B6 [red],if('November 2006 SVOC'!B6<'March 2006 SVOC'!C6,'November 2006 SVOC'!B6 [blue],))
View 3 Replies View RelatedI want to create a system based on the value of a cell appearing as a grade in the adjacent cell. The cell could contain any value from -10 to +10.
the criteria would be
value greater than +2 = 3
value between 0 and +2 = 2
value between -2 and zero =1
value below -2 =0
I can get a formula to work for a single instance but not multiple conditions.
My colleagues are working on multiple projects at once. The projects got different priority, so I'm looking for a formula that change the cell color if a person is working on it, depending on the project's priority.
For example if John is working on project: East(pri.1), South(pri.2) and West(pri.3). On East he got 5 remaining hours (cell E8). Then i would like that cell to turn red. For South green and West red.
I would like to conditional format an Object depending on the variance between 2 weeks.
If the value is > than 0% the arrow is Green and points upwards.
If the value is = to 0% the arrow is Yellow and points at at 90 degree to the left.
If the value is < than 0% the arrow is Red and points downwards.
Attached is a sample : rrow Conditional Format.xlsm
I want to change the colour of a cell depending on its value, when compared to another row of data identiified by a value in another cell. To try an clarify:
b3 = 1 b5=4 Because b3 = 1 then compare cell b5 with the row g1 as b3=g1 then depending on its postion set a colour
g1= 1 g2:g5 = 2,3,4,5,6
h2= 2 h2:h5 = 7,8,9,10
I have a variable "strDate" that has date in it as numbers ie. 04 2006... I need to change it into mmm-yyyy format eg. Apr 2006
View 3 Replies View RelatedI cannot change the number format in a single sheet of my spreadsheet. I can go to format cells and select colour etc but it will not change the number format.
Another issue on the same sheet is that linked cells i.e cell b3 formula "=A3" displays "=A3" not the actual value in A3. Again, i cannot change the number format of this cell and yes, auto calc is on.
Whilst not wanting to offer a partial explaination (per the rules) just before this happened i was pressing a combitation of Shift+Control+` - not sure if that is relevant.
I want to get the sheet back to its 'normal' working order.
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 RelatedAs seen from my attached file,
The colour of the percentage cell changes according to its percentage. I would like the S/N cells to have the same colour as the total percentage cells automatically. (Even when the percentage updates)
Secondly, for the cells under "Target", if the target dates are 1 day before today(the current date on a particular day) and the actual date is not filled, the cell fill will turn amber.
If today is on or after the target date and the actual date is not filled, the cell will turn red.
However, if the actual date is filled, the target date cell will be filled green, overwriting the above two condition.
FormattingHelpExample.xls
i have a column with times of day and need a helper column to show the starting hour. for example, column a1 - 9:53am would like b1 to show 9am or 9. i tried customizing the date format to 'h' only, but when I pivot the table it still shows several 9's
View 2 Replies View RelatedI have a large project with many worksheets. Somehow I managed to change the default number format for new worksheets to Time. So... when I create a new worksheet and paste stuff into it I get everything in AM/PM. How can I change the default number format to Number?
View 2 Replies View RelatedI have been trying to type a date in a column in MS excel. But excel always reads it as a serial number.
How Can I get it to display the date as it is in date format. I have the next column formatted/ formulated to display the day of the date in the previous column.
Is there any way to change the decimal point from the usual period symbol (.) to a comma (,). The reason being, i have a structural design spreadsheet and the new terminology is just so, changed from 00.00 to 00,00 I have tried cell formatting but it just uses a comma (,) for numbers in their thousand and it wont do what i need.
View 3 Replies View Related