Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
AlZee
 
Posts: n/a
Default open ended multi page sum


RB: thanks again for your effort in helping me out. The format would
be:

'c:\new project\[school 1.xls]school summary'!$B$6

The WB is "School 1" (there will be many of these, called school 1,
school 2, school 3 etc); the WS is "school summary" (this will exist in
every workbook); and the top cell of the column of numbers to be summed
is B6. I am trying to get the master summary (for school districts) of
all of the individual "school summary" pages. If we can get this to
work than I would copy/paste the rest of the column.


--
AlZee
------------------------------------------------------------------------
AlZee's Profile: http://www.excelforum.com/member.php...o&userid=15906
View this thread: http://www.excelforum.com/showthread...hreadid=273888

  #2   Report Post  
RagDyeR
 
Posts: n/a
Default

What version of XL are you running?

--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

"AlZee" wrote in message
...

RB: thanks again for your effort in helping me out. The format would
be:

'c:\new project\[school 1.xls]school summary'!$B$6

The WB is "School 1" (there will be many of these, called school 1,
school 2, school 3 etc); the WS is "school summary" (this will exist in
every workbook); and the top cell of the column of numbers to be summed
is B6. I am trying to get the master summary (for school districts) of
all of the individual "school summary" pages. If we can get this to
work than I would copy/paste the rest of the column.


--
AlZee
------------------------------------------------------------------------
AlZee's Profile:
http://www.excelforum.com/member.php...o&userid=15906
View this thread: http://www.excelforum.com/showthread...hreadid=273888


  #3   Report Post  
RagDyer
 
Posts: n/a
Default

This procedure is workable in any XL version, if you're willing to go this
route.

You would have a column on your Summary sheet, where each row of the column
would return the contents of cell B6 from your individual "School #"
workbooks.
The formulas in this column will obtain the number of the "School #"
workbook from just dragging down the formula to copy as far as needed, and
return values from opened and/or closed WBs.
These formulas will also be ready to return data from as yet "uncreated"
WBs.
This would be easy if the Indirect() function would work on closed WBs, but
since it doesn't, this workaround might satisfy you.

Enter this formula in say D5:

="='c:\new project\[school "&ROW(A1)&".XLS]school summary'!$B$6"

NOTICE, that what's in the formula bar does *not* match what's displayed in
the cell.
Using the "fill handle", drag down to copy, say to D100.
During this copying, is the *only* time that you can automatically increment
the WB name, so a few extra rows shouldn't hurt.

Now, while all the rows are *still* selected from the "drag copy", right
click in the selection and choose "Copy".
Right click in A5, and choose "PasteSpecial".
Click on "Values", then <OK, then <Esc.

You now have a column of "Text" formulas, linked to existing and "not yet"
existing WBs.
Since they are "Text", you aren't getting any errors in the column from
"non-existant" links.
If you have an existing WB, "School 1", click in A5, hit <F2 then <Enter,
and you'll see the data from B6 in the "School 1" WB.
You could now go down the column, converting the formulas of existing WBs to
"true" formulas with the <F2 <Enter keystrokes.

If you have a large number of already existing WBs, you could convert them
in a single block by simply selecting those appropriate cells in ColumnA,
then:
<Edit <Replace
In the "Find What" box enter "=" (no quotes),
In the "ReplaceWith" box enter "=" (no quotes),
Then <ReplaceAll
And you should have all your existing links displayed.

You could enter your SUM() formula in, say A4:

=SUM(A5:A100)

And have your totals returned without having any errors displayed, since
Sum() will by-pass any text, including the non-converted text formulas.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"AlZee" wrote in message
...

RB: thanks again for your effort in helping me out. The format would
be:

'c:\new project\[school 1.xls]school summary'!$B$6

The WB is "School 1" (there will be many of these, called school 1,
school 2, school 3 etc); the WS is "school summary" (this will exist in
every workbook); and the top cell of the column of numbers to be summed
is B6. I am trying to get the master summary (for school districts) of
all of the individual "school summary" pages. If we can get this to
work than I would copy/paste the rest of the column.


--
AlZee
------------------------------------------------------------------------
AlZee's Profile:
http://www.excelforum.com/member.php...o&userid=15906
View this thread: http://www.excelforum.com/showthread...hreadid=273888

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Cannot open a file that Excel says is open Plodhia Excel Discussion (Misc queries) 2 December 7th 04 02:43 AM
Open page to row 1 instead of row 36 Frank Excel Discussion (Misc queries) 1 November 29th 04 07:09 PM
open ended multi page sum AlZee Excel Worksheet Functions 1 November 1st 04 02:26 AM
open ended multi page sum AlZee Excel Worksheet Functions 2 October 31st 04 06:49 PM
open ended multi page sum AlZee Excel Worksheet Functions 3 October 30th 04 11:30 PM


All times are GMT +1. The time now is 08:52 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"