How can I use VLOOKUP to find data from multiple sheets and combine it in another sheet?

MJ0013

I have an existing database of suppliers/vendors set up in a sheet, and I'd like to automatically show if they're currently working on a project and which project they're currently working on, as well as the dates they're working on the project within that sheet. I'm creating a supplier sheet per project which shows all this information, so a straightforward VLOOKUP works just fine, but a problem arises when a single supplier is working on multiple projects (i.e. I have to reference info from multiple sheets). What is the best way to go about automatically showing this information in the database sheet per supplier?

Any help/suggestions would be very much appreciated!

Thanks!

Paul Newcome

Have you looked into creating a report?

Paul Newcome

Have you looked into creating a report?

MJ0013

Hi! Yes, so I tried creating a report to summarise the data in each of the project sheets. It is useful in itself because it gives an overview of the projects, but then I can't use VLOOKUP to get information from the report to be pulled into the database of suppliers. I've managed to find a way to combine the current project names from each of the project sheets into another sheet using JOIN(COLLECT...) but I need to be able to see the combined project names on a per-supplier basis, not for the sheets as wholes. I'm sure I'm just missing something silly or unaware of a function I can use for this purpose.

Paul Newcome

Are you able to provide some screenshots for reference? It would be helpful if we could visualize both your current data structure as well as a manually entered version of what you want to automate.

MJ0013

I've put together an example. I'll be setting up project info sheets that list info about the suppliers working on each project. There'll be one sheet per project - see the two screenshots below as simplified examples.

I want the information on the status of each supplier (and later the role and dates too) to be listed in our primary supplier database (see example below).

The problem is that a supplier is able to work on multiple projects. In the example above, Supplier 1 and 2 work on both projects.

Is there a way to automatically show the status, role, and dates applicable to each supplier as a summary of all their projects in the Suppliers sheet? I know I can do this via a report, which is great, but I'd like the information to be part of our supplier database instead of a separate sheet which has to be checked.

Paul Newcome

The challenge is when you have a supplier working on multiple projects and there are different dates/statues/etc..

In your above screenshots, would we pull the Status for Supplier 2 from the first project or the second?

MJ0013

Ideally, we'd want to pull from both and combine/concatenate. I've managed to do something similar in Google Sheets, and it does work there, but I'm unsure if Smartsheet has the equivalent capability.

Paul Newcome

Yes. You can do that with

=JOIN(COLLECT(....................), ", ")

MJ0013

Thanks, Paul. I've managed to use the =JOIN(COLLECT(.........), ",") to combine info from two cells, but not for a specific supplier, just in general. I'm looking to replicate both the 'query' part in the Google Sheets screenshot as well as the concatenation if that makes sense.

Paul Newcome

You would use the same logic as you have used before with the JOIN/COLLECT.

=JOIN(COLLECT({Source Sheet Project Column}, {Source Sheet Supplier Column}, @cell = Supplier@row), ", ")

MJ0013

Thanks so much for your help, this solution partially solved my problem. In the end, I decided to create separate sheets and then created a report based on those sheets instead of integrating this into the database, as it was the easier solution that wouldn't require constant updating when items changed or were updated.