a formula challlenge ... need some help

[Matt]

I want to use the SUM formula where one of the inputs is
variable, e.g. =SUM(ADDRESS(10,E2-Assumptions!D20):E10)

excel won't let do it this way

any ideas?

any help greatly appreciated
thanks
Matt

 

[Myrna Larson]

Try INDIRECT

=SUM(INDIRECT(ADDRESS(10,E2-Assumptions!D20)&":E10"))

 

[Matt]

That's great, thanks

Another quick question ... how do make E10 variable when I
drag the formula??

Thanks

-----Original Message-----
Try INDIRECT

=SUM(INDIRECT(ADDRESS(10,E2-Assumptions!D20)&":E10"))

 

[Leo Heuser]

Or

=SUM(INDIRECT(ADDRESS(10,E2-Assumptions!D20)):E10)

and as no reference is inside a string, it will update
properly, if the formula is copied etc.


--
Best Regards
Leo Heuser
MVP Excel

Followup to newsgroup only please.

Try INDIRECT

=SUM(INDIRECT(ADDRESS(10,E2-Assumptions!D20)&":E10"))