Populate Summary Sheet with same cell from multple sheets

Originally Posted by ShredDude

I'm attempting to pull the same cell ($a$1) from multiple sheets into one summary sheet, populating a column in a table.

I though I might accomplish this by putting an array formula in the desired column of my summary sheet that was similar to the following, to pull cell A1 from every sheet between the Frst Sheet and the Last Sheet.

For example:

=FirstSheet:LastSheet!:$a$1 (CTRL+SHIFT+ENTER)

This gives me #REF errors.

The values in $a$1 on each sheet are Text (addresses)

Anyway I can do this without Macros? The number of sheets between the First and Last will vary at any given time.

Thanks in advance!

Shred

Hi,

if you don't get a Formula version, then you can rightmouse the tab (of the master sheet) and select 'View Code' and paste

Please Login or Register  to view this content.

into there.

You can adjust the range where the results are pasted by amending
Range("A" & i) = Sheets(i).Cells(1, 1)
to
Range("A" & i + 4) = Sheets(i).Cells(1, 1)
etc, and the range where the data is collected from by adjusting
Range("A" & i) = Sheets(i).Cells(1, 1)
to
Range("A" & i) = Sheets(i).Cells(3, 4)
where (3,4) is Row 3, Column 4 = D3 etc

hth
---

Originally Posted by ShredDude

Instead of graggin a Cell on the worksheet as this is doing, could I grab the worksheet Name instead? Is there something like:

HTML Code: 

??

I'm using INDIRECT calls to that column D on my summary sheet to perform other actions that pull data from the Sheet named according to whats in column D. (It just so happens at this point I've got Cell D9 and the sheet name the same.) But for Data consistency, I was hoping to eliminate that possibility of an error.

Thanks again...I'm a newbie at using VB in Excel, but can appreciate the power of it. I"m hoping to learn how to use it more effectively. Any direction towards intor to VB links would be appreciated. I've already found a few and have begun reading.

Shred.

Hi,

the sheet number must remain the sheet number,

Range("D" & i + 7) = Sheets(i).Name

should help
---

Originally Posted by ShredDude

I got it working with the i+3 in the Sheet number part. It starts from the fourth sheet in the workbook and carries on from there. works great.

What I haven't got going yet is the trigger. To get it to work now, I have to open the code, and from the VB editor Run the sub routine. it then repopulates the summary sheet accordingly.

I've been reading the help on how I might cause another event to trigger the activation of the routine, but haven't got it going yet. Ideally, I'd like to have the insertion of another sheet, or the moving of a sheet to a different spot in the sequence of sheets, cause the routine to run and repopulate the column on the Summary page.

Any help / insight would be much appreciated.

The problem with using i + 3 that way is that there are three additional attempts at non-existing worksheets. Try
For i = 4 To Worksheets.Count
to start at Sheet 4 to the last sheet.

The original code should have triggered each time the sheet was selected.

If you are not on the summay page then the order in which the display occurs should be of little importance unless you are also selecting items via something like =Summary!B7 etc.

hth
---

Originally Posted by Stuart Farr

Perhaps this is too simplistic but in cell A1 on the Summary sheet why not input the formula "=SUM(Sheet2:Sheet20!A1) where Sheet1 is the second sheet i.e. excluding the Summary sheet and Sheet20 is the last sheet. If you insert any new sheets within the range, this formula will automatically pick up the insertion.

Hi Stuart,

the request was (originally) for a list down a column collected from a variable number of sheets rather than an accumulation.

---