link values from same cell of 20 worksheets into column on one she

[Stingraynut]

I would like to list on one sheet, the values of a cell B2 from about 20
worksheets

This list would run down a column of the master worksheet ie B1,B2,B3 etc

Ideally this list would automatically expand if more sheets were added,
perhaps by having a blank 'FIRST" and 'LAST" sheets at either end?

I can see how to do the linking by hand, one at a time, but is there a way
to do it by formula?

In fact I'd like to make two lists on the master sheet- one would be names
from all the B2 cells and the other would be corresponding numbers from all
the I12 cells

 

[Jacob Skaria]

Try this VBA solution. Select the sheet tab which you want the data to be
populated. Right click the sheet tab and click on 'View Code'. This will
launch VBE. Paste the below code to the right blank portion. Get back to to
workbook and try out.

The below will update the details from sheet named 'first' upto 'last'..You
can change these to the actual sheetnames..

Private Sub Worksheet_Activate()
Dim intCount As Integer, lngRow As Long
lngRow = 1
Application.EnableEvents = False
For intCount = Sheets("FIRST").Index To Sheets("LAST").Index
Range("B" & lngRow) = Sheets(intCount).Range("B2")
Range("C" & lngRow) = Sheets(intCount).Range("I12")
lngRow = lngRow + 1
Next
Application.EnableEvents = True
End Sub

If this post helps click Yes

 

[Ashish Mathur]

Hi,

Try this

1. Open a new worksheet and name this Summary. Position this sheet as the
first sheet
2. Now create a name (Ctrl+F3) called sheetname and in the refers to box,
type =TRANSPOSE(GET.WORKBOOK(1))&T(NOW())
3. Now select H2:H100 and array enter (Ctrl+Shift+Enter) the following
formula =MID(sheetname,SEARCH("]",sheetname)+1,50). This will give you all
the sheet names
4. In cell B2 of the Summary sheet type
=IF(ISERROR(INDIRECT(H3&"!B2")),"",INDIRECT(H3&"!B2")) and copy down till
B100. This will give you the value in cell B2 of all the sheets

Now when you add more sheets, the value from cell B2 of all the sheets will
appear in A2:A100

Hope this helps
--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[T. Valko]

See this:

http://www.mrexcel.com/forum/showthread.php?t=428957

--
Biff
Microsoft Excel MVP

Hi,

Try this

1. Open a new worksheet and name this Summary. Position this sheet as the
first sheet
2. Now create a name (Ctrl+F3) called sheetname and in the refers to box,
type =TRANSPOSE(GET.WORKBOOK(1))&T(NOW())
3. Now select H2:H100 and array enter (Ctrl+Shift+Enter) the following
formula =MID(sheetname,SEARCH("]",sheetname)+1,50). This will give you
all the sheet names
4. In cell B2 of the Summary sheet type
=IF(ISERROR(INDIRECT(H3&"!B2")),"",INDIRECT(H3&"!B2")) and copy down till
B100. This will give you the value in cell B2 of all the sheets

Now when you add more sheets, the value from cell B2 of all the sheets
will appear in A2:A100

Hope this helps
--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

I would like to list on one sheet, the values of a cell B2 from about 20
worksheets

This list would run down a column of the master worksheet ie B1,B2,B3 etc

Ideally this list would automatically expand if more sheets were added,
perhaps by having a blank 'FIRST" and 'LAST" sheets at either end?

I can see how to do the linking by hand, one at a time, but is there a
way
to do it by formula?

In fact I'd like to make two lists on the master sheet- one would be
names
from all the B2 cells and the other would be corresponding numbers from
all
the I12 cells