Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel
I have excel sheet that has a vlookup formula and conditional formated
accoridning to dates. I want to select the data and special paste only values and formats to another worksheet and those dates to become a formula so that the dates dependent on the date to become a X with red background and R X with Green background and A with yellow backgound. How do I do this.Please |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel
After pasting only values and formats and then to mention
"to become a formula" you lost me completely. Not to mention dates dependent on a date when there are no formulas. <shanaron wrote... I have excel sheet that has a vlookup formula and conditional formated accoridning to dates. I want to select the data and special paste only values and formats to another worksheet and those dates to become a formula so that the dates dependent on the date to become a X with red background and R X with Green background and A with yellow backgound. How do I do this.Please |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel
On Sep 23, 1:41 pm, "David McRitchie"
wrote: After pasting only values and formats and then to mention "to become a formula" you lost me completely. Not to mention dates dependent on a date when there are no formulas. <shanaron wrote... I have excel sheet that has a vlookup formula and conditional formated accoridning to dates. I want to select the data and special paste only values and formats to another worksheet and those dates to become a formula so that the dates dependent on the date to become a X with red background and R X with Green background and A with yellow backgound. How do I do this.Please- Hide quoted text - - Show quoted text - I am pulling data with the v lookup function in a workbook which also condtional formats. Using that data I would like to make e.g. any date less than 01/09/2007 become R with red background and anything greater than 02/09/2007 become R X with green background. Can this be done. Currently trying to hand over the data. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel
<shanaron wrote...
After pasting only values and formats and then to mention "to become a formula" you lost me completely. Not to mention dates dependent on a date when there are no formulas. <shanaron wrote... I have excel sheet that has a vlookup formula and conditional formated accoridning to dates. I want to select the data and special paste only values and formats to another worksheet and those dates to become a formula so that the dates dependent on the date to become a X with red background and R X with Green background and A with yellow backgound. How do I do this.Please- Hide quoted text - - Show quoted text - I am pulling data with the v lookup function in a workbook which also conditional formats. Using that data I would like to make e.g. any date less than 01/09/2007 become R with red background and anything greater than 02/09/2007 become R X with green background. Can this be done. Currently trying to hand over the data. As long as you still have numeric dates on you "copied to" sheet you should be able to copy all of the formats from the original to the new sheet using Ctrl+A then copy (Ctrl+C) then on the second sheet use Edit, Paste Special, Formats only. Above Excel 2000 suggest hitting Ctrl+A three times due to developers ruining this perfectly good shortcut. If you don't already have Conditional Formatting then select cell A1 and select all cells (3 times Ctrl+A) then since A1 is the active cell we will address that cell as $A1 in the formula and the formula will refer to cell A1 for every cell on that row. CF1: =$A1<Date(2007,01,09) color Red background CF2: =$A1Date(2007,02,09) color Green background If the date in is another column use $x instead of $A You cannot put an "X" into a cell with conditional formatting My page on Conditional Formatting, and please read the comment in Red http://www.mvps.org/dmcritchie/excel/condfmt.htm and always keep in mind: (see blue writing in an outlined box) Which cells can be get coloring is based on the selection. The formula itself is based on the active cell. Each cell in the selection will be tested and uses 1 of the 3 conditions per cell that you are allowed in C.F. (some of this changes in Excel 2007 and haven't had a chance to look at it). Therefore, you would make the column portions absolute in your C.F. formula, when you want to color by rows. -- HTH, David McRitchie, Microsoft MVP -- Excel My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm to do that you would have to code a similar formula into your column. such that A1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|