0

I'm creating a project management worksheet to generate automated reports on various projects the charity I work for is running.

Currently I have a sheet per project, and a data collation sheet which pulls out all the fields from across the various sheets into one place for making graphs etc. Every time a new project is created I need to go into the data collation sheet and Find + Replace a row of placeholder formulas with the name of the new sheet.

I was wondering if there is a solution (with code most likely?) that could automatically update these formulas when a new sheet is created, and insert the title of the new sheet into them.

I saw this question, which is similar (Is it possible to automatically add cells from a new sheet to a formula in Excel?) But I don't think the solution here will work for me as I am not only dealing with numerical fields. E.g. one field is Funder Type which could be corporate, government, etc. I know these could be classified numerically but I would rather not in the interest of keeping the project sheet interface intuitive for those that will be completing them.

Thanks!

BSiggery
  • 1
  • 1
  • I believe excel still has the consolidation feature. Basically, if all of your projects sheets are formatted the same you can use the consolidate feature to roll it up to your data collection sheet. Since it's been 10+ years since I used the feature I can't tell you how you add additional sheets to your data collection sheet, but I think it's easier than the find/replace you are currently using. – gns100 Dec 04 '20 at 21:54
  • Data consolidation is interesting. I didn't even know of its existence. Its nice to quickly create a summary of all sheets using a wizard-like interface. Its the granddaddy of 3D references :P – ExcelEverything Dec 05 '20 at 01:39

1 Answers1

0

If you run Office 2013 or newer, try 3D references. Keep the order of your projects sheets like this:

Summary / Proj1 / Proj2 / ProjNew / ProjLast / Template

Now

=SUM(Proj1:ProjLast!A1)

will sum across all project sheets. Just make sure your new project sheet stays sandwiched between Proj1 and ProjLast. If all the values in Template are zero (and you only SUM and not AVERAGE), you could of course =SUM(Proj1:Template!A1) and reduce the risk of skipping a new sheet.

ExcelEverything
  • 3,114
  • 1
  • 4
  • 16