How can I use a UDF in a SUM Function?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

MrFez - 01 May 2008 02:53 GMT

I have a UDF (GetLastColNum) which returns the column number of the last
column that has data.

I want to be able to sum all the values in a row from column RC[1] to column
RC[X] where X is the number returned by the UDF. And I want the total to be
updated each time a new column with data is added/removed (ie: X gets
increased/decreased automatically).

Does anyone know how to do this?

Any help is greatly appreciated and I thank you in advance.

Reply to this Message

Dave - 01 May 2008 03:28 GMT

Hi,
If you use a dynamic named range, you wouldn't need a macro.
If your values are in Row 1, your named Range formula would be:
=OFFSET(Sheet2!A1,0,0,1,COUNT(Sheet2!1:1))
Then put this into another cell:
=SUM(NamedRange)

You can add as many numbers as you like to the range, and the Sum will
always include them.
Regards - Dave.

> I have a UDF (GetLastColNum) which returns the column number of the last
> column that has data.
[quoted text clipped - 7 lines]
>
> Any help is greatly appreciated and I thank you in advance.

Reply to this Message

Bob Phillips - 01 May 2008 08:52 GMT

something like

=SUM(A1:INDEX(A:A,GetLastColNum(A:A))

Signature

HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

>I have a UDF (GetLastColNum) which returns the column number of the last
> column that has data.
[quoted text clipped - 9 lines]
>
> Any help is greatly appreciated and I thank you in advance.

Reply to this Message