Is it possible to automate a formula across new sheets

RGarrels

In order to feed information into a dashboard, I am importing an excel sheet on a regular basis.

Is there a way to have a formula automatically generate into each newly imported sheet, so that specific information is pulled into a metrics sheet?

The formula would need to pull from the new sheet each time, but from the same row.

Genevieve P.

Hi @RGarrels

What I would do in this instance is import the sheets into the same Workspace. That way you can build a Report off of the Workspace so any newly created sheets are automatically included into the Report.

See: Working with source sheets in reports

Then you'll want to set up a Filter based on how your Sheets are formatting so it only grabs the one row you care about from the newest sheet (e.g. if you have a date column you can filter by if the date is within a specific range.) See: Create Filter Criteria to Control Data in Report Builder

You can add this Report directly on to a Dashboard or use the content in a Chart Widget (depending on your type of data).

Cheers,

Genevieve

RGarrels

Hi Genevieve,

Currently, we have a Metrics sheet calculating totals into the Dashboard (=Countif(Formula).

The Metrics sheet is using an Excel sheet that we import- is there a way to automate the formulas on the Metrics sheet so they work with each new imported excel sheet, WITHOUT having to update the reference every time?

We are trying to minimize the amount of manual steps in a new process.

-Robyn

Genevieve P.

Hi @RGarrels

Imported Excel files create a new Smartsheet Sheet each time. There isn't a way for a formula to recognize a newly created sheet as it's destination for a cross-sheet reference. This means that yes, you would need to update the reference each time a new import has occurred.

An option would be to copy/paste data from the external file instead of importing it in as a new sheet. Alternatively, you could use the add-on Data Shuttle (if your plan has access to it), which would import Excel data within the same Smartsheet Sheet. See: About Data Shuttle

Cheers,

Genevieve