Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting Range name's listed in the range address box. | Excel Discussion (Misc queries) | |||
Formula to return ADDRESS of cell in range that meets criteria | Excel Worksheet Functions | |||
Indirect range in SUMPRODUCT? | Excel Discussion (Misc queries) | |||
summing part of cells in a range | Excel Discussion (Misc queries) | |||
Address of named range | Excel Worksheet Functions |