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:
- 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.
- To the right, I have a column called "Rate" with a drop-down menu of 3 rate options: "Low Rate", "Mid Rate", "High Rate".
- 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:
- If "rate column cell" is equal to "x rate" inside the "budget sheet"
- 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!
