linked sheets - trouble matching data

C

Chelrie

New Member
Joined
Jul 14, 2009
Messages
27
I have two files: 1) PO 2) SITE SUMMARY

I am working in PO and referencing SITE SUMMARY.

In PO

<table style="border-collapse: collapse; width: 194pt;" width="258" border="0" cellpadding="0" cellspacing="0"><col style="width: 17pt;" width="23"> <col style="width: 83pt;" width="110"> <col style="width: 94pt;" width="125"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 17pt;" width="23" height="20">
</td> <td class="xl66" style="width: 83pt;" width="110">A</td> <td class="xl66" style="width: 94pt;" width="125">B</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" height="20">7</td> <td class="xl68">To</td> <td class="xl69" style="border-left: medium none;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" height="20">8</td> <td class="xl68" style="border-top: medium none;">Email
</td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" height="20">9</td> <td class="xl68" style="border-top: medium none;">Address</td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" height="20">10</td> <td class="xl68" style="border-top: medium none;">City, State ZIP</td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> </td> </tr> </tbody></table>

in SITE SUMMARY:

<table style="border-collapse: collapse; width: 720pt;" width="956" border="0" cellpadding="0" cellspacing="0"><col style="width: 23pt;" width="30"> <col style="width: 77pt;" width="102"> <col style="width: 131pt;" width="174"> <col style="width: 98pt;" width="130"> <col style="width: 80pt;" width="106"> <col style="width: 72pt;" width="96"> <col style="width: 129pt;" width="172"> <col style="width: 110pt;" width="146"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; width: 23pt;" width="30" height="20">
</td> <td class="xl70" style="width: 77pt;" width="102">A</td> <td class="xl70" style="width: 131pt;" width="174">B</td> <td class="xl70" style="width: 98pt;" width="130">C</td> <td class="xl70" style="width: 80pt;" width="106">D</td> <td class="xl70" style="width: 72pt;" width="96">E</td> <td class="xl70" style="width: 129pt;" width="172">F</td> <td class="xl70" style="width: 110pt;" width="146">G</td> </tr> <tr style="height: 18.75pt;" height="25"> <td class="xl69" style="height: 18.75pt;" height="25">
</td> <td class="xl67">COMPANY</td> <td class="xl67">EMAIL</td> <td class="xl67">OFFICE PHONE</td> <td class="xl67">CELL PHONE</td> <td class="xl67">FAX</td> <td class="xl67">STREET ADDRESS</td> <td class="xl67">CITY, STATE, ZIP</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt;" height="20">1</td> <td class="xl65">IS 1 Contact
</td> <td class="xl68">IS1 email
</td> <td class="xl66">(804) 749-3737</td> <td class="xl66">(804) 514-7893</td> <td class="xl66">(804) 749-3765</td> <td class="xl65">11214 Howards Mill Road</td> <td class="xl65">Glen Allen, VA 23059</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt;" height="20">2</td> <td class="xl65">IS 2 Contact</td> <td class="xl65">IS 2 Email</td> <td class="xl65">IS 2 Office Phone</td> <td class="xl65">IS 2 Cell</td> <td class="xl65">IS 2 Fax</td> <td class="xl65">IS 2 Street Address</td> <td class="xl65">IS 2 City, State, Zip</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt;" height="20">3</td> <td class="xl65">IS 3 Contact</td> <td class="xl65">IS 3 Email</td> <td class="xl65">IS 3 Office Phone</td> <td class="xl65">IS 3 Cell</td> <td class="xl65">IS 3 Fax</td> <td class="xl65">IS 3 Street Address</td> <td class="xl65">IS 3 City, State, Zip</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt;" height="20">4</td> <td class="xl65">IS 4 Contact</td> <td class="xl65">IS 4 Email</td> <td class="xl65">IS 4 Office Phone</td> <td class="xl65">IS 4 Cell</td> <td class="xl65">IS 4 Fax</td> <td class="xl65">IS 4 Street Address</td> <td class="xl65">IS 4 City, State, Zip</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt;" height="20">5</td> <td class="xl65">IS 5 Contact</td> <td class="xl65">IS 5 Email</td> <td class="xl65">IS 5 Office Phone</td> <td class="xl65">IS 5 Cell</td> <td class="xl65">IS 5 Fax</td> <td class="xl65">IS 5 Street Address</td> <td class="xl65">IS 5 City, State, Zip</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt;" height="20">6</td> <td class="xl65">IS 6 Contact</td> <td class="xl65">IS 6 Email</td> <td class="xl65">IS 6 Office Phone</td> <td class="xl65">IS 6 Cell</td> <td class="xl65">IS 6 Fax</td> <td class="xl65">IS 6 Street Address</td> <td class="xl65">IS 6 City, State, Zip</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt;" height="20">7</td> <td class="xl65">IS 7 Contact</td> <td class="xl65">IS 7 Email</td> <td class="xl65">IS 7 Office Phone</td> <td class="xl65">IS 7 Cell</td> <td class="xl65">IS 7 Fax</td> <td class="xl65">IS 7 Street Address</td> <td class="xl65">IS 7 City, State, Zip</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt;" height="20">8</td> <td class="xl65">IS 8 Contact</td> <td class="xl65">IS 8 Email</td> <td class="xl65">IS 8 Office Phone</td> <td class="xl65">IS 8 Cell</td> <td class="xl65">IS 8 Fax</td> <td class="xl65">IS 8 Street Address</td> <td class="xl65">IS 8 City, State, Zip</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt;" height="20">9</td> <td class="xl65">IS 9 Contact</td> <td class="xl65">IS 9 Email</td> <td class="xl65">IS 9 Office Phone</td> <td class="xl65">IS 9 Cell</td> <td class="xl65">IS 9 Fax</td> <td class="xl65">IS 9 Street Address</td> <td class="xl65">IS 9 City, State, Zip</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt;" height="20">10</td> <td class="xl65">IS 10 Contact</td> <td class="xl65">IS 10 Email</td> <td class="xl65">IS 10 Office Phone</td> <td class="xl65">IS 10 Cell</td> <td class="xl65">IS 10 Fax</td> <td class="xl65">IS 10 Street Address</td> <td class="xl65">IS 10 City, State, Zip</td> </tr> </tbody></table>

I have PO B7 set as a data validation, so that only things in SITE SUMMARY column A are options.

If I want it so the other information matches and fills in...for example, I want B8 to equal the email from site summary for the appropriate company.

I tried this - no luck.

=INDEX('[SITE SUMMARY.xlsx]INSTALLERS'!B:B,MATCH(B7,'[SITE SUMMARY.xlsx]INSTALLERS'!$B:$B,0))

Any help would be appreciated.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
For email
=VLOOKUP($B$7,'SITE SUMMARY'!$A$1:$G$11,2,FALSE)

then change column 2 as necessary for the rest
 
Upvote 0
Your original formula is the most robust approach and simply needs a little tweak to work properly:

=INDEX('[SITE SUMMARY.xlsx]INSTALLERS'!B:B, MATCH(B7, '[SITE SUMMARY.xlsx]INSTALLERS'!$A:$A, 0))

You're matching the value you place in B7 to column A.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
537
smozgur
smozgur
E
  • Question Question
VBA code clean up
Replies
9
Views
482
mumps
mumps
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
902
epactheactor
E
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C
M
  • Question Question
Help with FilterXML formula
Replies
0
Views
1K
montex01
M

Forum statistics

Threads
1,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top