Linking One Workbook with Multiple Tabs to Master Workbook Formula

**Scholzie220** · 01-26-2015, 04:31 PM

Hi all,

I am trying to create a formula that will do the following:

I have a Master Job Cost Summary Workbook that contains one worksheet will all jobs on it and info relating to my KPI's. It includes multiple rows and each row represents a different job.

I also have another Job Cost Workbook that contains all of the job cost reports with each different tab (say 150 tabs) being a different job. Each job cost report is identical because they are derived from the same template.

What I am trying to do is create a formula on the Master Summary Workbook that will extract data from say cell A1 from Tab 1 of the Job Cost Workbook, but when I drag or copy the formula down through the Master Workbook it pulls from the next tab in the Job Cost Workbook instead of the same one.

It would take quite some time to link every cell I need to the Master Workbook.

Thanks,

Scholzie220

**Brendan_Floyde** · 01-26-2015, 04:50 PM

Assuming Column A has a list of worksheet names in it use this to copy down. This works inside a workbook with lots of sheets to create a summary sheet

Formula:

Please Login or Register  to view this content.

To link to another worksheet use this sort of thing

Formula:

Please Login or Register  to view this content.

**FDibbins** · 01-26-2015, 05:06 PM

Note that INDIRECT() only works on open workbooks.

Also not that by using "'!A1" like that, that will not adjust when copied down.

Perhaps try something like this 0 using the suggestion of having sheet names in column A, instead....
=INDIRECT("'[FileName.xlsm]"&A1&"'!A"&ROW(A1))

It may be easier to help if you provided sample workbooks?