A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Setting up and Configuration of Excel
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Duplicates and different worksheets



 
 
Thread Tools Display Modes
  #1  
Old March 15th 12, 05:57 PM
speedstr speedstr is offline
Junior Member
 
First recorded activity by ExcelBanter: Mar 2012
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
Ads
  #2  
Old March 15th 12, 07:46 PM
Spencer101 Spencer101 is offline
Senior Member
 
First recorded activity by ExcelBanter: Mar 2012
Posts: 662
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.
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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 12:40 PM.


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