I have two templates with links to each other and need help

John Stark

I have created two sheets for our company travel planning, one is a budget planning sheet, and one is a trip strategy sheet.  There are hyperlinks to jump between the sheets, and cell links to transfer data from sheet to sheet.  When I make the daughter sheets, I would link the links to automatically update, right now I have to manually update the links when I create the pair of sheets for our travellers.  What do I do?

Tim Meeks

John,

 

I"m a little confused on your exact scenario,  but I don't think there is a way to automatically update links. you will have to update the links manually. I'd almost have to see exactly what you are doing.

 

Tim

John Stark

What is the best way to let you see what I am doing?  I would be glad to share the templates:  Until we can do that, here is a bit more detail:

Each trip is tracked on two sheets: i.e. "Trip to Africa Budget" would be one sheet, (with all the projected costs) and "Trip to Africa Planning" would be a second sheet with details such as itinarary, key contacts to see, purpose for each meeting, etc.  I built the sheets with example data to be sure everything worked, then stripped the data and saved them as templates.  the Budget sheet has a cell with a hyperlink to the planning sheet, and vice versa.  The # of travel days (generated by the planning sheet travel dates) is copied by cell linking from the planning sheet into the budget sheet for items such as hotel cost, meal allowances, etc.  Budget totals (proposed and post-trip actual) are copied by cell linking into the planning sheet so the supervisor can have a budget overview without jumping to the budget sheet.  When I create a new set of trip sheets from the template, the links continue to point to the template cell, not the newly created planning sheet or budget sheet as they need to.  

 

Thanks for giving this some thought and solution time.

Tim Meeks

John,

 

Thanks for the extra detail. That makes sense now.  So in the newly created sheet both the hyperlink and the cell linking point back to the template?  I haven't heard of a feature or solution to point to a newly created sheet. Maybe an employee of SS can advise.

 

If I may step back and suggest another solution.  Have you thought about combining ALL of the data from the two sheets on ONE sheet and then using a Report (if necessary) to filter the view for Planning and Budget respectively? 

A "report" in SS is really a filter of the sheet and if a report and sheet user have the same permission level, they can update the report and have it update the sheet.

 

if you want to share with me, my email is tim (dot) meeks (AT) gmail (dot) com 

 

I went to Joburg, SA in '03....Keep up the good work!

 

Tim

John Stark

I'll set up sharing with my example sheets, filled w/ faux data.   I did not know how to get the two different types of information into one smartsheet, although I could do so in Excel in a couple of different ways.  

 

I really appreciate you willingness to help.  I'll set the share as soon as I send this.