I have a workbook with 2 worksheets.

Worksheet #1 contains a range of values that I need to use for calculations in worksheet #2.

The range runs from A1:A10.

Is it possible to use a formula that can identify these start and end values without me having to explicitly hard-code them? Additionally, I'd like Excel to name the range once it locates it, so that I can use the name as an input parameter for a subsequent SUMPRODUCT() function.

I'm trying to avoid using VBA, but not sure if what I am trying to do is possible with off-the-shelf formulas.


A dynamic range with a known starting point is easy enough by creating a named range and using the formula:


However if A1 is going to change it becomes:

=INDIRECT("A"&MATCH(INDEX(Sheet1!$A:$A,MATCH(TRUE,INDEX((Sheet1!$A:$A<>0),0),0)),Sheet1!$A:$A,FALSE)&":A"& COUNTA(Sheet1!$A:$A)+MATCH(INDEX(Sheet1!$A:$A,MATCH(TRUE,INDEX((Sheet1!$A:$A<>0),0),0)),Sheet1!$A:$A,FALSE)-1)

again as a named range.

Note that this will only work if there is not other data in the A column other than that of interest.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.