Selected Answer

The formula can't be reconstructed without access to the worksheet on which it is supposed to work but the following term in it is definitely wrong.

`D18&" - "&S18&" ( "&AG18&" : "&AN18&" )"`

I can't guess what D18, S18, AG18 or AN18 contain but if both D18 and S18 are numbers then *D18&" - "&S18* should be*D18 - S18* and the following *" ( "&AG18&" : "&AN18&" )"* can't be constructed as valid syntax, like *100 - 50(P13:O26)*.

Starting with the assumption that *" ( "&AG18&" : "&AN18&" )"* is a range, AG18 and AN18 should hold cell addresses, resulting in something like *(P13:Q26). *So, perhaps S18 holds a function name like "SUM". Perhaps you are trying to construct something like *SUM(**P13:Q26).* If this is so I must tell you that, to the best of my knowledge, it isn't possible to take the function name from a cell value. It needs to be in the formula and then you can assign the range using *INDIRECT()*. In the example below AG18 and N18 would hold text like "P13" and "Q13".

`=D18-SUM(INDIRECT(AG18&" : "&AN18))`

As a useful alternative you might use the ADDRESS function to construct the range address.

`=D18-SUM(INDIRECT(ADDRESS(AG18,16)&" : "&ADDRESS(AN18,17)))`

Here the numbers 16 and 17 represent the columns P and Q while AG18 and AN18 hold row numbers. This construct would enable you to calculate the start and end rows using functions. The column numbers could also be determined by some calculation if that is helpful in your taks.