software

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 quot;dashboardquot;, which will
report on the totals available and used, for each of the types of quot;out
timequot; 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
quot;buildquot; 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,quot;_quot;,LEFT(C7,1),quot;_06quot;)
which would yield
Jack_S_06 for Jack Smith, for instance.

the complete countif would be
=COUNTIF(CONCATENATE(B7,quot;_quot;,LEFT(C7,1),quot;_06quot;),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(quot;'CS_DB_2006'!quot;,B7,quot;_quot;,LEFT(C7,1),quot;_0 6quot;)

So then I tried to nest the quot;CONCATENATE()quot; 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,quot;_quot;,LEFT(C7,1),quot;_06quot;) ]

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

dk
Try:

INDIRECT(B7amp;quot;_quot;amp;LEFT(C7,1)amp;quot;_06quot;)
put this in place of where your named ranges would be in the formula.HTH
JG--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: www.excelforum.com/member.php...foamp;userid=6261
View this thread: www.excelforum.com/showthread...hreadid=497715