Combining budgets with associated financial reports

Darius Oskoui

Hi all,

To give a little context, I am working for an ONG that funds projects with various operators. We are currently in the process of wanting to switch our budgeting and financial reporting processes from excel to smartsheet, and for that we are facing one major issue.

The issue stems from the fact that we are receiving a huge quantity of budget proposals, but only a select few projects are actually approved. This combined to the fact that smartsheet does not allow tabs within sheets poses a practical problem, which is, how to provide numerous budget templates to applicants, and once approved, how to link the validated budget to a reporting sheet that extract important values to compare budgeted costs vs actuals for example.

As creating cell links between budgets and reports for each approved project would ask for too much bandwidth, would there be an easier way to automate this ?

My first idea was to share a whole folder to the applicants as that would allow for pre-entered links between sheets, but as we need to use sheet models, this does not seem to be an option. Applicants will not have smartsheet accounts, and the only way they will have access to provisional budgets will be through an external link.

We then thought about using index/match formulas, but again this would require too much manual input as references will need to be set for each new project unique reporting sheets.

I hope I am being clear enough, but Copy/pasting the approved budget values to a new budget sheet that has already preset links to a reporting sheet seems to be our only viable option.

I also though of datamesh for this operation, but it seems each new project would require the creation of a unique configuration which would be too time consuming. And also our budgets are not structured by fixed columns.

Would anyone have any other ideas or recommendations to limit manual entries in this process ? To be noted, we have advanced smartsheet subscription, with all applications (workapps, data shuttle, dynamic view, data mesh) and we will also include those sheets within project Workapps down the line.

Thanks a lot,

Paul Newcome

Have you looked into rolling out new projects via Control Center?

Darius Oskoui

@Paul Newcome Thanks, indeed yes we're looking at this right now.

Operators will be filling forms that will trigger the creation of folders. Only obstacle left to solve is how to automate the sharing of these folders to non-licensed users, considering their contact information will be present in the intake sheet of the blueprint.

We're looking at zapier at the moment, not sure it's a great solution but we don't have anyone with enough programming knowledge to code an API call to a cloud to share those.

Paul Newcome

Control Center can automatically share as well if people are listed on the Intake Sheet as long as it is a valid email address in a Contact type column.

Darius Oskoui

@Paul Newcome Had not seen that! life saver thank you so much

*Edit: Seems like the permission option does not show up on my screen can't figure out why just yet

Paul Newcome

It looks like you may not have the correct field selected (Field Name = "undefined"). Make sure that you have the column on the sheet and then make sure it is checked off in the Profile Data Selection step (the step right before this one). This should have your field automatically added to the list when you get to this screen.

As long as that field is set up in your intake sheet as a contact type column, it should pull through.

Darius Oskoui

@Paul Newcome I get this "Not included in any template" message but don't really know what it means as seems like it is (screenshot 2). Field also present in intake list (screenshot 3). Both columns are set as contact type and box has been checked off in the profile data selection step...

Paul Newcome

So it should be there because it is in the intake sheet (screenshot #3) can you provide a screenshot of your Control Center for the Profile Data Selection step?

Darius Oskoui

@Paul Newcome Thanks a lot for the help. Here's the screenshot. I was thinking maybe summary rollup needs to be on for this function to work ?

Darius Oskoui

@Paul Newcome So actually we've found where the issue lied. On the contact column settings from the intake sheet, we needed to restrict the number of contacts to a single one.

One solution bringing another problem, we're now having trouble finding how to automate that the contact in question receives an email with the link of the sheet upon creation of the project folder. This option is available when creating the project manually, but I couldn't find it when provisioning the projects through an automation.

Paul Newcome

@Darius Oskoui I hadn't tried it before with multi-contact turned on for the column. Good to know.

I have my intake sheets set up so that Control Center automatically checks a box on the row when the project has been provisioned. You could then set up an automation to be triggered when this box is checked and have it send to the contact in the cell.