Indirect Row reference from another sheet

[gruntingmonkey]

I have this calculation that I can not seem to get to work. I have a cell on the page with the last row (Cell B17 with a value of 20)

Original:
=SUMIF('Line Detail Data'!E14:E20,"<>""",'Line Detail Data'!A14:A20)

My poor attempt:
=SUMIF('Line Detail Data'!E14:INDIRECT("E"&B17),"<>""",'Line Detail Data'!A14:A20)

If I can understand that first bit, I can then apply to the rest of the working.

 

[Fluff]

Try

=SUMIF('Line Detail Data'!E14:index('Line Detail Data'!e4:e1000,B17),"<>""",'Line Detail Data'!A14:A20)

 

[gruntingmonkey]

Thanks for this Fluff.

This kind of negates the issue as E1000 doesn't go far enough down the sheet. There's roughly 50k lines. If I put the 100 to 100,000 it works but I'm worried if the data ever exceeds100k lines that it wont automatically update. Essentially the data in cell B17 is the last row used in column A.

 

[Fluff]

f I put the 100 to 100,000 it works but I'm worried if the data ever exceeds100k lines that it wont automatically update.

Then make it 1,000,000, IMO you should avoid indirect as much as is possible.

 

[gruntingmonkey]

Noted, thanks for the info. Will now go and change all my indirects to index!

 

[Fluff]

You're welcome & thanks for the feedback.