Retrieving value of a corresponding cell in same row

[moth_on_the_wall]

Hi all,

I am currently struggling with a spreadsheet that has been created in Excel 2007. Essentially, it has a number of items (individually identified by “S code” in the first column) that need to be tested at the specific dates over a one year period (i.e. at “2 weeks”, “4 weeks”, “8 weeks”, etc) as shown in the screenshot below.

A user manually enters “Complete” into the corresponding cell in the “In-testing status” section of the spreadsheet when testing has been completed for a certain item at a particular time point.


Screenshot.jpg


I already have set up conditional formatting that highlights cells with dates older than the current date red. What I need to do now is to check for a particular item and date whether or not the corresponding “In-testing Status” cell reads “COMPLETE”. If it does, I need to use a conditional formatting rule to return formatting to normal.

What I am unsure of is how exactly to retrieve the value of the corresponding “In-testing Status” cell.

Unfortunately I can’t use a macro-enabled workbook in this environment so was wondering if anyone has any suggestions as to how to approach this without VBA? Please let me know if there is any additional information I can provide to clarify my question.

Any help or advice would be very much appreciated.

[cbatrody]

Hi,

Welcome to the Forum.

You can use INDEX & MATCH functions to retrieve a cell value based on the row & column. Please post a sample template of your workbook in excel format using "Go Advanced" option.

[moth_on_the_wall]

Thank you very much for your reply. I did consider the index and match approach, but wasn’t sure how to look up the corresponding column in the “In-testing Status” section in the context of a conditional formatting rule for the whole worksheet.

For example, for the first item (S code S25252002), the 13 weeks testing date is 3/10/2013. I would like to look up the data in the corresponding 13 weeks cell in the “In-testing Status” array (i.e. M7 in the attached spreadsheet). But I’d like the rule to be able to check all testing dates for any item against their corresponding “In-testing Status” cells.

Example.xlsx

[cbatrody]

Hi,

Please see the attached file. I have added a new sheet and updated the formula in that.

[moth_on_the_wall]

Thanks again cbatrody for the quick response. I understand the formula you've included and can see how it would work with the fixed S code and testing week values in Sheet 2, but am still not entirely sure how I would go about adapting it into a formula for a conditional formatting rule that I could apply to all of the testing dates for all items in Sheet 1? For example, if the 13 weeks "In-testing Status" for any of the items was "INCOMPLETE", I would like to change the background colour of all of the corresponding 13 weeks testing dates for those items.

My apologies if I am being unclear, please let me know if I can clarify this any further.

[cbatrody]

Something like this? Please see the attached file.

[moth_on_the_wall]

Apologies for the slow response. Yes, that is exactly the sort of conditional formatting that I would like to implement. If you have any thoughts on the syntax of the rule I'd be very appreciative.

[cbatrody]

In cell C7 (Sheet1) in the conditional formatting window, please type the following formula after choosing "Use a formula to determine which cell to format".


=K7="INCOMPLETE"

Then copy the cell using "Format Painter" and format the complete range of dates.

[moth_on_the_wall]

Thanks a lot for your reply again. What you have suggested works really well as a stand alone rule, but now I'm struggling to combine it with a pre-existing rule I had. Essentially what I want to do is highlight testing dates that have already passed (i.e. are less than the current date which I have in a fixed cell) and that have a corresponding "In-testing Status" of "INCOMPLETE". I know there must be a way to combine both in one conditional formatting rule, but for the life of me haven't been able to find a way to do it.

You've been really helpful cbatrody, I really appreciate it. My apologies for being dense!

[cbatrody]

Hi,

No worries, please see the updated file. I have changed the conditional format as following-

=AND(C7<TODAY(),K7="INCOMPLETE")

[moth_on_the_wall]

I have no idea why this is working in the example spreadsheet but not my own original... While I had C7 selected I went into the Conditional Formatting window and entered in the formula =AND(C7<TODAY(),K7="INCOMPLETE") to highlight relevant cells red as a rule as you suggested, and afterwards used the Format Painter tool to copy the formatting of C7 across the rest of the range. It still doesn't seem to be highlighting dates older than the current date red, however. I'm not at all sure what it is that I'm doing wrong in the original spreadsheet.

[cbatrody]

Hi,

It should ideally! Please post that file if you want me to have a look at it.

[moth_on_the_wall]

Apologies again for the slow response. Here is a slightly modified version of the original spreadsheet.

Online Example.xlsx

[cbatrody]

Hi,

I have modified the conditional formatting in the attached file. Please see if the color coding meets the desired output.

[moth_on_the_wall]

Thanks again, but for some reason, in the last two rows the 2 weeks testing dates are not highlighted even though the dates have passed and the status is "INCOMPLETE"?

[cbatrody]

Please see the attached file.

[moth_on_the_wall]

Thanks again for your assistance and patience, but unfortunately incorrect cells are still being highlighted. Who would've thought such a simple concept could get so complicated!

Basically, a cell should be highlighted red if its date is older than the current date and its corresponding "In-testing Status" is "INCOMPLETE". So if there is a cell with the date "6/08/2014" and its corresponding "In-testing Status" is selected as "INCOMPLETE", then it should be highlighted red.

For some reason in the most recent versions of the spreadsheet that you've posted, some such cells are not highlighted red, while others with dates that haven't occurred yet are highlighted red. I can't quite figure out why this would be the case and would very much appreciate any further input you can offer.

[cbatrody]

Please check the attached file.

[moth_on_the_wall]

Brilliant, that works perfectly now. What was it that you changed in the last version?

My next question would then be: Is there a way I can count the number of highlighted cells using a countif() function?

Thanks so much for helping me out!

[cbatrody]

I reapplied the conditional formatting clearing the previous ones & it worked

No, you can't count the highlighted cells using "COUNTIF" function, you would need a macro for that.

[moth_on_the_wall]

No worries, thank you very much! I'm very glad to finally see it sorted.

[cbatrody]

You are welcome

If that takes care of your question, please mark this thread as Solved by selecting Thread Tools --> Mark thread as solved.