dynamically building references to named ranges

[kadowd]

OK. I've searched and don't see anything quite like what I'm trying to
do.
And in composing this and trying a few more things I've solved the
problem, but thought I'd post it in any case.

In a nutshell, I have created named ranges for each staff member in a
calendar sort of workbook to track staff time of various sorts (
vacation, training, etc... ).
I have named the ranges in a standard way,
fname_Linitial_YY

Now, I'd like to use these ranges in a simple "dashboard", which will
report on the totals available and used, for each of the types of "out
time" for each employee.

In the dashboard sheet I also show Firstname and Lastname. What I am
trying to do in my countif() functions, is to reference the named
range, but to use the values in the firstname and lastname columns to
"build" the name of the named range.

So if Col B is firstname, Col C is lastname, then what I should be able
to use is:

=CONCATENATE(B7,"_",LEFT(C7,1),"_06")
which would yield
Jack_S_06 for Jack Smith, for instance.

the complete countif would be
=COUNTIF(CONCATENATE(B7,"_",LEFT(C7,1),"_06"),E$1)
**NB: row 1 contains the reference text I'm searching for in the named
range

This does not work, even if I make the string more explicit, to
indicate that the named range is in a different worksheet within the
workbook. as below...
=CONCATENATE("'CS_DB_2006'!",B7,"_",LEFT(C7,1),"_06")

So then I tried to nest the "CONCATENATE()" within an INDIRECT()
function. Still NG.

Finally, i redid the concatenate that builds the string, to remove the
worksheet reference [so back to CONCATENATE(B7,"_",LEFT(C7,1),"_06") ]

and now it functions properly.
=COUNTIF(INDIRECT(A4,FALSE),E$1)

dk

 

[pinmaster]

Try:

INDIRECT(B7&"_"&LEFT(C7,1)&"_06")
put this in place of where your named ranges would be in the formula.


HTH
JG