Microsoft Excel - Collect Data from different sheets based on specific cell

Asked By Subash D on 31-Jul-18 04:37 AM
Hi Team,

I have an Excel workbook with multiple sheets (Sheet Names - 1 - 150) with same formats and data.  (Eg. B3 in all sheets contains Names, D3 in all sheets contains Date of Birth, B8 in all sheets contain Date of Joining, etc, etc.

I also have a summary sheet with Column A containing the sheet names.

I need to pull specific data from each sheet:

Example, in summary sheet, in B1 , I need the name in Sheet 1, in C1, I need DOB in Sheet 1, etc .
I am currently using this formula.
='1'!$B$3
='1'!$D$3
='1'!$B$8

But I need to change the sheet name in all the rows.

Is there a formula which can pull data from different sheets based on the sheet name in summary page.

Subash D
STEPHEN DRULEY replied to Subash D on 09-Sep-18 06:32 AM
=SUM(Sheet1:Sheet24!A2)

For optimum results lets say that sheet one is for Jan17 and you want to construct sheets for 24 months out. 
1) Construct sheet tabs for each month, {Jan17, Feb17, Mar17 … Sep18, Oct 18, Nov18, Dec18}
2) Select the Jan17 sheet then hold down the shift key and select the Dec18 tab.
3) Now all sheet tabs are grouped, so when you type a heading on Jan17, for example, or any non-data entry it is copied to all other sheets so everything is lined up for reporting purposes.
4) Select the Jan17 sheet again and the grouped sheets mode is turned off.
5) Enter data individually for each sheet.
6) If your sheets are not identical and the data is in different places on each sheet, you are in for trouble... redo it
7) Even though the Mar17 sheet tab, for example, is named as such, it still can be referred to as Sheet3
8) Put your summary sheet at the end (Sheet25) where the above formula can be used.