Changing a range referenced by Conditional Sum

[Guest]

I have created a table that uses a Conditional Sum Formula Array to add up
values by Hospital Floors or Units. Folks who audit charts send in results
that are added to a large, growing spread sheet in multiple columns.

I want get the table to dynamically change the range each Conditional Sum
formula references, so that by clicking a button, the user can see the
tabulated results for each of the questions answered in the spread sheet.

My Conditional Sum formula is currently as follows:

=SUM(IF('Sheet1'!$T$5:$T$1000="Jan",IF('Sheet1'!$D$5:$D$1000="2
Tower",'Sheet1'!$K$5:$K$1000,0),0))

Is there a way I can change this formula's refenced range by using a
variable that will look range value specified in another cell. For example,
if cell A1 were to contain the value 'Sheet1'!$K$5:$K$1000. The formula could
look like:

=SUM(IF('Sheet1'!$T$5:$T$1000="Jan",IF('Sheet1'!$D$5:$D$1000="2
Tower",**Cell A1's Value**,0),0))

This way, when a user wants to see the results to Question A, Question B, or
Question C, he clicks on a button and the range values change in the
Conditional Sum Formula Array.

I've tried several different ways to get the formula to reference a range I
specify elsewhere but with no luck. I am still relatively new to all of this.

Any help would be greatly appreciated,

WillRn

 

[Guest]

If you have this in A1

Sheet1!K5:K1000

then you can use

=SUMPRODUCT(--(Sheet1!$T$5:$T$1000="Jun"),--(Sheet1!$D$5:$D$1000="2
Tower"),INDIRECT(A1))

your originall formula can be simplified to the above, it's faster and you
can enter it normally


Regards,

Peo Sjoblom

 

[Frank Kabel]

Hi
try:
=SUM(IF('Sheet1'!$T$5:$T$1000="Jan",IF('Sheet1'!$D$5:$D$1000="2
Tower",INDIRECT(A1),0),0))

 

[Guest]

Try

=SUMPRODUCT(--(Sheet1!$T$5:$T$1000="Jun"),--(Sheet1!$D$5:$D$1000="2
Tower"),INDIRECT(A1))

no need for the sum(if(
the above is faster and can be entered normally


Regards,

Peo Sjoblom

 

[Guest]

I tried this but keep getting a #REF error.

If you have this in A1

Sheet1!K5:K1000

then you can use

=SUMPRODUCT(--(Sheet1!$T$5:$T$1000="Jun"),--(Sheet1!$D$5:$D$1000="2
Tower"),INDIRECT(A1))

your originall formula can be simplified to the above, it's faster and you
can enter it normally


Regards,

Peo Sjoblom

 

[Guest]

I have tried both ways and still get a #REF error

 

[Frank Kabel]

Hi
what is exactly in cell A1?

 

[Guest]

Sheet1!K5:K10000

When I look in the "Edit Formula" window it returns "Sheet1!K5:K10000" as
the value of INDIRECT(A1).

When I enter the formula array, it then goes to #REF for the value.

Strange?

 

[Guest]

Should read:

'Sheet1'!K5:K10000

Sheet1!K5:K10000

When I look in the "Edit Formula" window it returns "Sheet1!K5:K10000" as
the value of INDIRECT(A1).

When I enter the formula array, it then goes to #REF for the value.

Strange?

 

[Frank Kabel]

Hi
try entering the value in cell A1 WITHOUT the leading apostrophe:
Sheet1'!K5:K10000

and use
INDIRECT("'" & A1)
instead

 

[Guest]

Now I get "#NA"

Hi
try entering the value in cell A1 WITHOUT the leading apostrophe:
Sheet1'!K5:K10000

and use
INDIRECT("'" & A1)
instead

 

[Frank Kabel]

Hi
probably your ranges are not correct. Please post your complete formula
+ the value in cell A1

 

[Guest]

I think I figured out part of the problem.

It is the linking of the data range to other work sheets. The Indirect(A1)
works if my cell references are on the same page. When I try to specifiy a
range on another spread sheet, I get the error messages.

Is there a way a can fix this or do I need to build my tables on the same
worksheet as the data ranges the tables pull from?

 

[Frank Kabel]

Hi
it should also work for other sheets in the same file (it won't work
for closed workbooks though). As said: post your exact formula and the
exact value in cell A1

 

[Guest]

The Orginal Conditional Sum Formula straight off the spread sheet:

=SUM(IF('PCA Review'!$T$5:$T$1000="Jan",IF('PCA Review'!$D$5:$D$1000="Cath
Lab",'PCA Review'!$K$5:$K$1000,0),0))

The Altered Formula with INDIRECT(A1):

=SUM(IF('PCA Review'!$T$5:$T$1000="Jan",IF('PCA Review'!$D$5:$D$1000="Cath
Lab",INDIRECT(A1),0),0))

The Value of A1:

'PCA Review'!$K$5:$K$1000

I just rewrote the formula in the process of doing this and it worked!!!!

I guess there was a typo somewhere somehow, . . . but I couldn't find it. I
also had to close and re-open the workbook to get my original formulas back.
Perhaps that had something to do with it.

Thanks a million for all your patience and help,

WillRn

 

[Guest]

As an addendum, I noticed that the leading single quotation mark gets dropped
out of the cell when you post the value to the cell. so the value looks like
" PCA Review'!$D$5:$D$1000 . . . ."

I placed an additional single quote mark in front of the cell entry, " ' '
PCA Review'!$D$5:$D$1000 . . . ." and the formula referenced the value with
no problems.

WillRn