Forum Discussion

troyyy's avatar
troyyy
Copper Contributor
Jul 26, 2022
Solved

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

NameAverage Difficulty ScoreAverage Attractiveness ScoreTotal Searchvolume
Black screen music88332368681
Soothing8537601769
Gaming Music89322788108

 

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

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum 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. 

    • troyyy's avatar
      troyyy
      Copper Contributor
      Hey 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_Eekelen's avatar
        Riny_van_Eekelen
        Platinum 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.

         

Resources