INDIRECT() randomly breaks with ROW()

[lamking]

I am trying to do dynamic formulae creation and have successfully done in the past, but I am facing a very strange issue whereby using the ROW() formula within the INDIRECT() formula breaks it and it gives a #VALUE error.

If I enter 3 values in column A as below:

A1: 1
A2: 2
A3: 3

I can then enter the following in B1: =SUMPRODUCT(INDIRECT("A"&1&":"&"A3")) which gives the correct answer of 6. However, if I enter in C1: =SUMPRODUCT(INDIRECT("A"&ROW()&":"&"A3")) the answer given is 0 because at the final stage INDIRECT({A1:A3}) evaluates to #VALUE.

If I again change this an in D1 I enter: =ROW() which evaluates to 1 and in E1 I enter: =SUMPRODUCT(INDIRECT("A"&D1&":"&"A3")) it again works no problem.

Can someone please enlighten me as to why the formula in C1 is broken?

Thanks muchly.
Ian

 

[lamking]

I am trying to do dynamic formulae creation and have successfully done in the past, but I am facing a very strange issue whereby using the ROW() formula within the INDIRECT() formula breaks it and it gives a #VALUE error.

If I enter 3 values in column A as below:

A1: 1
A2: 2
A3: 3

I can then enter the following in B1: =SUMPRODUCT(INDIRECT("A"&1&":"&"A3")) which gives the correct answer of 6. However, if I enter in C1: =SUMPRODUCT(INDIRECT("A"&ROW()&":"&"A3")) the answer given is 0 because at the final stage INDIRECT({A1:A3}) evaluates to #VALUE.

If I again change this an in D1 I enter: =ROW() which evaluates to 1 and in E1 I enter: =SUMPRODUCT(INDIRECT("A"&D1&":"&"A3")) it again works no problem.

Can someone please enlighten me as to why the formula in C1 is broken?

Thanks muchly.
Ian

I have solved my original problem with helper columns to remove the need for the ROW() formula to be embedded within the INDIRECT() formula, but the fact remains that I would love to know why this is failing?

If anyone has come across this issue (and preferably resolved it) I would love to hear your feedback.

Thanks
Ian

 

[rallcorn]

For dynamic begin & end row range, try:
=SUM(INDIRECT("A"&ROW()&":A"&COUNT(A:A)))

 

[lamking]

For dynamic begin & end row range, try:
=SUM(INDIRECT("A"&ROW()&":A"&COUNT(A:A)))

Hi rallcorn,

Thanks for the reply . . . and I use a variety of techniques to get dynamic calculations - usually summing over dynamic ranges.

However, in this case, I have to use the INDIRECT() function and combine it with the ROW() function and I started off with a very long complex formula which was broken. As I analysed the problem, I came down to the fact that I could break INDIRECT with ROW with the simplest of functions as stated in the first post. In fact you get the same issue using a simple SUM instead of the SUMPRODUCT.

I don't understand why the above simple formula is breaking. I tested your formula and it works and does not evaluate to #VALUE, but several other options such as entering the following in G1: =SUMPRODUCT(INDIRECT("A1:"&"A"&ROW()+2)) also break and give a #VALUE error during evaluation.

 

[lamking]

My exact proble is described here: http://stackoverflow.com/questions/1...and-sumproduct

Thanks for the help.

 

[vds1]

where ever you have inserted Row() > Replace it with SUMPRODUCT(ROW()) or SUM(ROW())

Example

In C1 case =SUMPRODUCT(INDIRECT("A"&ROW()&":"&"A3"))

will become


=SUMPRODUCT(INDIRECT("A"& SUMPRODUCT(ROW()) &":"&"A3"))

or
=SUMPRODUCT(INDIRECT("A"& SUM(ROW()) &":"&"A3"))

I think output of Row() is a array value which needs to be converted to a value. This is my opinion though.