Summing a range using INDIRECT & ADDRESS
 

I am trying to sum a range on the "Data" sheet. I will actually be using the
MATCH function within the ADDRESS function to ultimatly identify the range to
sum. But I cannot even get this fuction to work. It gives the #Ref! error.


=SUM(INDIRECT("Data!"&(ADDRESS(55,5))&":"&(ADDRESS (55,64))))

Thanks for any help.
Todd

Works for me. Do you have a sheet called Data?

--

Vasant

"Todd" wrote in message
...
I am trying to sum a range on the "Data" sheet. I will actually be using
the
MATCH function within the ADDRESS function to ultimatly identify the range
to
sum. But I cannot even get this fuction to work. It gives the #Ref!
error.


=SUM(INDIRECT("Data!"&(ADDRESS(55,5))&":"&(ADDRESS (55,64))))

Thanks for any help.
Todd

Your formula works for me, so long as I have a sheet named Data. You have a
few needless sets of (), but they don't interfere with the formula.

=SUM(INDIRECT("Data!"&ADDRESS(55,5)&":"&ADDRESS(55 ,64)))

"Todd" wrote:

I am trying to sum a range on the "Data" sheet. I will actually be using the
MATCH function within the ADDRESS function to ultimatly identify the range to
sum. But I cannot even get this fuction to work. It gives the #Ref! error.


=SUM(INDIRECT("Data!"&(ADDRESS(55,5))&":"&(ADDRESS (55,64))))

Thanks for any help.
Todd

Vasant Nanavati wrote...
Works for me. Do you have a sheet called Data?

There's a not too unlikely chance the OP's worksheet name is more
complicated than Data. If it includes spaces, then it needs to be
delimited by single quotes. Good idea always to delimit the worksheet
name with single quotes.

=SUM(INDIRECT("'Data'!"&...))

"Todd" wrote in message
I am trying to sum a range on the "Data" sheet. I will actually be using
the MATCH function within the ADDRESS function to ultimatly identify the
range to sum. . . .
....
=SUM(INDIRECT("Data!"&(ADDRESS(55,5))&":"&(ADDRE SS(55,64))))

No good reason to use INDIRECT or ADDRESS. If the first cell is fixed
and the second to be given by a MATCH call, then better to use

=SUM('Data'!$E$55:INDEX('Data'!$E$55:$IV$55,MATCH( ...)))

which replaces the volatile INDIRECT call with a nonvolatile INDEX call.