| Home |
| Search |
| Today's Posts |
|
|
|
#1
|
|||
|
|||
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
|
|||
|
|||
|
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
|
|||
|
|||
|
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
|
|||
|
|||
|
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 |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Merge Worksheets | Excel Discussion (Misc queries) | |||
| Merge Worksheets | Excel Worksheet Functions | |||
| Copying data within worksheets with lookups | Excel Discussion (Misc queries) | |||
| How to protect and unprotect 30 worksheets in a file every month . | Excel Worksheet Functions | |||
| HELP! How do you--> Lock a set of rows but also link worksheets to | Excel Discussion (Misc queries) | |||
Hybrid Mode
