|Sorry, the question heading left off a < symbol! It should read that I want to return an asterisk on one sheet if any cells in a range on another sheet are <101.|
I maintain a fleet of four aircraft, and one of the tools I use is an Excel workbook that has two sheets, one that tracks the total time on each aircraft in hours (Daily Status), and the other that tracks an annual budget for parts due on each aircraft during the current fiscal year (FY 10-11). FY 10-11 tracks each aircraft's parts and the times those parts need to be replaced.
This workbook was written, and is used in Excel 2003, SP3.
What I want to do is put an indication by a particular aircraft on the Daily Status sheet (such as an asterisk) if any of that particular aircraft’s parts on the FY 10-11 sheet are due within 100 hours.
For example, Aircraft #1 currently has 3,400.0 hours on it according to the Daily Status sheet, and it has an engine due to be replaced at 3,500.0 hours, as listed on the FY 10-11 sheet. A calculation is done on FY 10-11 to show that the part is due in 100 hours. I want an asterisk to show up by Aircraft #1 on the Daily Status sheet informing me to check the FY 10-11 sheet to see what parts are coming due.
It is easy to get an asterisk from only one cell on the FY 10-11 sheet by using this:
=IF('FY 10-11'!E14<101,"*"," "), where E14 is the time remaining for that part.
My problem is that I have a list of several parts for each aircraft, any of which may be within 100 hours. Say for example the range for the times the parts are due for aircraft #1 is E14:E20, I don't know how to make one cell on the Daily Status sheet return an asterisk if any (or all) of the cells in E15:E20 are <101 hours.
Can this be done?