Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
link values from same cell of 20 worksheets into column on one she
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
link values from same cell of 20 worksheets into column on one she
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 --------------- Jacob Skaria "Stingraynut" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
link values from same cell of 20 worksheets into column on one she
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&"!B 2")) 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 "Stingraynut" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
link values from same cell of 20 worksheets into column on one she
See this:
http://www.mrexcel.com/forum/showthread.php?t=428957 -- Biff Microsoft Excel MVP "Ashish Mathur" wrote in message ... 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&"!B 2")) 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 "Stingraynut" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
link cell in to different worksheets in same workbook excel 2003 | Excel Discussion (Misc queries) | |||
Insert a value in a cell based upon a comparison of cell values in 2 separate worksheets | Excel Worksheet Functions | |||
Auto Update the formula values without opening the link worksheets | Excel Worksheet Functions | |||
Creating a link from cell values | Excel Discussion (Misc queries) | |||
Is there a way to link Auto Filter values to a Cell | Excel Worksheet Functions |