Multiple Worksheet reference in a formula
I'd like to collect the value from all of the "A3" cells in a series of
worksheets in one workbook. Like: =Sheet2!A3, =Sheet3!A3, =Sheet4!A3, etc, but instead of typing each sheet's name for every instance, I'd like to list all the sheets in a column and then have the formulas refer to that column to pick up the sheet names. Can do? |
Multiple Worksheet reference in a formula
If your sheets are the default names of Sheet1 etc. enter this formula in a
summary sheet in e.g. A1 =INDIRECT("Sheet" & (ROW()) & "!A3") Copy down. If not default names................................. This macro will give you a new sheet with the names of sheets in column A Private Sub ListSheets() 'list of sheet names starting at A1 on new sheet Dim rng As Range Dim i As Integer Worksheets.Add(After:=Worksheets(Worksheets.Count) ).Name = "List" Set rng = Range("A1") For Each Sheet In ActiveWorkbook.Sheets rng.Offset(i, 0).Value = Sheet.Name i = i + 1 Next Sheet End Sub In B1 of "List" sheet enter =INDIRECT(A1 & "!A3") Double-click on fill handle to copy down. Gord Dibben MS Excel MVP On Sat, 23 Aug 2008 08:06:28 -0700, Mike Maguire <Mike wrote: I'd like to collect the value from all of the "A3" cells in a series of worksheets in one workbook. Like: =Sheet2!A3, =Sheet3!A3, =Sheet4!A3, etc, but instead of typing each sheet's name for every instance, I'd like to list all the sheets in a column and then have the formulas refer to that column to pick up the sheet names. Can do? |
All times are GMT +1. The time now is 04:21 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com