If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
dynamically building references to named ranges
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),"_0 6") 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 |
#2
|
|||
|
|||
dynamically building references to named ranges
Try: INDIRECT(B7&"_"&LEFT(C7,1)&"_06") put this in place of where your named ranges would be in the formula. HTH JG -- pinmaster ------------------------------------------------------------------------ pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261 View this thread: http://www.excelforum.com/showthread...hreadid=497715 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Like 123, allow named ranges, and print named ranges | WP | General Discussion | 1 | April 8th, 2005 06:07 PM |
Sum using INDIRECT & Dynamic Named Ranges | steveEx | Worksheet Functions | 5 | September 25th, 2004 06:43 PM |
Formulas with named ranges with 2 or more range areas | agarwaldvk | Worksheet Functions | 1 | September 7th, 2004 07:33 AM |
Named Ranges | Nick1966 | New Users | 2 | August 10th, 2004 11:54 AM |
Using Named Ranges with Data validation | Kevin | General Discussion | 3 | June 20th, 2004 10:34 PM |