Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
sixpence668
 
Posts: n/a
Default 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

  #2   Report Post  
Max
 
Posts: n/a
Default

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



  #3   Report Post  
sixpence668
 
Posts: n/a
Default


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

  #4   Report Post  
Max
 
Posts: n/a
Default

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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Merge Worksheets Mark Jackson Excel Discussion (Misc queries) 1 June 9th 05 10:39 AM
Merge Worksheets Mark Jackson Excel Worksheet Functions 0 June 8th 05 10:42 PM
Copying data within worksheets with lookups MAWII Excel Discussion (Misc queries) 4 April 26th 05 07:23 PM
How to protect and unprotect 30 worksheets in a file every month . Protect & Unprotect Several Worksheets Excel Worksheet Functions 4 January 10th 05 02:29 PM
HELP! How do you--> Lock a set of rows but also link worksheets to FRUSTRATED Excel Discussion (Misc queries) 6 December 29th 04 11:05 PM


All times are GMT +1. The time now is 11:08 PM.

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

About Us

"It's about Microsoft Excel"