• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Most efficient way to link information by ID between two worksheets

D

dgavilanes

Member
Hi All,


I have two worksheets, worksheet# 1 is the data source about 6000 rows of data,it has a location ID and Unit ID(primary key) worksheet# 2 is a summary report with 56 rows of data and each row has a location ID.(row data to be populated on the correct row in the summary report)


What would be the best and accurate way of linking this information, the idea is when the Unit ID, School name and the matching location Id is selected on worksheet #2 with a dropdown box; the entire row of data from worksheet #1 is place on worksheet #2.


Can a drop down box have more than one field list? but only one field is populated on the summary report?


Example-Dropdown box:LocationID,UnitID,Schoolname but only School name is populated on the report


I attached a link to a sample file that contain 2 records.


I try using a pivot report, however I was not able to add more than on field to a single dropdown box(the requirement is only one filter)


http://sdrv.ms/OELKW3


Hope it makes sense


Dennis
 
Hi dgavilanes,


Please find the file here:


https://hotfile.com/dl/165393489/b1b2a37/link_information_by_ID_dgavilanes.xlsx.xlsx.html


At A2, B2 and C2 of Sheet2, we have drop downs for Location ID, Unit and School Name, respectively.Change the drop down to update the row from D2:N2.


PS:I have placed a named range ("DynamicRange") in table array of vlookup formula.As and when you add or delete the data in sheet2 (starting from B2), the named range will automatically expand or shrink, accordingly and hence the formula.Press ctrl+F3, select the name range to examine the formula at refer to box..


Please let us know if we are able to help you..


Regards,

Kaushik
 
You must log in or register to reply here.
Back
Top