Linking arrays and Worksheet names
 

Doozie of a question - workbook, formula linking

Hey guys,

So I have this workbook with tons of different worksheets, with
different names. I also have a summary page with summaries of the
answers from these sheets.

Is there any easy way to make the summary formulas automattically
include any new worksheets that are added?

I was thinking if I could do a list of the worksheets, then I could
just tell it somehow to say

= sum({list} for cell b56) - this calcs the sum for all the b56 cells
in that list.

I tried to do this through concatenate, but it comes out as text and
errors. There seems to be no way to link to another worksheet without
pyshically clicking into it (this makes it a pain, since I need to
change every cell for each new sheet) Now if I could get it to just
do:

Say the worksheets to sumarize from are red,blue, green. I want to be
able to list these vertically and have excel do

sum(Red56,Blue56,Green56) and when I add Yellow to the list to just
make it sum(Red56,Blue56,Green56, yellow56)

Anyone?


--
gsimmons2005
------------------------------------------------------------------------
gsimmons2005's Profile: http://www.excelforum.com/member.php...o&userid=26385
View this thread: http://www.excelforum.com/showthread...hreadid=396645

gsimmons2005 wrote...
....
So I have this workbook with tons of different worksheets, with
different names. I also have a summary page with summaries of the
answers from these sheets.

Is there any easy way to make the summary formulas automattically
include any new worksheets that are added?
....

The easiest way to do this would be to add one blank worksheet before
and one after the worksheets over which you're summing. Name them
something like First and Last. Then use formulas like

=SUM(First:Last!B6)

Insert new worksheets between First and Last, and your formulas will
automatically adapt to the change. Note: this is a 3D adaptation of a
technique that goes back at least to the mid-1980s in Lotus 123.

That is a great tip, and I have a question that is similar.

Say that I have several worksheets with the same data layout.

I also have a sheet up front that is a summary sheet of all the other
data sheets.

I would like to list out the data of a specific cell (D50) for each
sheet on the summary sheet.

Is there an easy way to do this?

Edit: I should say that currently I am hand linking each value to the
summary page... this is very time consuming and I am looking for a
shortcut.


--
gwexcel
------------------------------------------------------------------------
gwexcel's Profile: http://www.excelforum.com/member.php...fo&userid=6845
View this thread: http://www.excelforum.com/showthread...hreadid=396645