Automatically pull specific data from current or new worksheet tabs into new tab

Question

I am trying to create a workbook that automatically loops through the tabs to pull specific information into separate or 'Summary' tab. The idea is for it to create a visual of the current or any new data that is added. My initial thought was to create a macro, but I have not been successful with importing the data into the appropriate cells. If you reference the image with 'Summary' tab, it provides an overview of how the data should be listed from each 'Assignment' tab. Presently, the workbook has 3 tabs with the expectation more assignments will be added.

Note, the 'Assignment' tab uses the same template for each new assignment, so the data needed would be place in the same cells for each tab (cells B1, B2, & F1 - see Summary tab below for cell reference)

I would appreciate any suggestions on the best method to set up this automation.

Summary tab

Cell B3 (ID Name) ='Assignment 1'!B1

Cell B4 (Start Date) ='Assignment 1'!B2

Cell B5 (Completion Status) ='Assignment 1'!F1

Answer 1

Hi,

In range B2:D2 of the Summary tab, type the worksheet names (no extra spaces and/or spelling errors).

In cell B3, enter this formula

=indirect("'"&B$2&"'!B1)

In cell B4, enter this formula

=indirect("'"&B$2&"'!B2)

In cell B5, enter this formula

=indirect("'"&B$2&"'!F1)

Drag B3:B5 to the right.

Hope this helps.

Answer 2

Ashish,

Thank you for the feedback. After correcting the indexing to pull the correct sheet names, the indirect formula worked as expected.

Thank you!

Answer 3

You are welcome. If my reply helped, please mark it as Answer.