Lookups across worksheets
 

I need to consolidate some information, using several worksheets in one
file. Here's an idea of what I need to do:

WORKSHEET 1 - [/b]

_FIRST_|__LAST__|_COMPANYID_|_LOCATIONID_
BILL | SMITH | 1 | 3
JEN | JONES | 2 | 1

[b]WORKSHEET 2 -

_Company_ID_|__Name_
1 | Acme
2 | Widgets
3 | Bucky's

WORKSHEET 3 -

_LocationID___|__Name_
1 | New York
2 | Paris
3 | Rome

I need to come up with a set of formulas that give me the following
result:

WORKSHEET 1 -

_First__|_Last__|_CompanyID_|_LocationID_
Bill | Smith | Acme | Rome
Jen | Jones | Widgets | New York

Any ideas would be greatly appreciated!!

Jessica


--
sixpence668
------------------------------------------------------------------------
sixpence668's Profile: http://www.excelforum.com/member.php...o&userid=25472
View this thread: http://www.excelforum.com/showthread...hreadid=389209

One set-up to try ..

In a new Sheet4
------------
Copy paste Sheet1's headers into A1:D1
_FIRST_|__LAST__|_COMPANYID_|_LOCATIONID_

Put in A2: =IF(Sheet1!A2="","",Sheet1!A2)
Copy across to B2

Put in C2:
=IF(Sheet1!C2="","",VLOOKUP(Sheet1!C2,Sheet2!A:B,2 ,0))

Put in D2:
=IF(Sheet1!D2="","",VLOOKUP(Sheet1!D2,Sheet3!A:B,2 ,0))

Select A2:D2, fill down to say, D100,
to cover the max expected data range that'll be in Sheet1

Sheet4 will return the desired results:

_First__|_Last__|_CompanyID_|_LocationID_
Bill | Smith | Acme | Rome
Jen | Jones | Widgets | New York
etc

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"sixpence668"
wrote in message
...

I need to consolidate some information, using several worksheets in one
file. Here's an idea of what I need to do:

WORKSHEET 1 - [/b]

_FIRST_|__LAST__|_COMPANYID_|_LOCATIONID_
BILL | SMITH | 1 | 3
JEN | JONES | 2 | 1

[b]WORKSHEET 2 -

_Company_ID_|__Name_
1 | Acme
2 | Widgets
3 | Bucky's

WORKSHEET 3 -

_LocationID___|__Name_
1 | New York
2 | Paris
3 | Rome

I need to come up with a set of formulas that give me the following
result:

WORKSHEET 1 -

_First__|_Last__|_CompanyID_|_LocationID_
Bill | Smith | Acme | Rome
Jen | Jones | Widgets | New York

Any ideas would be greatly appreciated!!

Jessica


--
sixpence668
------------------------------------------------------------------------
sixpence668's Profile:
http://www.excelforum.com/member.php...o&userid=25472
View this thread: http://www.excelforum.com/showthread...hreadid=389209

That worked perfectly! Thank you so much!


--
sixpence668
------------------------------------------------------------------------
sixpence668's Profile: http://www.excelforum.com/member.php...o&userid=25472
View this thread: http://www.excelforum.com/showthread...hreadid=389209

You're welcome !
Thanks for the feedback
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"sixpence668"
wrote in message
...

That worked perfectly! Thank you so much!


--
sixpence668
------------------------------------------------------------------------
sixpence668's Profile:
http://www.excelforum.com/member.php...o&userid=25472
View this thread: http://www.excelforum.com/showthread...hreadid=389209