View Single Post
  #2   Report Post  
Spencer101 Spencer101 is offline
Senior Member
 
Posts: 663
Default

Quote:
Originally Posted by speedstr View Post
excel 2010

I have a workbook with 2 worksheets. I have 16 rows of numbers arranged as:

Column A Column B Column C
1-1 2-1 3-1
1-2 2-2 3-2
1-3 2-3 3-3
etc......

On the same worksheet I have the same numbers but listed all down one row:
Column D
1-1
1-2
1-3
2-1
2-2
2-3
3-1
3-2
3-3
etc.....

I can create a dropdown list from the single row of numbers. I can highlight duplicates between the dropdown list and the 16 rows by using the conditional formatting ( highlight cell rules, duplicate values). So what happens ( all within the same worksheet) is that I can choose a number from the dropdown list (say, 1-2) and it will highlight the 1-2 in the column of the 16 rows that contain 1-2. That's not an issue and works great.

My question is that I would like the drop down list to be on a separate worksheet and still retain the ability to highlight duplicate cells between the dropdown and the 16 rows. so basically the dropdown list on one sheet and the 16 rows on another sheet. Choosing a number from the dropdown list will highlight (or color) the same number in the other sheet.
Is that possible? Or am I not making sense of what i am trying to do?

Thnaks for any help
As far as I'm aware, conditional formatting only works if the cell you're referencing is on the same sheet. (I'm using 2007 so have no idea if this has changed in 2010)

You can get around this in a simple way by having a hidden cell on the sheet with the numbers that will be highlighted that references the cell on the second sheet that contains the drop down list.

Hope that makes sense. Let me know if not.

S.