Dynamic Formula to links multiple sheet based on Sheet Name

Ibrahim Khaleel

Dear Team,

I have multiple folders (Individual Project) in workspace which has multiple sheets in each project folder. Is there any dynamic way or formula to link the individual sheets data (example Total Budget and Actual Cost) based on the Sheet name (Main Sheet contains a Columns Projects Name which has each Project Sheet Name) to Main Sheet and for every New Project Formula should updated whenever user enter the Project Sheet Name.

Appreciate your prompt support.

Thanks and best regards.

Paul Newcome

It sounds like you have a good use case for the Control Center add-on.

Zrob

Sorry to jump on an old post, but I've got the same need to pull data from many child sheets with names that need to be dynamic (not hard coded into formulas).

This answer pointing to an add on feature doesn't help I'm afraid. If we pay for the upgrade to use Control Center, how would that solve the problem? Does Control Center give access to new formula functions or something?

If there is a solution we may likely buy the upgrade. We really need to make cover sheets which roll up summary info from a variable set of child sheets in an automatic way. Hard coding every child sheet name into gargantuan formulas is unworkable.

Thanks!

Paul Newcome

@Zrob Control Center allows you to create a template set of sheets, reports, dashboards, etc. and then roll that template out over and over again. Similar to "Save as New" on a folder but with some added enhancements.

Control Center will rename everything for you.

Control Center will allow for "Global Updates" so if there is a need to make a change to the template, most changes can be automatically pushed out to all projects provisioned from that template without you having to manually adjust every single one.

Control Center also has a Dynamic Scope Report feature available so that you can automatically have a set sheet or sheets from each project added to the report as the projects are provisioned.

And Control center will automatically list every project out in a sheet so that each project has its own row and will automatically create cell links to bring project level data back into a "Summary Sheet".

If you still don't think that will work for you, can you provide more details as to exactly what you are looking for and why you are wanting to do it that particular way?