I am checking accountability of whether my office is creating source acknowledgement reports "C", recommended whenever a reference document "A" is read, and required whenever an article "B" is published that cites information from reference document "A". Both reports "C" and articles "B" cite the serial numbers of reference documents "A", and I can export spreadsheets from two different systems to create lists which count each separate type of document published for a specified period of time: one worksheet lists identifying data (including the source doc serial) of source acknowledgement reports "C"; the other list identifying data (including source doc serial) of articles "B".
By putting the data on separate worksheets in the same spreadsheet, I can identify which source acknowledgement reports were written about reference documents whicher were cited in articles by using this formula in worksheet 2:
It seems I have to insert the column of serial numbers matching both lists from worksheet 2 into a new column (B) on the article worksheet 1 in order to use the conditional formatting function in Excel 2007 (it won't work with multiple worksheets).
My problem is I want the conditional formatting rule to highlight each entire row green in the article worksheet 1 (but I will settle for just the serial # cell) if the serial # in one of worksheet 1's Column A cells matches any of the serial numbers inserted in worksheet 1 column B.