Summing a range using INDIRECT & ADDRESS

[Guest]

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]

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

 

[Guest]

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)))

 

[Harlan Grove]

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'!"&...))

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.