SUM using results from an Adress-formula

[Langbraten]

Is there any (somewhat) simple way to make the the SUM-function operate with the text-strings returned by the ADDRESS-function?

Example:

[Cells C2] = 16
[Cells C3] = 35
[Cells D7] = 4

In my application C2,C3 are input cells were the user selects wich rows he/she is to incude in the summary.

[Cell D2] = ADRESS($C$2;D$7;4) which returns "D16"
[Cell D3] = ADRESS($C$3;D$7;4) and the result is "D35"

Then in [Cell D4] I want to use SUM(D2;D3) and have it return the sum of all cells from D16 to D35. In other words i want my "SUM(D2;D3)" act as if it contained the formula "SUM(D16;D35)".

But since the result of the ADDRESS-funciton is a textstring it doesn't work.
Is there any way to make the SUM-formula use the contents of cell D2 and D3 as cellrefenrences instead of text. Or is there any other way to achive the result I want.

The point is to have a user interface were the user can select how many rows should be included in the summary and then that selection is "transfered" to the same two cells and those are used to get the result.

[Pete_UK]

Try the INDIRECT( ) function - this should do what you want. Excel Help
has details.

Hope this helps.

Pete

[Bernard Liengme]

This will do it =SUM(INDIRECT(D2):INDIRECT(D3))

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Langbraten" <[email protected]> wrote
in message news:[email protected]...
>
> Is there any (somewhat) simple way to make the the SUM-function operate
> with the text-strings returned by the ADDRESS-function?
>
> Example:
>
> [Cells C2] = 16
> [Cells C3] = 35
> [Cells D7] = 4
>
> In my application C2,C3 are input cells were the user selects wich rows
> he/she is to incude in the summary.
>
> [Cell D2] = ADRESS($C$2;D$7;4) which returns "D16"
> [Cell D3] = ADRESS($C$3;D$7;4) and the result is "D35"
>
> Then in [Cell D4] I want to use SUM(D2;D3) and have it return the sum
> of all cells from D16 to D35. In other words i want my "SUM(D2;D3)" act
> as if it contained the formula "SUM(D16;D35)".
>
> But since the result of the ADDRESS-funciton is a textstring it doesn't
> work.
> Is there any way to make the SUM-formula use the contents of cell D2
> and D3 as cellrefenrences instead of text. Or is there any other way to
> achive the result I want.
>
> The point is to have a user interface were the user can select how many
> rows should be included in the summary and then that selection is
> "transfered" to the same two cells and those are used to get the
> result.
>
>
> --
> Langbraten
> ------------------------------------------------------------------------
> Langbraten's Profile:
> http://www.excelforum.com/member.php...o&userid=32980
> View this thread: http://www.excelforum.com/showthread...hreadid=528096
>

[Langbraten]

Thanks a bunch!!!!! That did the trick!!!

I have tried INDIRECT before but it didn't succeed. I now realize why; by mistake I used ";" instead of ":" and thus made it SUM only the two cells D16 and D35 from my example below.

Now I'm gonna rule my project