Indirect Returning #N/A Error when referred to a range

[High Plains Grifter]

A Person using my workbook will go to sheet 2 and enter a series of integers from 1 to 5 inclusive. Sheet 1 will display analysis of the numbers. In order that the data be easily updated , I have had to use formulas which will refer to the right places when they are clicked and dragged, or in other ways extended.
The formula I have entered in sheet one is essentially a reference to a range in sheet 2, and then taking the mode() of that range.

The bottom of the range is found like this:

COUNTA(INDIRECT("Sheet2!"&CHAR(64+ROW(A1)*2)&":"&CHAR(64+ROW(A1)*2)))

char(64+row(A1)*2 can be dragged down to display alternate letters starting with B, so the indirect() parts collapse down to give Counta(indirect("Sheet2!B:B") in the first row. This counts the number of integers entered in the data column (let's say there are 31 entries)

This part of the formula works fine when used on its own in a cell, but in order to calculate the mode of the range, I need to put it into a larger formula:

=MODE(INDIRECT("Sheet2!"&ADDRESS(2,ROW(A1)*2)&":"&CHAR(64+ROW(A1)*2)&COUNTA(INDIRECT("Sheet2!"&CHAR(64+ROW(A1)*2)&":"&CHAR(64+ROW(A1)*2)))))

with parts of this compressed, this should come out as looking like this:

=MODE(INDIRECT("Sheet2!"&"$B$2&":"&B&COUNTA(INDIRECT("Sheet2!"&B&":"&B)

However, the bold part of the formula, which worked fine in a cell on its own, for some reason returns a #value! when it tries to implement the Indirect("Sheet2!B:B") step. This then leads to an error in the whole formula.

Why, and how do I avoid this?

Thanks for looking at this post; I hope I have provided enough info, and all responses will be very welcome.
Mark

 

[barry houdini]

Hello Mark,

Your specific problem is with the ROW function. Although ROW(A1) returns a single value it's actually an "array" rather than a "scalar". One way round this is to replace all of your ROW(A1) instances in the longer formula with ROWS(A$1:A1) but, in fact, you could probably shorten your formula by using an approach that avoids INDIRECT altogether, e.g. try this formula

=MODE(OFFSET(Sheet2!B$2,,(ROWS(J$2:J2)-1)*2,COUNTA(OFFSET(Sheet2!B$2:B$1000,,(ROWS(J$2:J2)-1)*2))))

Assumes that you have the formula in sheet1 J2 copied down, adjust as required

 

[High Plains Grifter]

Thanks - I had forgotten about OFFSET() being used like that :P Very kind of you to reply so promptly, and so well. I will remember offset() now
Thanks
Mark