I have 2 columns of concern DATE and COST.
I would like the COST cell to be shaded if the DATE cell is less than a
specificed date.
Then if it is possible to calculate the COST field but only the ones
highlighted.
Help is greatly appreciated!!
Thanks, Stacey
Two part solution to your question:
First, calculate your cost formula only if the date in specified cell is less than (earlier) desired date.
Second, use Conditional formatting to change cell color if specified cell is less than (earlier) desired date.
Therefore, assuming units are in A4, unit price is in B4, Cost calculation to take place in C4 and date is in D4 enter this formula in C4:
=IF(D4<DATE(2005,2,1),A4*B4,"X") [of course adjust date and formula as needed]
Then, in C4, select Format>Conditional Formatting... Set Condition 1 as:
Formula is: =$D$4<DATE(2005,2,1) and set the Format options to your desired Font, Color, etc.
Adjust the $D$4 cell reference to 'relative' (D4) if you will have different dates for each row. Then copy C4 down your range.
Now, if the date is less than your specified date in the formula, the formula will calculate the cost and change the format of the cell. If not, it will place an X in the cell and leave the formatting unchanged.
NOTE: you could substitute a cell reference to the date (e.g. place 02/01/2005 in $C$1) instead of the DATE(2005,2,1) in both the formula and the CF (e.g.:
=IF(D4<$C$1,A4*B4,"X")
That way, if you need to change the reference date, you don't need to adjust all the formulas and formatting, just the value in C1!
HTH
Last edited by swatsp0p; 03-08-2005 at 12:52 PM. Reason: additional information...
Bruce
The older I get, the better I used to be.
USA
For the conditional format, use the following for the condition of the cost
column:
=$A$2<TODAY()
and so on, where cell A2 is the corresponding date column. Select the
appropriate shade on the pattern tab.
As far as calculating the cost column if it is highlighted, my only thoughts
are to write a macro that you can run that checks for all cost cells in the
column and performs the functions if the cell is shaded. Use an outside loop
that loops for all cells in the cost column with the following code inside:
If Range("B2").Interior.ColorIndex = 6 Then 'or whatever your shade color is
Range("B2").Formula = "hello" 'type whatever your formula is here
End If
Hope this helps!
"SMac" wrote:
> I have 2 columns of concern DATE and COST.
> I would like the COST cell to be shaded if the DATE cell is less than a
> specificed date.
> Then if it is possible to calculate the COST field but only the ones
> highlighted.
>
> Help is greatly appreciated!!
>
> Thanks, Stacey
I copied your formula and it worked but I want to enter a date i.e. 3/1/05
but it doesn't seem to work, I have: =$A$1<"3/5/05"
Where A1 is 3/8/05.
I will look into the macro.
Thanks!
"Chad" wrote:
> For the conditional format, use the following for the condition of the cost
> column:
>
> =$A$2<TODAY()
> and so on, where cell A2 is the corresponding date column. Select the
> appropriate shade on the pattern tab.
>
> As far as calculating the cost column if it is highlighted, my only thoughts
> are to write a macro that you can run that checks for all cost cells in the
> column and performs the functions if the cell is shaded. Use an outside loop
> that loops for all cells in the cost column with the following code inside:
>
> If Range("B2").Interior.ColorIndex = 6 Then 'or whatever your shade color is
> Range("B2").Formula = "hello" 'type whatever your formula is here
> End If
>
> Hope this helps!
>
> "SMac" wrote:
>
> > I have 2 columns of concern DATE and COST.
> > I would like the COST cell to be shaded if the DATE cell is less than a
> > specificed date.
> > Then if it is possible to calculate the COST field but only the ones
> > highlighted.
> >
> > Help is greatly appreciated!!
> >
> > Thanks, Stacey
Use this: "=$A$1<DATE(2005,3,5)"
"SMac" wrote:
> I copied your formula and it worked but I want to enter a date i.e. 3/1/05
> but it doesn't seem to work, I have: =$A$1<"3/5/05"
> Where A1 is 3/8/05.
>
> I will look into the macro.
>
> Thanks!
>
> "Chad" wrote:
>
> > For the conditional format, use the following for the condition of the cost
> > column:
> >
> > =$A$2<TODAY()
> > and so on, where cell A2 is the corresponding date column. Select the
> > appropriate shade on the pattern tab.
> >
> > As far as calculating the cost column if it is highlighted, my only thoughts
> > are to write a macro that you can run that checks for all cost cells in the
> > column and performs the functions if the cell is shaded. Use an outside loop
> > that loops for all cells in the cost column with the following code inside:
> >
> > If Range("B2").Interior.ColorIndex = 6 Then 'or whatever your shade color is
> > Range("B2").Formula = "hello" 'type whatever your formula is here
> > End If
> >
> > Hope this helps!
> >
> > "SMac" wrote:
> >
> > > I have 2 columns of concern DATE and COST.
> > > I would like the COST cell to be shaded if the DATE cell is less than a
> > > specificed date.
> > > Then if it is possible to calculate the COST field but only the ones
> > > highlighted.
> > >
> > > Help is greatly appreciated!!
> > >
> > > Thanks, Stacey
GREAT! That worked.
Thanks!
"Chad" wrote:
> Use this: "=$A$1<DATE(2005,3,5)"
>
> "SMac" wrote:
>
> > I copied your formula and it worked but I want to enter a date i.e. 3/1/05
> > but it doesn't seem to work, I have: =$A$1<"3/5/05"
> > Where A1 is 3/8/05.
> >
> > I will look into the macro.
> >
> > Thanks!
> >
> > "Chad" wrote:
> >
> > > For the conditional format, use the following for the condition of the cost
> > > column:
> > >
> > > =$A$2<TODAY()
> > > and so on, where cell A2 is the corresponding date column. Select the
> > > appropriate shade on the pattern tab.
> > >
> > > As far as calculating the cost column if it is highlighted, my only thoughts
> > > are to write a macro that you can run that checks for all cost cells in the
> > > column and performs the functions if the cell is shaded. Use an outside loop
> > > that loops for all cells in the cost column with the following code inside:
> > >
> > > If Range("B2").Interior.ColorIndex = 6 Then 'or whatever your shade color is
> > > Range("B2").Formula = "hello" 'type whatever your formula is here
> > > End If
> > >
> > > Hope this helps!
> > >
> > > "SMac" wrote:
> > >
> > > > I have 2 columns of concern DATE and COST.
> > > > I would like the COST cell to be shaded if the DATE cell is less than a
> > > > specificed date.
> > > > Then if it is possible to calculate the COST field but only the ones
> > > > highlighted.
> > > >
> > > > Help is greatly appreciated!!
> > > >
> > > > Thanks, Stacey
Chad,
How would I go about writing the code to looking for the shading and in turn
sum the cells that have it?
Thanks!
Stacey
"Chad" wrote:
> For the conditional format, use the following for the condition of the cost
> column:
>
> =$A$2<TODAY()
> and so on, where cell A2 is the corresponding date column. Select the
> appropriate shade on the pattern tab.
>
> As far as calculating the cost column if it is highlighted, my only thoughts
> are to write a macro that you can run that checks for all cost cells in the
> column and performs the functions if the cell is shaded. Use an outside loop
> that loops for all cells in the cost column with the following code inside:
>
> If Range("B2").Interior.ColorIndex = 6 Then 'or whatever your shade color is
> Range("B2").Formula = "hello" 'type whatever your formula is here
> End If
>
> Hope this helps!
>
> "SMac" wrote:
>
> > I have 2 columns of concern DATE and COST.
> > I would like the COST cell to be shaded if the DATE cell is less than a
> > specificed date.
> > Then if it is possible to calculate the COST field but only the ones
> > highlighted.
> >
> > Help is greatly appreciated!!
> >
> > Thanks, Stacey
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks