Problem with cell references

[MuddyBrain3]

Can anyone tell me why this works:

=SUM(INDEX('Sales Record'!B$2:[B]M$42[/B], 0, ROWS(D$3:D3)))

but this doesn't work:

=SUM(INDEX('Sales Record'!B$2:[B]INDIRECT($D$2)[/B], 0, ROWS(D$3:D3)))

where $D$2 in the active sheet contains the text value reference M$42

I receive a VALUE error.

 

[Weaver]

Re: Using cell references in a a formula

I think it would need to be:

=SUM(INDEX(INDIRECT("'Sales Record'!B$2:" & $D$2), 0, ROWS(D$3:D3)))

 

[MuddyBrain3]

Re: Using cell references in a a formula

Brilliant! Thanks so much!