Dynamic Named Range

L

Len

Hi,

In excel vba, how to set dynamic named range so that it can use in
sumif excel formula

E.g.

=SUMIF(Acct, "Total*", xxx)-SUMIF(Acct, "Expenses*",yyy)*-2

Acct refer to named range in column A
xxx refer to dynamic named range in column C
yyy refer to dynamic named range in column D

Can anyone help and thanks in advance

Regards
Len
 
R

Roger Govier

Hi Len

If Acct has been defined and is in column A, then range for column C would
be
Offset(Acct, 0, 2)
and for column D would be
Offset(Acct, 0, 3)

SUMIF(Acct, "Total*", Offset(Acct, 0, 2))-SUMIF(Acct,
"Expenses*",Offset(Acct, 0, 3))*-2

--
Regards
Roger Govier

Len said:
Hi,

In excel vba, how to set dynamic named range so that it can use in
sumif excel formula

E.g.

=SUMIF(Acct, "Total*", xxx)-SUMIF(Acct, "Expenses*",yyy)*-2

Acct refer to named range in column A
xxx refer to dynamic named range in column C
yyy refer to dynamic named range in column D

Can anyone help and thanks in advance

Regards
Len

__________ Information from ESET Smart Security, version of virus
signature database 4833 (20100203) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4833 (20100203) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 
B

Bob Phillips

LastRow = Cells(Rows.Count, "A")>End(xlUp).Row

Range(Range("A2"), Cells(LastRow, "A")).Name = "Acct"

HTH

Bob
 
B

Bob Phillips

That > should be a dot (.)

Bob

Bob Phillips said:
LastRow = Cells(Rows.Count, "A")>End(xlUp).Row

Range(Range("A2"), Cells(LastRow, "A")).Name = "Acct"

HTH

Bob
 
L

Len

Hi Roger, Bob

Thanks a lot your formula and both work great!.......



Best Regards
Len
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top