Summarise hundreds to sheets on one sheet
Need someone help me automatically populate fields from hundreds of sheets (tabs) into a single summary sheet.
We replicate the Master sheet each time, rename the sheet to the latest number and then fill in the details. We need all those details to flow through to the 'Actual vs. Forecast' sheet.
Any questions please let me know.
Copy-of-Costing-Form.xlsx
We replicate the Master sheet each time, rename the sheet to the latest number and then fill in the details. We need all those details to flow through to the 'Actual vs. Forecast' sheet.
Any questions please let me know.
Copy-of-Costing-Form.xlsx
John Tsioumpris
Well this is not a solution ...but more a recommendation...i think its time to switch to a database like Access...essentially you are running a "database" with numerous records on a spreadsheet...its time for upgrade.
basically you have the sheet... All you need to do is replace the sheet name (which you have hardcoded now) by the value in cell a.
that is very easy..
just create a formula in D2 (and next columns) using: =indirect(a2 & "!$e$91")
if you than turn your Actual vs forecast sheet into a table, escel with autopopulate the formula's when you add a new name.
So once done, the only thing you need to do is type in the number in column A (and make sure the sheet exists).
that is very easy..
just create a formula in D2 (and next columns) using: =indirect(a2 & "!$e$91")
if you than turn your Actual vs forecast sheet into a table, escel with autopopulate the formula's when you add a new name.
So once done, the only thing you need to do is type in the number in column A (and make sure the sheet exists).
ASKER
Hi Koen, is there any chance you are able to input the formula into the spreadsheet please as I am not following? Thanks
If you have't already applied the suggestion by Koen, please find attached an updated spreadsheet. The bottom two rows have the INDIRECT() formulas applied. For the next contract number, just "fill down" or copy/paste the previous line. The two "divide by 0" errors have have also been caught and will now return "0" to be visually cleaner.
EE-29096140-Costing-Form.xlsx
EE-29096140-Costing-Form.xlsx
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.