• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Very basic formula question

I

indi visual

Member
Forgive my ignorance.


=AND($G10<Today(), $U10="")


This works fantastic for one line. Please tell me I don't have to apply this formula for every line on my sheet (I have 1,000 lines that would take forever).


I've tried searching everywhere for this simple answer.


It seems my problems are too small. The simple answers are too hard to search for.
 
Well, it depends on what you're trying to do. If you're trying to have that formula result on a per line basis, then yes, you'll need to put that in every line. Fastest way is to double tap the box in the right hand corner of the cell. This will fill down your formula to the bottom of the data, provided that the column to the left doesn't contain any null values.
 
I was afraid of that. I guess it's time for plan B. Start the macro recorder, filter all yesterday's date to the top in that column, and apply a formula to each of the top 25 lines only (I shouldn't have any more than 25 jobs anyway).


All of this is for a formula that highlights the entire row red if the job is late.


What I'm doing with the formula is if the due date was yesterday and the complete date is empty, then mark the entire row red.
 
Hi,


Did you try conditional formatting?


The formula you can use for what you are trying to do is (What I'm doing with the formula is if the due date was yesterday and the complete date is empty, then mark the entire row ):


=AND($G10=TODAY()-1, $U10="")


You can apply this conditional formatting to any number of rows and mark the entire row.
 
Indi

As Varak has pointed out you can apply conditional formatting too all your rows at once

Select all the rows with your data or even beyond

Apply a Conditional Format using your equation =AND($G10<Today(), $U10="")

Change 10 to be the Top row number of your selected Rows

Excel will adjust all the other automatically

Code:

Have a look through some of Chandoo's CF Tutorials

http://chandoo.org/wp/2009/03/13/excel-conditional-formatting-basics/
 
Thanks to both Hui and VaraK, this has propelled my excel sheet to the next level. My excel knowledge is very limited, but I'm slowly getting the pieces together form my own independent thought.


Thanks again fellas. The simple fundamentals are what's getting me through the tricky stuff.


These tips helped a bunch!
 
You must log in or register to reply here.
Back
Top