Forum Discussion
troyyy
Jul 26, 2022Copper Contributor
Collect certain cell infos + name of sheet into a mastersheet
Hello,
I have a very big excel sheet with 50+ tabs/sheets all labeled a certain way.
I collected searchvolume of keywords and other informations like difficulty score.
I'm able to get the total searchvolume and other informations on each sheet in row $B$400-$D$400.
I want to have a mastersheet where on each row the following is listed:
Column A: Name of sheet
Column B: Data from B400
Column C: Data from C400
Column D Data from D400
as a shortcut
For example
Name | Average Difficulty Score | Average Attractiveness Score | Total Searchvolume |
Black screen music | 88 | 33 | 2368681 |
Soothing | 85 | 37 | 601769 |
Gaming Music | 89 | 32 | 2788108 |
Look at the first sheet called "Mastersheet" in the attached excel file.
How to make a formula / VBA which works that the results are like shown in the file or above?
Thanks guys
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.
4 Replies
Sort By
- Riny_van_EekelenPlatinum Contributor
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.
- troyyyCopper ContributorHey Riny van Eekelen,
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- Riny_van_EekelenPlatinum Contributor
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.