Jul 25 2022 05:30 PM
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
Jul 25 2022 10:37 PM
@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.
Jul 26 2022 05:36 AM
Jul 26 2022 05:57 AM
Solution@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.
Jul 27 2022 12:26 PM
Jul 26 2022 05:57 AM
Solution@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.