How to Setup Formulas to Automatically Link & Pull Data from Other Sheets

Dereck H

I'd like to try and figure out how to most effectively and efficiently connect 2 separate smartsheets together, preferably through some sort of smart formula/code. I don't think doing a manual "cell linking" from a cell in one sheet to the next would be very smart or efficient for what I'm trying to do here.

I have both a "Budget" sheet and "Average Rates" sheet. "Budget" stores a template of the budget line items. "Average Rates" is essentially a database of hundreds of quotes I've done in the past that averages out the "low, mid, high" cost for each of the line items. Below is a hypothetical example of what I'm trying to do:

1. On the "Budget" sheet I have an expense item called "Camera Gear". I have some pictures attached to help show what the budget sheet looks like visually.
2. To the right, I have a column called "Rate" with a drop-down menu of 3 rate options: "Low Rate", "Mid Rate", "High Rate".
3. When I select a rate from the dropdown menu, I'd then want smartsheet to pull data from a cell in the "Average Rates" sheet where the rate data is stored. So if I were to select "Mid Rate" for the "Camera Gear" line item, I'd want smartsheet to go to the "Average Rates" sheet and pull data from a cell that stores the mid rate for camera gear.

Why not just store this quote data inside the "Budget" template? Why have it in a separate "Average Rates" sheet? Because I'll eventually have thousands of quotes stored in there and I don't want to clutter/slow down the performance of my normal budget sheet. I want to make the budget simple and easy for the client to review without having to manually delete/hide past quote line items before presenting the budget to them. 

So I guess the logic of this function would be some like this:

1. If "rate column cell" is equal to "x rate" inside the "budget sheet"
2. Go to "average rates sheet" and pull data from "x cell"

I know this might be a tricky one to accomplish, any guidance or ideas is much appreciated, thanks!

 

L_123

The community has been vocal for years about being able to use formulas to grab information from other sheets and nothing has come from it.

https://community.smartsheet.com/discussion/formula-reference-cell-another-sheet

https://community.smartsheet.com/discussion/if-formula-pulls-data-one-cell-different-sheet

https://community.smartsheet.com/discussion/reference-cell-another-sheet-formula

https://community.smartsheet.com/discussion/can-sumif-formula-be-used-pull-info-another-sheet

https://community.smartsheet.com/discussion/request-allow-cell-references-different-sheets

https://community.smartsheet.com/discussion/referencing-external-sheet-data

https://community.smartsheet.com/discussion/formulas-looking-data-other-sheets

 

And that was just a simple google search and 5 min making sure the sources were what I wanted. I'm sure it is just the tip of the iceburg. There are other sources that offer this service specifically for smartsheet, they just cost a lot of money.

Mike Wilday

Please submit a Product Enhancement Request to Smartsheet by clicking on the link in the Quick Links section of the Community page. Smartsheets is listening and those several posts indicated by Luke have not gone unheard. Keep asking!