Linking two spreadsheets with text data and from multiple sheets into one

Copper Contributor

I know it is possible to link spreadsheets using a simple = if the cells are to remain in the same place, or to use consolidate if you wish to summarise numerical data, but in my current job role, we use a lot of spreadsheets for storing text data.


I work for a Landlord and we have an annual inspection log which we keep up to date and record details of repair/maintenance issues we noted that require attending to.

We then have a separate spreadsheet for 'Ongoing Repair Works' which duplicates most of the same data, but records only those properties with repairs still outstanding.


I wish to link the two spreadsheets so that we don't have to manually update the Ongoing Repair Works spreadsheet and this will automatically pull the updated comments from the Annual Inspection Log, for the relevant properties. 


I attempted this with a VLookup and added a column to the Annual Inspection Schedule to confirm whether Repairs were outstanding 'Yes/No' to use this as an 'IF' function.


However,  this would leave me with a lot of rows that state N/A if there aren't repairs outstanding, and I would want these to automatically be hidden/to not be included if possible.


Also, the Annual Inspection Schedule spreadsheet is broken down into various tabs for each holding Company, of which there are about 8. For the Ongoing Repair Spreadsheet, we want only one sheet, but therefore wish the Company names to appear as sub-header rows. Is there any way to do this?


I'm thinking it might actually be easier to go the other way around and keep the ongoing repairs updated and then get excel to pull these comments across to the annual inspection log using V-Lookup or similar, I would just need to add a unique identifier I think.


Anyway, long post! but I would love to hear people's thoughts on the best way to present the data.


Due to GDPR I can't attach the actual spreadsheets but have made example test versions to show the current columns/rows/tabs etc.  I can't figure out how to attach them here though!




1 Reply
You could use Data, Get Data, From file, From Workbook for this purpose. You can then make sure the name of the tab is there as a separate column. Also, you can filter the table before it is sent to the worksheet so you can omit anything you don't need. But there is a problem: If you add any comments next to the resulting data, a subsequent refresh of the data removes all comments (unless you make this a tricky to setup, self-referencing query to get the comments back).