Filling Conditional Formats

[Cpar4944]

I have a spreadsheet that I use to keep inventory for our power sports dealership and would love some help in making it more efficient. Attached is a small sample of what our spread sheet looks like. My goal is to highlight the year in the "2016" tab a different color depending on how long the machine has been in inventory. White if under 60 days, yellow for a warning between 60-90, and red if it is older than 90 days. I believe my formula is correct unless I am over thinking it for the conditional format, but I can't drag it down in a manner similar to the fill handle where the conditional format changes to correspond with an adjacent cell. Any help on dragging conditional formatting down similar to a fill handle would be appreciated.

By the way, I tried right clicking, dragging down, and selecting fill formatting only, but it doesn't do what I am expecting.

Thank you again for any help provided.

[6StringJazzer]

You can copy and paste conditional formatting rules using the Format Painter. You can also edit the "Applies To" range in the Manage Rules window.

But I find your problem confusing. "My goal is to highlight the year in the 2016 tab a different color depending on how long the machine has been in inventory." But the CF rules on that sheet refer to the Polaris Combined sheet, which lists each machine multiple times. So what does it mean for how long "a machine" has been in inventory? For example, tab 2016 has an entry for "OUTLAW 50 | BLUE | A16YAK05AF" which looks like a machine, but that combination is listed three times on Polaris Combined, which looks like three machines.

I don't think the rules on the 2016 tab are doing what you want. Your rule refers to column AE but there is no data in that column.

[Cpar4944]

I'm so sorry. That was terribly confusing. I look at it all day so it makes a little sense to me. Here is the actual spreadsheet that I use with the numbers replaced by "x".

When a machine comes in, I put it in the Polaris Combined tab. This populates the 2016 or 2017 tab. We may have multiple units of the same model in inventory at any given time. Our sales people only use the 2016 or the 2017 sheet when they are working so it is the only sheet they print. I want to highlight the year depending on how long a machine has been in inventory so that they know which machines to try to get rid of first. If you click the A2 cell on the 2016 tab and manage rules, you should see 3 conditional formats. Each format takes the model number from the AA column and matches it with the same model code in the Polaris Combined column. Once its matched, I want it to see how many days it has been in inventory then go back to the 2016 tab and highlight cell A2 accordingly. I think the formula works, but my problem is that A3 is still pulling AA2.

The format painter still bases the formula off of AA2.

Again, thank you so much for responding and your willingness to help. I sincerely appreciate the help.

[JeteMc]

The conditional formatting rules need to evaluate to either true or false. Also since no formatting is going to be applied to the "<60" category no rule is needed. Here are the two rules applied to the attached file: =INDEX('POLARIS COMBINED'!S$2:S$218,MATCH(AA2,'POLARIS COMBINED'!D$2:D$218,0))="60-90" (Yellow)
=INDEX('POLARIS COMBINED'!S$2:S$218,MATCH(AA2,'POLARIS COMBINED'!D$2:D$218,0))="90" (Red)
Let me know if you have any questions.

[Cpar4944]

Wow thank you so much for your help! That works perfect. I really appreciate it.

[JeteMc]

You're Welcome, thank you for the feedback and for marking the thread 'Solved'. I hope that you have a blessed day.