Read range value from a STRING cell

2
votes

I have many formulas (20+) using data from some range in a single spreadsheet.

The problem is that when I copy those formulas to some other part of the spreadsheet to handle another range, I have to manually modify each of them to adapt them with the new range value !!

What I'm looking for is -for example- to enter the range value in one cell as a string, so all my formulas will automatically use that string value as a range.

In other words, let's say that my formula in cell A2 is calculating the sum of a range using =SUM(C32:C45)

If cell A1 contains the value C32:C45 I want to modify my A2 formula to use that range.

I tried =SUM(INDIRECT(ADDRESS(row(),column()-1))) but it didnt work... What Am I missing ?

Any other ideas that helps avoiding the multiple editing of each formulas are welcome (hopefully using native google-spreadsheet functions only -no scripting unless it's the only way :< -)

1
google-sheetsgoogle-docs
Oh, good catch... I needed a double indirect function... I didnt know about named ranges either, that for sure would be helpfull too... Thanks a lot and feel free to post a proper answer so I'll accpet it :-) – Enissay

1 Answers

1
votes

When in ColumnA column()-1 won't suit. In keeping with the OP's approach it seems: 

=sum(indirect(indirect(address(row()-1,column()))))

would suit though perhaps also the simpler: 

=sum(NamedRange1)

where NamedRage1 is C32:C45.