specifying named range with a lookup

[TSW632]

In this sumif below, the first argument: "Budg_Project_Dept_Acct", is
a named range - more specifically a "dynamic named formula" where the
range is on a sheet containing a budget database. The formula works
fine as is, but I want to have that range name constructed instead
like: =SUMIF("Budg_"&(vlookup(DetailChoice,DetailOptions,2,false)),$H
$35&"_"&$D50,JAN_Bud)

I can't get it to work. Anyone know if it's do-able?


=SUMIF(Budg_Project_Dept_Acct,$H$35&"_"&$D50,JAN_Bud)

 

[Pete_UK]

You might like to try this:

=SUMIF(INDIRECT("Budg_"&vlookup(DetailChoice,DetailOptions,2,false) ),
$H$35&"_"&$D50,JAN_Bud)

All one formula - be wary of spurious line-wraps.

Hope this helps.

Pete

 

[TSW632]

You might like to try this:

=SUMIF(INDIRECT("Budg_"&vlookup(DetailChoice,DetailOptions,2,false) ),
$H$35&"_"&$D50,JAN_Bud)

All one formula - be wary of spurious line-wraps.

Hope this helps.

Pete





- Show quoted text -

Ah. I thought that indirect function might be part of the fix, but I
couldn't get there, having only used it for the first time today in
that dynamic range. Thanks. I'll give it a shot and let you know.

Troy

 

[TSW632]

Ah. I thought that indirect function might be part of the fix, but I
couldn't get there, having only used it for the first time today in
that dynamic range. Thanks. I'll give it a shot and let you know.

Troy- Hide quoted text -

- Show quoted text -

Didn't work.

The dynamic range Budg_Project_Dept_Acct is defined as
=OFFSET('Data Budget'!$AA$23,0,0,COUNTA('Data Budget'!$AA:$AA),1)

In evaluating the #REF! error resulting from
=INDIRECT("Budg_"&VLOOKUP(DetailChoice,DetailOptions,2,FALSE)), shows
that the INDIRECT function yields the text of the name of the dynamic
named formula, but it appears that the dynamic range is not being
recognized. I searched the group on dynamic and indirect, and the only
solution I found would not work in my case, as the range I'm pointing
to contains criteria rather than values to sum.