I am trying to link a range of cells from one sheet that contain various dates to another sheet in the same workbook. Some of the cells are empty. I select the source cells (say, F8-F19), copy, select target worksheet, Paste Special (and select Paste Link button). Target cells display accurate date information that updates as source cells are changed, however, any source cells that were blank now read 1-Jan-04. I have checked to ensure that all source/target cells have the same formatting (Number-Date).
When trying to Paste Special values and/or number formats only, the option to Paste Link is no longer available. How can I link the cells so that blank source cells result in blank target cells? Issue seems similar to: http://www.excelforum.com/excel-gene...worksheet.html
I have a date in a cell that when it is less than today() (so yesterday's date) would turn red and the 5 cells to its immediate right would also turn red, if the date is still current then it would be green and so would the right hand cells.
I looked through the format link: Conditional formatting page on Ozgrid and was unable to figure out what I want to do.
What I want to do is check a range of cells which have dates in them, and then if they are between such and such dates, they will turn a certain background color. But by "such and such" I mean, between the dates entered in two different cells.
So in the range of A1:A10, if any of those cells are between b1 and b2 then apply color1. If any of them are between b2+1 and b3, then apply color2.
If it's not possible tell me but I think it should be as I can do it with conditional formatting. I need to have 16 conditions though. What it is is a chart with projects in rows and dates in columns. The dates often change and I want to rearrange the entire schedule visually just by changing the date in another place. (I change the date in the target cell, what I call b1 above, and b2-10 are calculated based on that date). Hope that's enough information.
I have one cell with $20,000 hard coded (cell A1). In another cell I want the cell to say "20,000 Capital Raise" (cell B1). And i want B1 to link to A1 so that if i change the number in A1 it will also change in B1.
Here's what i have in B1 so far: ="$"&J6&" Capital Raise"
This produces "$20000 Capital Raise" in cell B1.
So the only thing i'm trying to figure out is how to get the comma in the $20,000 so it will read "$20,000" instead of "$20000".
I am trying to get a row of cells to highlight a percentage based on a date range
Below is an example of what my spreadsheet will look like, very simple for managers to read and understand but I am stuck on how i can get this to display the right way.
In the example i would need the Jan column to colour for a certain percent for 21 days and continue to feb for 26 days. Im not sure if this makes sense but this is what they are asking for. Colour bars to simple show the percent of days off each month.
Name Start Date End Date Jan Feb Mar
Dale 11/01/14 26/02/14 21 days 26 days
I have attached the spreadsheet for an example : Book1.xlsx
I have two conditions setup in Options>View - Zero Values.", "style="background: #FFFFFF;padding: 2px;font-size: 10px;width: 550px;"");' onmouseout='GAL_hidepopup();'>formatting.htm" target="_blank">conditional formatting. The first is setup for alternate row coloring with this formula inside Conditional Formatting:
Formula is = MOD(ROW(),2)
My Second Condition is
Cell Value is between $P$10 and $Q$10. This sets the font bold and a different color. The two cell values are two dates. I want to change the cells font color and bolding as long as the value is within that date range. It works fine, but for cells that are on the row that is colored the second condition doesn't apply for some reason.
A little background on what i am doing. I have a spreadsheet that tracks when i have blown the dust out of our computers. I have set up conditional formatting so that the text turns red after 300 days and the cell turns yellow after 600 days. However, some computers are in high dust environments. I would like the spreadsheet to tell me to blow the dust out of these sooner. see the attached sheet.
I have attached the sample. I need the cells without the employee or without a boss to highlight a color and i also need the date of certification to highlight if it is more than one year old.
I have a column of dates in column M, and in A1 I have the formula =TODAY() for today's date. I would like to conditionally format all of column M (up to row 198) so that it turns orange if the date in M is before today and after or equal to 3 working days before the date in M.
I have this =AND($A$1<M2,$A$1>=(WORKDAY(M2,-3))) But it doesn't appear to be working because it's highlighting dates after todays date.
Is there a way to date format a cell so that when I enter only the number date (ie today is October 13 so I'd enter 13) the cell formats to the current month with the date?
So for today I'd enter "13" and the cell would read 13-Oct. Additionally, when the month turns to November that same cell, without me making any changes to it, would change to 13-Nov. For future dates it would always show the current month with the date I entered manually.
I feel like this is a pretty simple question to answer but I cant find an answer to it anywhere or I'm just not skilled enough to understand it. I have two columns D and E that have dates in them. Column D is the purchase date of a computer and column E is how many years months and days its been since it was purchased. I want both columns to be the same color. If the computer is 2 years old or less I want it green, 3-4 yrs old yellow and 4+ red. I have it working for column D but I can't figure out how to apply it to column E.
I thought I read somewhere that it wasn't good practice to format a whole column(s) b/c then you would have empty cells that were formatted. Is there a better way that you guys format your columns without selecting the whole column? I know you can do this in VBA, but I was just wondering if there was a way that I'm unaware of.
Second, is there a formula that can inserted into conditional formatting, that will check a named range against two other cell values. If that ranges date is > AB2 and <AC2 then it will bold that range? I'm trying to figure out how to do things without always relying on VBA if possible.
I have a workbook that uses the values that a user had entered into 3 cells to calculate multiple other charts/diagrams on multiple sheets within the workbook. Each sheet would show what the user had entered in the 3 cells to allow them to see what is being used to calculate each table. Is it possible to link these cells so that the user can change the 3 values without having to go back to where he originally entered the 3 values?
For example, a user has entered in 3 values in Sheet 1. A formula in Sheet 2 displays what is entered by the user and uses these calls in Sheet 2 for calculations. When the user wants to change the three values, he would have to navigate to Sheet 1 and enter in the new values to have the workbook recalculate all the tables. Is there a way to link the three cells from Sheet 1 and Sheet 2 so when the user is on Sheet 2, he has the opportunity to change the values on the current Sheet without having to navigate to Sheet 1 to do so?
how the scrolling date bar in the attached is achieved. I am not asking for a step by step with code I am just after the basics of how this was achieved and i will research the rest. Most importantly is how to associate the dates with the rest of the cells in the column, as I imagine this is how I am going to fill the cell...The purpose is to add this scrolling date bar to my workbook, also attached.
I have a number in A1, another number in B1, a Date in C1. In D1, I am referencing all 3 of the other cells: =CellA1 & "-" & CellB1 & "----" & CellC1. Trouble is the date is coming through as a number. Is there a way that I can get the date to show as displayed as opposed to the value of the cell?
I need for coloring "Expected Delivery Date" column corresponding with "Control Date" and "Control Result. But I want to correspond with the cells' last content of "Control Date" and "Control Result" But first of all I want "Control Date" should be flashed 10 days ago of it's date... (it's already done on my excel file)
Then I want "Expected Delivery Date" as green when - "Expected Delivery Date" =< "Control Date" and "Control Result" =Y (Means Control is Ok on due time)
I want "Expected Delivery Date" as red when - "Expected Delivery Date" > "Control Date" and "Control Result" =Y (Means control is ok not on due time)
- "Expected Delivery Date" > "Control Date" and "Control Result" =N (Means control ok is not given, delayed...)
I'd like my conditional formatting to format the range of cells red if the range K2:S2>H2. That is when the range K2:S2 are added together, and if their sum is greater than H2, format cells K2:S2 red.
What I am trying to do, is apply conditional formatting to a range of cells (for example, A2:J2) based on whether the data in one of the cells (D2) contains "Yes" or "No". I would like the entire range of cells to be one color if D2 contains "Yes", and another color if D2 contains "No". If needed, this can be split into 2 different conditional formatting rules.
I am wondering if there is a quicker way to set up conditional formatting for a range of cells. In my projects, I have a range of cells N7:U16, where each row (7-16) would need to have a cell formatted based on a value in the cell adjacent to the range. I.e. the cell in range for row N7:U7 needs to be formatted based on the value in V7, and so on down for for rows 7 thru 16. Said another way, if the cell in the row range between n7:u7 = v7, then the cell in the range is uniquely formatted.
But I want to apply this logic all at once, instead of having to conditional format each row individual. The trick is that the cell with the value that I am comparing to is variable (i.e. V7:V16), but does correspond with the row I am comparing it to.
conditional formatting a range of cells where i need more than 3 conditions, so please see below and can anyone kindly construct some vba code for me to do this (And if you have time to explain how it works so i have a good understanding of this)
Range R6 to R299 contains delivery dates Range AO6 to AO299 is = to the corresponding cells in Range R but formatted to show the Day
Cell AL1 contains a date which changes on a Thursday to show the following week commencing date.
What i am after is code so that Range AO6 to AO299 changes cell colour to the following criteria
less than AL1 (White) Is between AL1 & AL1+7 (Blue) Is between AL1+7 & AL1+14 (Green) Is between AL1+14 & AL1+21 (Yellow) Is between AL1+21 & AL1+28 (Pink)
On attachment, I am trying to highlight values in column AA dependant on any values appearing in range AF4: AQ8 less 15 minutes. eg at present the value in AA29 is 13:22. I would like this cell to highlight as it is greater than 15 minutes before AH4 at 13:25.
The purpose of the sheet is to highlight any times in column AA that fall within 15 minutes of the start of a major meeting. These major meetings are pulled from another sheet, and a drop down box is used on that other sheet that then transposes the major meeting times to this sheet. I have tried an "Or' statement in a conditional format, however an error message appears around not using a range in a conditional format.
I have a spreadsheet with 'due date' for returns in column L and 'received date' for returns, in the adjacent column M.
I would like to get the due date to turn red when it is overdue, ie. the date in the cell has arrived and no return was submitted. By trawlling the forum, I managed to accomplish this with the following in conditional formatting: Cell value is less than or equal to =TODAY()
All's great.....but - I would like the date to turn back to black if I enter a date in the adjacent received column, column M.
In other words, I want to flag a problem if the due date has passed, but once I receive the return and enter the date I receive it, i would like the due date entry to revert back to the original formatting ie. black. So only the outstanding returns are highlighted.
I create the billing data for some key accounts and I was asked to build a summary sheet that brought all of them into one place so that the billing department didn't have to open each individual spreadsheet each month to verify their info to mine. What I would like to accomplish is to stay current according to today's date.
So if it's March, 10, 2014, I need to update the spreadsheet data according to the February 2014 spreadsheet a Key Account #1. Then in April, I need it to look for the new March 2014 spreadsheet. I'm assuming I can use an =IF(OR(... formula for this, but I'm just not sure how to get the date thing sorted out.
As far as naming of the the Key Accounts billing spreadsheets, they go something like 0214 "KeyAcct #1".xlsx. Then the new one will be 0314 "KeyAcct #1".xlsx. Even tho the next months billing file isn't created, the formula should be able to check against the date and know when to look for the new one.
I had a list of people on sheet1 of my workbook, and on sheet 2 a table with a list of names and their department.
I started off by wanting to use VLOOKUP within the conditional formatting feature of Excel to find anyone who worked in "finance" in sheet 2. I could get this formula to work on the sheet but as soon as I put it into the conditional format feature it wouldn't work.
Trying to find a work around I added a column in sheet 1 where I inputted the VLOOKUP formula and caused it to display "1" if the person worked in finance. It looked like this:
A: Person's name B: Value of 1 or 0
The idea was to hide column B and use conditional formatting to say "if column B2 = 1 colour column A2 blue". Now I could get this to work on a single row but couldn't make it work over a range, ie. "if cells A2:A150 have a 1 in the column next to them colour the relevent cell blue"
I feel I'm missing the obvious. I found quite a few answers online but non actually specified how to get the conditional formatting to recognise the relevent cell it should be looking at in a range.
1) How would I get conditional formatting working over a range
2) Whether it is possible to use VLOOKUP within the conditional formatting function of Excel.
