Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pulling data from Sheets in A workbook
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pulling data from Sheets in A workbook
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. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "MRSVATEK" wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pulling data from Sheets in A workbook
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 On Wed, 24 Jun 2009 12:40:01 -0700, MRSVATEK wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pulling data from other Excel sheets | Excel Discussion (Misc queries) | |||
Need help with a formula pulling data from mutilple sheets | Excel Discussion (Misc queries) | |||
Multiple Sheets (Need to create 500 individual sheets in one workbook, pulling DATA | Excel Worksheet Functions | |||
pulling through data from another workbook | Excel Discussion (Misc queries) | |||
Pulling data from another workbook | Excel Worksheet Functions |