Tell me more ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.
up vote 0 down vote favorite

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: 

=IF(ISERROR(VLOOKUP(A1'SHEET1'!$A$2$A$160,1,FALSES)),"",VLOOKUP(A1,'SHEET1'!$A$2:$A$160,1,FALSE))

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.

share|improve this question
Not clear about "separate worksheets in the same spreadsheet" (or FALSES) but have you seen the answers to "How to identify cells in Column A that contain any of the values in Column B" - once identified it would seem to be a small step to filter on the identification and highlight the resultant rows. – pnuts Nov 2 '12 at 9:02
Note also “in conditional formatting, to reference a range on another worksheet (or workbook) you need to use a named range” from @chris neilsen. – pnuts Nov 2 '12 at 9:33

1 Answer

up vote 0 down vote

Is this the sort of result you are aiming for?:

SU498145 example

(with Applies to =$A$2:$E$9).

share|improve this answer
YES! That was it, pnuts! I was forgetting to make the "Applies to" cover the upper-right to lower-left ends of the data. Thank you! – Alfred Nov 2 '12 at 17:17
@Alfred The "Applies to" is maybe not as obvious as it might be, given that a LOT of people seem to have this problem. PS Don't forget the named range tip! – pnuts Nov 2 '12 at 17:24
Thanks, I won't! – Alfred Nov 5 '12 at 19:58

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.