Cross Referencing Data Across Sheets

AFader

I've read several threads on this and tried to figure out solutions but am still stumped. I am creating a workspace for managing events. I have vendor lists with their rates and a planning sheet with the number of staff we need and which activities will be booked, etc. I'm trying to create an expense sheet where I can have a drop down of all our vendors and it would pull in their rate. Or if I put in the event name it will tell me how many staff worked at the event and which activities we book, based on the overview sheet.

Are either of these things possible or am I expecting too much?

PS. I have tried putting our vendor list into google docs and then using data shuffle to refer to it, but I can't make that work so if that is the solution please point me to something that would help me figure out how to do that better.

PPS. I'd love any ideas on automating a budget from existing data in sheets. I can make a report of my income and one of my expenses, but since you can't really add data from different sources into a report or add custom columns I don't see a way to automate a budget from existing data. Would love if anyone has a solution for this.

Thanks

Paul Newcome

To get started, you could use an INDEX/MATCH with cross sheet references to pull in your rates based on vendor selection.

=IFERROR(INDEX({Vendor Sheet Rate Column}, MATCH([Vendor Column]@row, {Vendor Sheet Vendor Column}, 0)), "")

Paul Newcome

To get started, you could use an INDEX/MATCH with cross sheet references to pull in your rates based on vendor selection.

=IFERROR(INDEX({Vendor Sheet Rate Column}, MATCH([Vendor Column]@row, {Vendor Sheet Vendor Column}, 0)), "")

AFader

https://community.smartsheet.com/discussion/comment/413667#Comment_413667

Thank you for this reply. I took me a few tries, but I did figure out how to do this! Now if only we could get a way to create a drop down based on values from another sheet, which I've seen dozens of people ask for.

Paul Newcome

Feel free to browse the Product Ideas tab at the top of this page. I believe there is already an idea submitted for the dynamic dropdowns that you can add your vote to. In the meantime, you can leverage the premium app Data Shuttle, the premium app Bridge, or the API.

Can you also provide some screenshots for context on how you would want the other part(s) to work?

"Or if I put in the event name it will tell me how many staff worked at the event and which activities we book, based on the overview sheet."

AFader

https://community.smartsheet.com/discussion/comment/413919#Comment_413919

I do have data shuffle, but could you point me to directions on how to make it work for this purpose. I wasn't able to figure that out either.

Other than that, I think with your previous help, I'm getting pretty close!

Paul Newcome

Here is the official help article.

The basic idea is that you use whatever method you need to create the filtered list, use an offload workflow to export the filtered list, then an upload workflow to update the dropdown options.