Excel / conditional formatting assistance

[Kol]

Hi guys,

I've been doing a fair bit of googling and reading tutorials but I wondered if you guys could give me some guidance on some advanced (for me, especially!) techniques in excel to try and make my life a little easier.

I'll give you an example of what I'm trying to achieve:

Excel spreadsheet consisting of three worksheets (apologies if the terminology is incorrect).

Worksheet1 has a list which has some of my data listed by ID number.
Worksheet2 has all of my products listed by ID number and name.
Worksheet3 I wish to have all of the ID numbers referred to by name.

I'm wishing to use conditional formatting to take the ID number (in worksheet1), resolve the ID number to name (from worksheet2) and export this to worksheet 3 which it then updates.

I wondered if any of you guys could help me in working out what I should do to get the ID number to resolve to name and be exported to worksheet 3? I know it's possible because back when I was at university I did studied a module where I managed to get this work, however, I've not used excel since my 2nd year (2007) and since have been using a mac and well, have completely forgotten how to use it to this extent.

Any pointers? Any help would be greatly appreciated!!

 

[Tomsk]

Off the top of my head you want to use the INDEX and MATCH functions.
In Sheet3 each cell would have a formula something like:
INDEX(range of NAME data in Sheet2, MATCH(ID NUMBER, range of ID NUMBERS in Sheet2, 0 {for identical matching}), column of NAME data in Sheet2)

 

[Kol]

Tomsk - thanks for that buddy! That's what I thought. I was asking my housemate last night and he said, to be honest I'd be much better knocking something up in access, but my knowledge of that is non existent to say the least.

I don't suppose you (or anyone else maybe) could show me an example of the formula maybe? I appreciate this might be asking a bit much :/

Cheers.


edit - I've knocked up a very crude simple example version of what I'm talking about...
http://www.2shared.com/file/vCK8jwhI/Workbook1.html

 

[spydermixx]

In cell B2 on worksheet 3:

=INDEX(Sheet1!$A$1:$B$5,MATCH($A2,Sheet2!$A:$A,0),2)

and drag down. Should do the trick I think.