SOLVED

Collect certain cell infos + name of sheet into a mastersheet

Copper Contributor

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

 

4 Replies

@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. 

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
best response confirmed by troyyy (Copper Contributor)
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.

 

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
1 best response

Accepted Solutions
best response confirmed by troyyy (Copper Contributor)
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.

 

View solution in original post