Forum Discussion
Collect certain cell infos + name of sheet into a mastersheet
troyyy See attached.
I used Power Query to obtain all the sheet names from your file and copied them back into the Mastersheet. Then I entered the formulas as described earlier. It took about a minute to create this. I prefer PQ over VBA as it's more flexible and much easier. But that's a personal opinion.
troyyy In D2, you could use a formula with INDIRECT like this:
=INDIRECT("'"&A2&"'!D400")It build a cell reference in text, using the sheet name in A2 that INDIRECT will "transform" into a real reference. This one you can drag down assuming that all the sheet names in A are spelled correctly. Create similar formulas in B2 and C2.
Having said that, why not collect all the data in one large table, rather than 200 separate ones. Add a column for the genre and you can summarise the data in a matter of seconds with a pivot table, for instance. No formulas needed.
that seems like doesn't work for me.
Or can you demonstrate it on the sheet?
My challenge is to collect the sheet name and then add from the individual sheet cells B400, C400 and D400.
When I add the formula to the sheet nothing happens.
I want to do this in bulk. And I feel like I need a macro to do this?!
Cheers
Troy
troyyy See attached.
I used Power Query to obtain all the sheet names from your file and copied them back into the Mastersheet. Then I entered the formulas as described earlier. It took about a minute to create this. I prefer PQ over VBA as it's more flexible and much easier. But that's a personal opinion.
- Thanks Riny van Eenkelen, that helps a lot.
I understand that Power Query seems to be the better approach over VBA.
You really helped a lot.
it's much apprechiated.
Cheers
Troy