Pulling data from Sheets in A workbook

M

MRSVATEK

I have 100 sheets with the same layout that I want to summarize on a master
worksheet. The cell references are the same on all 100 sheets so it is easy
to link the data but I dont want to reference or change the sheet name to get
each line of data.
Can I name the sheet with a text string that is on my summary worksheet and
have a formula look for that sheet and pull the data in the referenced cells.
 
L

Luke M

Yes, trying using the INDIRCT function. For instance, if you ahve a sheet
name "My Sheet", and cell A1 contains "My Sheet" This will return the value
of B2 from My Sheet

=INDIRECT("'" & A1 & "'!B2")
the spaces are just for clarity. Note that you can concatenate text and
cells together.
 
G

Gord Dibben

This might be what you want.

Use this macro to get a list of all sheets into column A of Summary sheet.

Sub CreateListOfSheetsOnSummarySheet()
Dim ws As Worksheet
For I = 1 To Worksheets.Count
With Worksheets("Summary")
Set ws = Worksheets(I)
.Cells(I, 1).Value = ws.Name
End With
Next I
End Sub

In B1 enter =INDIRECT(A1&"!C6") and copy down.

In C1 enter =INDIRECT(A1&"!E6") and copy down.


Gord Dibben MS Excel MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top