Question about Templates and Formula References

Murz

Hi,

I am creating a template of a "Cost Estimator" which will be created into a new sheet each time I have a new project. The Cost Estimator sheet will link to a master Project List using the Project ID and the Project Name. What I am attempting to do is create a Cost Estimator "Totals" sheet which will have a row linked to each of the cost estimator sheets by the Project ID and Project Name and then formulas using Index Match to each of the Cost Estimator sheets "Totals" cell for each of the cost categories. This is easy to do with the first sheet, but then its quite a bit of work to change the references in all the totals columns in the "Totals" sheet to match each new Cost Estimator sheet.

My primary outcome is to be able to report on project totals using a generated PDF for each project or some kind of report/graphs from the "Totals" sheet since I don't know if I can generate a PDF from the Cost Estimator sheet.

To hopefully make this easier, I am attaching photos (with all dummy data) of the Cost Estimator sheet and the Cost Estimator Final sheets.

The Cost Estimator Final sheet has Project ID and Project linked via cell link to the Cost Estimator sheet Project ID and Project. The "Totals" columns are index match using the fixed "totals" line on the Cost Estimator sheet using variations of this formula (based on the fixed verbiage for each line)

=INDEX({Estimated Costs}, MATCH("TOTAL GENERAL REQUIREMENTS", {Category}, 0))

The references are from the Cost Estimator sheet.

I have tried to build my formulas from the template in the hopes that the references would carry through but templates are not available when I look for it as my reference sheet.

Or maybe I'm going about this all wrong….maybe there's an easier way to do this?

Hopefully someone can help, I'm in a tizzy. lol

Thank you in advance, Smartsheet friends!

Lisa

Murz

Thank you! I will leave as a sheet instead of a template. Thank you so much!!

Andrée Starå

https://community.smartsheet.com/discussion/139710/question-about-templates-and-formula-references

Hi,

I hope you're well and safe!

If it's a Template type of asset, then it's not possible to find and connect them; I recommend using an ordinary sheet instead as a template.

Make sense?

Would that work/help?

I hope that helps!

Be safe, and have a fantastic day!

Best,

Andrée Starå | Smartsheet Expert Consultant & Partner / CEO @ WORK BOLD

✅ Did I help answer your question/solve the problem? Please support with💡 ⬆️ ❤️, and/or ✅ Answer. This will make it easier for others to find a solution or help answer! I appreciate it, thank you! 🙏

Murz

Thank you! I will leave as a sheet instead of a template. Thank you so much!!

Andrée Starå

https://community.smartsheet.com/discussion/comment/485504#Comment_485504

Excellent!

Happy to help!

✅ Remember! Did I help answer your question/solve the problem? Please support with💡 ⬆️ ❤️, and/or ✅ Answer. This will make it easier for others to find a solution or help answer! I appreciate it, thank you! 🙏