Excel and linking worksheets (again)

  exdragon 22:47 14 Apr 06
Locked

Hi - can you link two worksheets so that if you enter anything anywhere in a cell on sheet 1 and the same words appear anywhere in sheet 2, then the cell in sheet one will change colour, or the font will change color?

It's to check that people aren't entering photos into a competition two years running - we'd like to improve on having to rely on someone saying, 'I'm sure we saw that last year' and then having to check back on previous entries.

What would be better would be if it was possible to use the entries in two adjacent columns, ie, a surname in sheet 1, rows 1 to 4, col B, with his current year's image titles in col C, rows 1 to 4. On sheet 2, his name and entries could be on any row at all, but still in cols B and C.

Hopefully - thanks in advance!

  VoG II 22:59 14 Apr 06

"an you link two worksheets so that if you enter anything anywhere in a cell on sheet 1 and the same words appear anywhere in sheet 2, then the cell in sheet one will change colour, or the font will change color?"

yes this is possible using Conditional Formatting.

"What would be better would be if it was possible to use the entries in two adjacent columns, ie, a surname in sheet 1, rows 1 to 4, col B, with his current year's image titles in col C, rows 1 to 4. On sheet 2, his name and entries could be on any row at all, but still in cols B and C."

I am sorry but this means nothing to me - can you explain?

  wbiggchiefy 23:01 14 Apr 06

Best place to go for this sort of info is on microsoft knowledgebase and click on excel experts - I tried this with several (what I thought were) complicated formulae and had several different replies (which all incidentally worked) within 15 mins.
There are people on there who just live for queries like this and will be able to provide you with a formula/macro to specifically do this

  exdragon 23:06 14 Apr 06

Sorry! I tried conditional formatting, but it said you couldn't do it with 2 worksheets (Excel 2003)

The second bit - for the purpose of this exercise, if Joe Bloggs last year sent us 4 pictures, I'd put his name on 4 consecutive rows in col B, with the titles of his pics on the same 4 rows in col C. (say his pics are named Rubbish, More Rubbish, Nonsense and Wonderful)

If he enters again this year, and I enter his name, and one of his pics is called Rubbish, then if the cell or font changes colour, the duplicate entry can be picked up there and then.

Does that make it any clearer? Maybe the third glass of Rioja wasn't such a good idea!

  exdragon 23:07 14 Apr 06

Thanks for that, I'll keep it up my sleeve, I think I may have a few more 'problems' later on!

  VoG II 23:23 14 Apr 06

Please be patient -
I'll attend to this first thing in the morning.

wbiggchiefy - I am one of those people. Unfortunately I had a meeting with Mr Daniels tonight.

  exdragon 23:25 14 Apr 06

You're a star - i was wondering how I could say I was ready for bed without hurting your feelings! No immediate hurry, honestly - sleep well...

  wbiggchiefy 23:27 14 Apr 06

No need to "keep it up the sleeve" - those guys live just to solve your & my everyday headaches and even make MR Average look soo good on excel - be careful not to tell anyone at work though or you will be either exposed or fed bum info that doesn't work and be made to look bad

  VoG II 06:46 15 Apr 06

OK, what you need is a macro along the lines of

Sub ColourMe()
Dim txtArray, clrArray, cel As Range, i As Integer
txtArray = Array("Whisky", "Gin", "Rum", "Vodka", "Wine", "Beer", "Bourbon", "Pinacolada", "Pernod", "Water")
clrArray = Array(24, 33, 38, 40, 36, 35, 34, 37, 39, 19)
For Each cel In ActiveSheet.UsedRange
For i = LBound(txtArray) To UBound(txtArray)
If cel.Value = txtArray(i) Then
cel.Font.ColorIndex = clrArray(i)
Exit For
End If
Next i
Next cel
End Sub


but in your case we need to read txtArray from Sheet2 and we only need one colour. I'm assuming that the workbook does not contain state secrets and it would be easier if I could see it for myself rather than you trying to describe its structure. Please click my yellow envelope to establish contact. This should keep me out of mischief as click here is down for maintenance.

  exdragon 09:22 15 Apr 06

OK, I've sent the message, but how do I attach the examples?!

  VoG II 10:37 15 Apr 06

The potential duplicates are in Column D of each worksheet. The following macro colous duplicates found on the newer sheet.

Sub CheckDups()
Dim NewSht As Worksheet, OldSht As Worksheet
Dim NewLastRow, iRow
Dim Found As Range
Set NewSht = Sheets("Entries2007")
Set OldSht = Sheets("Entries2006")
NewLastRow = NewSht.Cells(Cells.Rows.Count, 4).End(xlUp).Row
For iRow = 2 To NewLastRow
Set Found = OldSht.Columns(4).Find(what:=NewSht.Cells(iRow, 4).Value, lookat:=xlWhole)
If Not Found Is Nothing Then NewSht.Cells(iRow, 4).Font.ColorIndex = 3
Next iRow
End Sub

This thread is now locked and can not be replied to.

Huawei P10 review

1995-2015: How technology has changed the world in 20 years

An overview: What leading creative agencies are doing to improve diversity

New iPad, iPhone SE & Red iPhone 7 on sale now