Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 1
Default Duplicates and different worksheets

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
  #2   Report Post  
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.
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Finding duplicates within several worksheets Norm75 Excel Discussion (Misc queries) 2 May 2nd 09 02:10 PM
Search for duplicates across three worksheets Sarah_Lund Excel Worksheet Functions 1 May 20th 08 12:29 AM
Disallow duplicates in different worksheets ClaireView Excel Discussion (Misc queries) 5 February 4th 08 09:05 PM
Finding duplicates in 2 worksheets darkwood Excel Worksheet Functions 2 May 12th 06 08:06 PM
excel duplicates on 2 worksheets Luke Rogers Excel Discussion (Misc queries) 3 February 6th 06 05:31 PM


All times are GMT +1. The time now is 10:40 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"