Copy Fill From Another Cell That Is Formatted To Change Depending On Its Formula
Dec 10, 2012
As 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
View 2 Replies
ADVERTISEMENT
Aug 29, 2012
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.
View 5 Replies
View Related
Feb 9, 2007
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 Related
Dec 30, 2009
i want to fill down a column and instead of my formula changing from A6 to A7 i want it to change to B6.
View 9 Replies
View Related
Feb 17, 2012
How to change a "ddd' formatted cell and change it to a text string (even if you have to use another set of cells)?
I have 3 columns of cells A,B,C.
Column A has dates in it.
Column B has the "ddd" format of the A cells (takes date from A cell and turns it into the day of the week...ex:2/17/2012 into "Fri")
Column C is the one I want to formatt whatever is in the B column into a string, such as "Fri" but with it being text instead of "ddd" formatted.
View 4 Replies
View Related
May 27, 2008
In a worksheet I retrieve data from a SQL query. Now I have to add 5 different calculations per row and each calculation will be stored in a new cell (so 5 columns will be created)
I want to do this with a loop macro and calculate this till the last "filled" row. The number of rows differs every month.
What loop code should I use and can somebody give me an example of the VBA code.
Note that some formulas contains nested If-functions.
View 6 Replies
View Related
May 1, 2008
I have a template I have been asked to amend and I know I need to use Conditional Formatting I just can't crack the formula I need to use. Cell A2 will have either "CD" or "CW" or "IN" input into it
When user inputs either "CD" or "CW" into cell A2, I want cell G2 to have "Margin Movement" automatically displayed. When user inputs "IN" into cell A2, I want cell F2 to have "MV" automatically displayed, and I want cell G2 to be coloured in bright red fill. Then user is to go to cell G2 and input some text as a description, and I want the red fill to disappear once they have input something in there.
I thought I had it licked using IF statements for the "Margin Movement" and the "MV" cells, but I can't get the conditional formatting to work - I assume this is because the cells aren't technically "blank", as they have IF statements in them.
View 3 Replies
View Related
Jun 23, 2009
Afternoon everyone i am having abit of trouble working on an excel 2007 spreadsheet. In cell I1 i have a tab called Color. i want the cells below I1 to be filled with one of 3 colors green, yellow, or red depending on whats in cell F "Status" (closed or open - in progress) and cell G ECD for estimated completion date.
Green - i need it to fill green if status is closed. Yellow - need it to fill yellow if ECD is any date greater than today and if status is open. Red - need it to fill red if ECD is todays date or older and status is open.
View 3 Replies
View Related
May 27, 2014
if I have one cell that ive enlarged to half the sheet.... and it is effected with rollover hyperlink so that when I hover over other cells. it turns different colours..
is it possible to make that cell open up different files using the double click event depending on the colour it currently is?
i.e. if its blue, open file1. if its yellow, open file 2 etc
View 4 Replies
View Related
Jul 6, 2009
i am looking for a formula, if i put it as plainly as i can then i hope someone can work it out for me!
if ad4 is greater or less than 30 then ad4 text is red, if ad4 is equal too 30 then text is green
View 14 Replies
View Related
Oct 10, 2008
i'm trying to ask my spreadsheet to fill a cell with either 'YES' or 'NO' depending on the value of one cell. I've succeeded in getting it to enter 'YES' but can't figure out how to tell it to choose between the two options. This is the formula so far
=IF(L5>2,"YES")
View 3 Replies
View Related
Jan 23, 2013
When doing a normal copy and paste of a formula you get 1 cell added to the formula
ie... a1=b1+c1
when dragged and copied to a2 you get a2=b2+c2
Is there a way to make it add more than one cell so that when the first formula is dragged and copied I would get a2=b17+c17
The actual formula I am doing a copy and paste with is:
=IFERROR(CHOOSE($I$2,'YTD & MTD'!D58,'YTD & MTD'!E58,'YTD & MTD'!F58,'YTD & MTD'!G58,'YTD & MTD'!H58,'YTD & MTD'!I58,'YTD & MTD'!J58,'YTD & MTD'!K58,'YTD & MTD'!L58,'YTD & MTD'!M58,'YTD & MTD'!N58,'YTD & MTD'!O58,'YTD & MTD'!C58),"")
I am needing to copy and paste to the next row but want 'YTD & MTD'D58 to change to 'YTD & MTD'74
View 1 Replies
View Related
Aug 12, 2014
I have a column who's content is determined via about 6 nested if statements from data on that row. That cell is then conditionally formatted to a certain color based on the text that is ultimately printed from the nested ifs (simply an extra visual legend for the text). All of this contributing info and about 1000 lines items make a very large and difficult to print page. What I am trying to do is a make a summary sheet that simply takes the index number of these 1000 rows and copies or links the conditional formatting of the mentioned cell onto this number on another sheet. I have already linked this status cell and put it adjacent to the index number which works well in that with two columns I can show the index and status but if I could combine the color of the status cell onto the index cell, it would be even better. I am pretty sure another conditional format for this summary sheet would not be possible or be extremely complex since the contents of the index cell I am conditionally formatting have no bearing on the conditions for the format. Was hoping there is some VBA magic that could simply mimic the conditional formatting from one cell and put it on another.
View 1 Replies
View Related
May 22, 2014
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.
View 2 Replies
View Related
Feb 18, 2010
I 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.
View 9 Replies
View Related
Apr 16, 2014
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 Replies
View Related
Jun 11, 2009
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.
View 2 Replies
View Related
Jun 3, 2013
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 Related
Oct 22, 2009
How 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.
View 14 Replies
View Related
Jul 11, 2007
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.
View 9 Replies
View Related
May 20, 2009
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
View 9 Replies
View Related
Feb 15, 2008
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).
View 3 Replies
View Related
Jan 9, 2013
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
View 3 Replies
View Related
Apr 29, 2014
I have an excel workbook that has many spreadsheets (each one sheet has a client name)I have another excel workbook that has these client names on one sheet (on a list) and next to every name I have a number (i.e total turnover of the year).
In the first workbook (where every client has his sheet (tab named after the client) I want to have a cell that equals to the sum of some cells on the other workbook, that refer to the specific client
(it looks like this ='[comissions NF 10-14.xls]comissions 14(auto)'!$J$81+'[comissions NF 10-14.xls]comissions 14(auto)'!$J$197+'[comissions NF 10-14.xls]comissions 14(auto)'!$J$313+'[comissions NF 10-14.xls]comissions 14(auto)'!$J$429)
I want this sum to be added to every sheet of this workbook. each sheet refers to a client, so $J$81, $J$197 etc must be changed for the correct cell that refers to the name of the client. The tab names are alphabetical and so s the list.
Is there any way to do it, without re-entering the formula to each one?
View 1 Replies
View Related
Jul 3, 2014
I am having a terrible time with Excel today. The version I'm using is Excel 2010.
I just want a simple division formula in a cell, formatted to accounting. This should be really easy, but it isn't reacting the normal way.
The simple division is =13588/12
The output in accounting format should be 1,132.33.
Every time I enter this formula into a cell that is pre-formatted to accounting, the result is 1132 1/3. It also erases my formula and replaces it with the value.
View 7 Replies
View Related
Apr 11, 2014
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
View 4 Replies
View Related
Nov 22, 2007
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
View 2 Replies
View Related
Jan 30, 2010
What I would like to do with the information that I have put together on a different workbook is to reference an Agent (column A), and a time range (Date is in column D) and then copy the row from A to I for each occurrence during that range of dates so we can see how many and what errors have occurred during any range of dates selected. (I will have different people inputting errors at different times so the dates will not be sorted unless they have to be). I want to be able to select different agents/date ranges to be able to get a glance of how that particular agent did during this time frame.
Here is an example of the work sheet where the data is originaly.
[Removed]
Here is an example (in a different workbook) of what the sheet should look like if I was searching errors for Albert Ozier between 1/25/2010 and 1/28/2010.
[Removed]
View 9 Replies
View Related
Feb 6, 2007
I have a formula that is averaging seven (one week) consecutive cells, I want to Copy/Fill the formula down the worksheet for the entire year. Is there a way to use the fill handle to drag down and make the formulas use the next seven colums?
I have removed the $ from the row numbers in the original formula, but when I use the fill handle it fills like this:
=average(B2:B8)
=average(B3:B9)
I would like it to fill like this:
=average(B2:B8)
=average(B9:B15)
View 9 Replies
View Related
Dec 6, 2009
I have some formulas that combine template text and values from cells in that row to result in text that I then Copy and use in another place. I am trying to convert that to a Macro.
My Skills are Novice -- I have searched Google and Tutorials and it seems that my usage is not common so, I have not found answers to help me move ahead.
I'm working with a simplified version of what I want to do to eastablish a
Working model that I can modify and expand. I had a macro that ran to completion but I abandoned it. I realized that I was on a Wrong Track. It was created from recorded macro sections and googled routines and very clumsy.
Heres my Scenario
If my Excel Cells were
Cell A1 = 135252
Cell B1 = $13.95
Cell C1 = 3
Cell D1 = Oversize
I want to run a Macro from E1 that would Result in text of
Our Stock Number is 135252 - The Price is $13.95.
We have 3 in stock. Type of Packaging is Oversize
being left in Cell E1.
I would like the Macro be able to run on other rows as well when launched from Cell E of that row.
My Goal would be to make the macro work on a range of rows by Highlighing Cell E in those rows.
My Excel Formula to accomplish the same thing is -
="Our Stock Number is "&A1&" - The Price is "&B1&".
We have "&C1&" in stock. Type of Packaging is "&D1&"."
If I could get this much working, I think I can expand and edit to encompass my actual application.
View 9 Replies
View Related
