XL 2K - Function prob...using Sum & Indirect

[CEN7272]

First off, cell B1 contains the name of a sheet (sheet1) and cell B2 contains the name of another sheet (sheet7). I want to get the average of cell B6 from sheets 1-7.

I can accomplish this by using the following formula:

=SUM('sheet1:sheet7'!B6)

however it may not always be sheets1-7, sometimes it might be 4-8, etc. which is why I need to have the input cells (B1,B2)

I have tried using indirect and Average and I keep getting a #REF error. Here is the formula I've been trying. Am I missing something?

=SUM(INDIRECT("'"&B1&":"&B2&"'!B6"))

It seems to me that this should be the exact copy of a regular sum function. Any ideas?

-Clay
claysdays.blogspot.com

 

[SkipVought]

Hi,

Your syntax is incorrect!
[tt]
=SUM(Sheet1!A1,Sheet2!A1,Sheet3!A1.....)
[/tt]

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[CEN7272]

If you try it though, my way works. As long as the sheets are consecutive, its a good work around so that you don't have to write out every sheet in your function. I just don't understand why it won't work with the Indirect function.

-Clay
claysdays.blogspot.com

 

[DaleWatson123321]

CEN7272,

It would appear you're right - that your formula should work, but I've concluded as you have that it doesn't.

I do, however, have another option for you - that DOES work.

Here's the formula...
=SUM(INDIRECT(VLOOKUP(B1,table1,3)&":"&VLOOKUP(B2,table1,3)))

For the formula to work, you'll need to set up a relatively simple table - the one referred to in the formula as "table1" - a name you'll need to assign to a 3-column by 8-row range (to allow for 8 sheets). You could obviously expand this range for additional sheets if required.

The Table...
1st column - simply numbers 1 to 8.

2nd column - enter this formula =Sheet1!B$6, and copy down for the 8 rows, and change each formula to reference each sheet.

3rd column - enter this formula =CELL("ADDRESS",G2), and
copy down for the 8 rows. This formula is of course based on the table being in F2:H9. You'll need to adjust for wherever you decide to place your table.

I hope you can use this alternative solution.

Regards, Dale Watson

 

[CEN7272]

ok...I'll try the table. Any ideas why the format I was using for the Sum function worked when the sheets are typed in but why it doesn't work when they are referenced using 'indirect' or 'address'? Is it just a quirky thing or is there some kind of rule about that?

-Clay
claysdays.blogspot.com

 

[CEN7272]

Well I'm working on implementing the table but the problem is that each tab is actually a week worth of information, so every week the workbook will be one more sheet long. If I were the primary user of this book I would be able to add a new row everytime I add a sheet, but ideally I wanted to make this sheet as user friendly as possible. I would like the user to be able to be able to type in the range of sheets and get the average without having to add an extra row to the table every week...if this is the only way it can be done then so be it, but if there are any more ideas I'm willing to try. I've spent several hours on this and I feel like I just can't hop over this one last hurdle, if I can figure this out then the rest of the data will fall into place. Am I going about this all wrong? If nothing else, I may have to redesign this workbook. I didn't create it and its not really set up very well

-Clay
claysdays.blogspot.com

 

[SkipVought]

Clay,

...but the problem is that each tab is actually a week worth of information, ...

The REAL problem is with your workbook DESIGN. By segmenting similar data into multiple sheets, you are MULTIPLYING your problems. Take a look at

http://www.alphabetcitydataworks.com/FundamentalsOfRelationalDatabaseDesign.doc

If all your data were in ONE TABLE, with an additional column that would correspond to your Sheet Tab Name, you would not be having this problem. Reporting and data analysis would be relatively simple.

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[CEN7272]

OK...I think I'll follow you're advice and just redesign the workbook...I should have done that in the first place, Its definitely put together for input, not output. I just thought I might be able to work around the problem but, now I realize thats not going to happen. I've posted on ExcelForum.com also and nobody there has had any solution either...thanks.

-Clay
claysdays.blogspot.com

 

[SkipVought]

In such a design there are at least 3 major design and functionality issues...

1. How to get the data from the user to storage. (input)

2. How to organize and store the data. (storage)

3. How to analyze and report the data. (output)

Items 1 & 3, involving the user require interface issues. Item 2 is much more technical and should be totally transparent to the user.

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}