a formula challlenge ... need some help

Matt · Aug 6, 2003

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 · Aug 6, 2003

Try INDIRECT

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

Matt · Aug 6, 2003

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 · Aug 6, 2003

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.

Myrna Larson said:
Try INDIRECT

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