Pulling Data from multiple Sheets into master worksheet

[sdgg91]

I tried searching in the forum and didn't find any results tending to what I need done in my workbook. I have a workbook with 70 sheets in it, all sheets are the same template so it will make some part of this easier.

What I need to do is pull data from each individual sheet in Cell !AF167. However each sheet is named something different. On my master worksheet which will summarize everything I have had to manually type in the name of the tab and then I use !AF167. So for example ='Tab1'!AF167 will give me the correct value. I don't however want to do this 70 additional times so is there a formula that I can use that looks at the tab name and then just merges the two together to give me the result desired? Each value from the different sheets will have there own cell, it is not a sum of all of these cells like a consolidate method.


Thanks for your help in advance!

[JBeaucaire]

1) On the master, but this in B2:

Tab1

2) In C2, put this indirect formula:

=INDIRECT("'" & B2 & "'!AF167")

That formula will construct the reference indirectly, using the text in cell C2 and the text in quotes within the INDIRECT() function. You string pieces of text together using the & operator.

3) Add other entried in column B, copy C2 down as needed.

[oeldere]

@sdgg91

Why not all data in one sheet (e.g. with an macro).

After that you can use pivot table to analize the data.

[sdgg91]

@sdgg91

Why not all data in one sheet (e.g. with an macro).

After that you can use pivot table to analize the data.

The spreadsheet is already huge, a macro would take a long time and I don't want all of the lines from each tab onto one sheet. Keeping it simple this time!

[sdgg91]

1) On the master, but this in B2:

Tab1

2) In C2, put this indirect formula:

=INDIRECT("'" & B2 & "'!AF167")

That formula will construct the reference indirectly, using the text in cell C2 and the text in quotes within the INDIRECT() function. You string pieces of text together using the & operator.

3) Add other entried in column B, copy C2 down as needed.

This worked brilliantly, just what I was looking for!

[JBeaucaire]

I've marked this thread as SOLVED for you.
Next time, select Thread Tools from the links above to mark a thread as SOLVED. Thanks.