Excel

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

shanaron@optusnet.com.au - 23 Sep 2007 00:55 GMT

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

Reply to this Message

David McRitchie - 23 Sep 2007 04:41 GMT

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.
[quoted text clipped - 4 lines]
> with Green background and A with yellow backgound. How do I do
> this.Please

Reply to this Message

shanaron@optusnet.com.au - 23 Sep 2007 05:55 GMT

On Sep 23, 1:41 pm, "David McRitchie" <dmcritchie_xl...@verizon.net>
wrote:

> After pasting only values and formats and then to mention
> "to become a formula" you lost me completely. Not to mention
[quoted text clipped - 11 lines]
>
> - 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.

Reply to this Message

David McRitchie - 24 Sep 2007 00:05 GMT

<shanaron wrote...

>> After pasting only values and formats and then to mention
>> "to become a formula" you lost me completely. Not to mention
[quoted text clipped - 17 lines]
> 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: =$A1>Date(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.

Signature

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 to this Message