combining INDIRECT in one formula?

Thanks guys! That helps answer the first part of the question, and is so easy to see I feel like a dummy for over-complicating it trying to nest INDIRECTs.

But here's the part where I might be opening Pandora's box, because more than likely I'm making it harder than I need to. But I'm still relatively novice at understanding what's going on in certain Excel formulae that when I find one that works for me, I tend to try and stick with it and tweak it since I can wrap my head around it.

It might be easiest if I don't try to describe the data set too much, so rather let's say I am successfully using an array formula like this:

{=(SUM(IF(MOD(ROW(Input!$AE$1:$AE$10000),15)=0,Input!$AE$1:$AE$20999,0)))}

Which pulls information from worksheet "Input", from a defined range, and essentially I'm just looking to see if I can replace the "Input!$AE$1:$AE$10000" portion (and also the latter "Input!..." part) and maybe utilize INDIRECT function to pull either "AE" and the starting and ending numerical values (e.g., "1" and "10000" in this case) from other cells?

Or perhaps having one cell that contains the string "Input!$AE$",

another containing "1",

and another containing "10000"

?

I think I'm trying to do something too convoluted, and there's most likely a way easier way to do this, but any help or suggestions if very much appreciated.

Thank you guys a ton! I'm always wowed by these Excel forums and the depth of knowledge so many people have in using Excel. Makes me wish I'd gone down a career where I needed to use it more often and in more in-depth ways.

Anyway, thanks, and happy New Years!

If you define the name Input_ALL referring to =Input!$1:$1048576, and the name ColumnLetters referring to =SUBSTITUTE(ADDRESS(1,COLUMN(Input_ALL),4),1,""), and you had AE in cell X97, 1 in X98 and 10000 in X99 in another worksheet, then in that same other worksheet,

Formula:

=SUMPRODUCT(--(MOD(ROW(INDEX(Input_ALL,X98,0):INDEX(Input_ALL,X99,0)),15)=0),INDEX(INDEX(Input_ALL,X98,0):INDEX(Input_ALL,X99,0),0,MATCH(X97,ColumnLetters,0)))

would give you the same result as the array formula you show.

I'll be more direct: in the long run you want to avoid INDIRECT and OFFSET when possible. This is a situation in which it's relatively easy to avoid using them.

Simpler than the formula above would be

Formula:

=SUMPRODUCT(--(MOD(ROW(INDEX(Input!$AE:$AE,X98):INDEX(Input!$AE:$AE,X99)),15)=0),INDEX(Input!$AE:$AE,X98):INDEX(Input!$AE:$AE,X99))

Note: when X98 is 1 and X99 is 10000, INDEX(Input!$AE:$AE,X98) is INDEX(Input!$AE:$AE,1) is Input!AE1, INDEX(Input!$AE:$AE,X99) is INDEX(Input!$AE:$AE,10000) is Input!AE10000, so INDEX(Input!$AE:$AE,X98):INDEX(Input!$AE:$AE,X99) is Input!AE1:AE10000.

Originally Posted by hrlngrv

If you define the name Input_ALL referring to =Input!$1:$1048576, and the name ColumnLetters referring to =SUBSTITUTE(ADDRESS(1,COLUMN(Input_ALL),4),1,""), and you had AE in cell X97, 1 in X98 and 10000 in X99 in another worksheet, then in that same other worksheet,

Formula:

=SUMPRODUCT(--(MOD(ROW(INDEX(Input_ALL,X98,0):INDEX(Input_ALL,X99,0)),15)=0),INDEX(INDEX(Input_ALL,X98,0):INDEX(Input_ALL,X99,0),0,MATCH(X97,ColumnLetters,0)))

would give you the same result as the array formula you show.

I'll be more direct: in the long run you want to avoid INDIRECT and OFFSET when possible. This is a situation in which it's relatively easy to avoid using them.

Simpler than the formula above would be

Formula:

=SUMPRODUCT(--(MOD(ROW(INDEX(Input!$AE:$AE,X98):INDEX(Input!$AE:$AE,X99)),15)=0),INDEX(Input!$AE:$AE,X98):INDEX(Input!$AE:$AE,X99))

Note: when X98 is 1 and X99 is 10000, INDEX(Input!$AE:$AE,X98) is INDEX(Input!$AE:$AE,1) is Input!AE1, INDEX(Input!$AE:$AE,X99) is INDEX(Input!$AE:$AE,10000) is Input!AE10000, so INDEX(Input!$AE:$AE,X98):INDEX(Input!$AE:$AE,X99) is Input!AE1:AE10000.

Hey thanks! That smiplified formula (using SUMPRODUCT as a non-array formula instead of the array formula I had been using) did serve the same purpose and with the added benefit of not having to be an array formula.

I haven't given the first portion of your suggestion a try yet, as it uses some features I'm not yet familiar with (for example defining names, as well as the SUBSTITUTE and ADDRESS functions), but I'll play around with those to get acquainted and give it a go.

There could be a minor snag in using Names, as the workbook also needs to be portable over to Google Sheets -- which I'm sure isn't within the scope of these forums, since they're for Excel users and not Google Sheets -- but I think you've really helped point me in a good direction.

Thanks so muche for the help everyone!