How to Pull Data from Tabs in Excel

This tutorial explains the formula for pulling in data from multiple tabs into a single worksheet. This can be a great way to summarize sheets for easy viewing and decrease clutter in a spreadsheet.

[tombates]

Long winded if you have many Step 1 - Formulas >  Define Name > Name = "SheetNames"  >    Refers to =   =REPLACE(GET.WORKBOOK(1), 1, FIND("]", GET.WORKBOOK(1)), "")Step 2 Create an index column (A) 1, 2, 3, 4, 5 ect  however many sheets you haveStep 3 Create sheetNames column (B) =INDEX(SheetNames, A2)   if A2 is your first index number. This will bring every sheet name up when the formula is dragged down.Step 4  Create a SheetPlace column (C), this is where you build your locations  =CONCATENATE("'"&B2&"'!", "$M$4")  B2 is the sheet name and my data is on M4 Step 5 Create a data column  (D) =INDIRECT(C2)Boom! Drag the formula down! Will change automatically if you rename sheets, although will be affected if you change the order of sheets

[iceclimberGD]

Thanks! This was exactly what I needed!

[sktneer]

No need to change the sheet name manually. You can use indirect function instead to change the sheet name automatically when you drag the formula down. :)

[jonathaninglis]

those kids are gonna go pro with batting averages like that

[Mydoggie]

I am trying to have excel automatically pull out almost expired and expired due dates from 10 tabs tabs and place on an overview page. So in each tab are due dates and I used conditional formatting to make them Green/Yellow/Red for not due yet, almost due within 30 days and Red expired. I would like to know how to pull out of 10 tabs (10 organizations keeping track of) onto a summary page the Almost due and due/overdue conditions as a reminder over view rather than having to look in at each of the 10 tabs to see what is almost due or due/overdue. How do I do that?