linking rows to columns? and changing colours?

  • Thread starter Thread starter EARTHWALKER
  • Start date Start date
E

EARTHWALKER

Excel 2002 SP2.

A couple of things I really can't work out after many mnay hours o
trying.

Ok. What I have is about 20 columns. The only 3 I am intersted i
are these.

A B C
Name | Initials | Pay Number.

on a separate sheet I have just those 3 columns with all of thos
details filled in.

Now, what I am trying to do is make is easy for the keyer. Rather tha
each day putting in about 200 from a huge list of 900 or more; names
initials and pay no.s. When the keyer puts in a pay number, I woul
like the other 2 columns to be auto filled in with the corretc info.

Eg. imagine colums 20 is fred | AB | 12345
Whne on the other sheets the keyer outs in the Pay No. of 12345 the tw
other colums are auto filled with Fred and AB respectively.

I think that makes sense :D

The other problem is. This list with the 20 or so columns will reac
to 10,000 or more.
So, If a payno. is put in say today, and that same pay no. is put i
again, something flags up so say "pay no. already put in" just as
recolection. Perhaps just changing the cell colour to red if any pa
no.s are put in more than once. That would save having to check an
filter all of the new entries.

Make sense too? I hope.

The first problem, I almost had using a lookup formula but it did giv
some very strange results...amused me...well, for the first hour. Th
nest 4 hours had me losing a lot of hair :D

Any help would be gratly appreciated
 
Hi
try the following (assumption: your details worksheet is named
'detailed')
In A2 (Name column) put the following=
=IF(C2<>"",INDEX('details'!$A$1:$A$1000,MATCH(C2,'details'!$C$1:$C$1000
,0),1),"")
in Be (Initials) put
=IF(C2<>"",INDEX('details'!$B$1:$B$1000,MATCH(C2,'details'!$C$1:$C$1000
,0),1),"")
copy both formulas down

For highlighting duplicated paynumbers try the following:
- Select the entire column C
- Goto 'Format - Conditional Format' and enter the following formula
=COUNTIF($C$1:$C1,C1)>1
and choose your format to tag duplicated entries

HTH
Frank
 
Back
Top