summary sheet that refers to multiple sheets

[Guest]

We are trying to produce a summary sheet that displays the value of the same
cell from a range of sheets. Ideally it will be a formula that can be copied
down the column, with each cell incrementing to the next sheet.
possible solutions might be around the use of a reference list to change the
sheet reference within the formula?apologies if this makes no sense! happy to
elaborate further

 

[MartinW]

Hi,

=INDIRECT("Sheet"&ROW()&"!A1")
This formula pasted into row 1 of your summary sheet and
dragged down will return
Sheet1 A1
Sheet2 A1
Sheet3 A1
etc.

If you were pasting it into say row 5 for example change it to
=INDIRECT("Sheet"&ROW()-4&"!A1")

Another method that might come in handy is Gord Dibben's
Start and End procedure.

Insert a blank worksheet before your first worksheet and
another after your last sheet and before your summary sheet.
Then in your summary sheet put this =SUM(Start:End!A1)

This will sum all of the A1s in your worksheets.

HTH
Martin

 

[Guest]

Presuming your source sheets are named as; Sheet1, Sheet2, Sheet3, etc
(where there is an incrementing number within the sheetname)

In your summary sheet,
Enter the target cell in B1, eg: C2

Place in B2: =INDIRECT("'Sheet"&ROWS($1:1)&"'!"&B$1)
Copy B2 down to return required results from each of the source sheets

The formula in B2 returns the same as the link: =Sheet1!C2, in B3 it'll
return: =Sheet2!C2 and so on, incrementing the sheet number as you copy down
...

You can also enter other target cells in C1, D1, E1 .. etc and just fill the
formula in B2 "as-is" across/down as far as required to return correspondingly

 

[T. Valko]

If you were pasting it into say row 5 for example change it to

=INDIRECT("Sheet"&ROW()-4&"!A1")

Better if you "hardcode" the row:

=INDIRECT("Sheet"&ROWS($1:1)&"!A1")

That way you don't have to calculate any offset depending on what row you
enter the formula in and as an added bonus it's robust against row
insertions. If you inserted a new row above the row that contains your
formula, the formula will break.

 

[MartinW]

Wuuooo.... Now you've started one Biff.
I used to use ROWS() for this sort of function until
JEMcGimsey chided me that ROW() is far more economical.
He didn't fully explain why and by comparing how
they work I came to the conclusion that when they
are dragged down to say row 2000,

the ROW() function is still just asking what row am I in
Whilst the ROWS() function is calculating A1:A2000

I don't know if my conclusion is right, I suppose I
should have asked JE for his reasoning.

Can you shed any more light on this?

Regards
Martin

 

[Bob Phillips]

Presumably because ROW gets its number from the Row the formula is on,
whereas ROWS will have to determine how many rows are in the range. But the
difference will be so slight that unless you have a performance issue it is
irrelevant. Also, ROW returns an array of numbers, even if that is just one
number, whereas ROWS returns a non-array result, so the formula has to
handle that, which probably counter-balances the other effect. Take into
account Biff's point about the formula failing, and there is no real
argument IMO.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[MartinW]

OK thanks Bob, I'm sure Harlan will pipe up if there
is some fundamental issue here. ;-)

Regards
Martin

 

[Harlan Grove]

OK thanks Bob, I'm sure Harlan will pipe up if there
is some fundamental issue here. ;-)

....

Since you invited me, ROW always returns an array, ROWS always returns a
scalar. ROW can cause problems when nested in some other functions. ROWS may
be slower, and it may use more memory, but it's generally more robust. So
would you prefer slower correct results or quick errors?

 

[MartinW]

Thank you Harlan,

Crystal clear and very informative.

Regards
Martin

 

[Bob Phillips]

Isn't that what I said?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[MartinW]

Yes it is Bob, but you stuck a 'presumably' at the start of
your post. Touchy, touchy, touchy no wonder you
blokes can't play cricket. <vbg>

Regards
Martin

 

[Bob Phillips]

Actually the presumably only referred to the performance issue, which Harlan
didn't address. The ROW v ROWS was fully covered by me.

And it was directed at Harlan Martin, as he likes to have a little friendly
banter <g>. And those blokes are even less able.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[T. Valko]

...

Since you invited me, ROW always returns an array, ROWS always returns a
scalar. ROW can cause problems when nested in some other functions. ROWS
may be slower, and it may use more memory, but it's generally more robust.
So would you prefer slower correct results or quick errors?

Decisions, decisions!

I like the way you put that.

 

[Guest]

thanks all there is a slight problem with the naming of the sheets is not
sheet1, sheet2 etc.
each sheet is the data pertaining to a single subject, and the tab is the
subject's reference number. in this case taking the form 'SAM3-01A' and the
next 'SAM3-02A'
as subject's data becomes available, new sheets are added. what we ware
after is a 'summary' sheet at the front of the book that has
SAM3-01A 312
SAM3-02A 421
etc
where the number following the tab/subject reference is on cell C10 of each
sheet.
Obvioulsy the easiest way to do this is copy the formula downwards and just
change the sheet reference within it manually.
but it would be great to see if there is a way that as sheets are added, the
summary can be expanded by dragging the formula down a column.

It might be that using Sheet1, Sheet2 will be the easiest way around this
and use the existing answers? many thanks

 

[Gord Dibben]

TBA

Private Sub ListSheets()
'list of sheet names starting at A1 down Column A
Dim Rng As Range
Dim i As Integer
Set SummarySheet = Worksheets.Add
SummarySheet.Name = "Summary"
Set Rng = Range("A1")
For Each Sheet In ActiveWorkbook.Sheets
If Sheet.Name <> "Summary" Then
Rng.Offset(i, 0).Value = Sheet.Name
i = i + 1
End If
Next Sheet
End Sub

Now you can enter in B1 =INDIRECT("'" & A1 & "'!C2")

Note the single quote included in "'" and "'! to allow for spaces in filenames.

Double-click on fill handle of B1 to copy down.

Adjust B1 and C2 to suit.


Gord Dibben MS Excel MVP

 

[Guest]

thanks Gord
would you mind running through the process of getting the code into excel
and how to then apply it? cheers Theo

 

[Gord Dibben]

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run or edit the macro by going to Tool>Macro>Macros.

You can also assign this macro to a button or a shortcut key combo.


Gord

 

[Guest]

Did you ever get this to work, I read through the answer and tried the
different but it did not work
I have 2 sheets-NY AND LA
A1:I1 ON BOTH SHEETS ARE THE SAME AND ABOUT 1000 ROWS STARTING ON A2:I2 OF
DATA ON EACH SHEET BUT OF COURSE DIFFERENT

HELP!!!!!!!!!!MY BOSS IS ON VACATION AND I TOLD HIM I WOULD HAVE IT FOR HIM
NEXT MONDAY