Pull data from Multiple sheets into single sheet and still allow to put formulas

Hi,

I have different sheet for projects but I want some key specifics in a single sheet that could allow me to use this data later to present the billing and other related aspects of the work.

Here's how it works:

Project plan sheet: This contains the project plan, start dates, end dates and the %complete. For an instance there are 9 such projects in place.
Invoicing sheet: This is where the data from these sheets is pulled into and I index-match formulas linked with other sheets such as rates, etc.
Master collation sheet: This is a sheet where I link summary from each project that is linked with the invoicing sheet via index-match. Here is the problem, every Time I have a new project I need to go into this sheet and then manually link the summary from the other projects which is laborious and phrone to errors.
Expense sheet: This is a sheet wherein I put the expenses and then I have formulas that link up with other sheets to give me an idea of whats the expense on each project.

I would like to know if there is a solution or a was I could code into smartsheets to just look up for the sheet name and pull in the data. Like I could just set up a column that has the exact sheet name or something and the remaining columns would contain the formulas to look up that sheet and find the exact references and add those details.

I would highly appreciate if anyone could share their experience of setting or linking too many sheets to a sheet for data collation.

Thank you

Find more posts tagged with

Formulas

Accepted answers

All comments

Andrée Starå

Hi @Shadaab

I hope you're well and safe!

Here's a possible workaround or workarounds

The premium app, Control Center can automate it all.
Maybe simplify it if possible, so adding additional projects with cell linking is simpler.

Would any of those options work/help?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Shadaab

Hi Andrée,

Thanks for the response and the suggestion to use cell linking. As of now since the count of projects in the smartsheet is less I'll be manually linking those in a separate spreadsheet but maybe in the future, I would like to try out the control center.

Appreciate your prompt response, thanks! :-)

Shadaab

Andrée Starå

@Shadaab

Excellent!

You're more than welcome!

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.