Edward Pamias
asked on
Excel Named Range
All, I need to create a named range for multiple tabs in my workbook, is that possible? Tab names are April, May, June, July etc..... all the way till December.
Are you trying to rename the worksheets or trying to create a named range that spans multiple worksheets?
ASKER
Named range that spans across multiple worksheets.
are the cell addresses on the worksheets identical or different?
It does seem possible. You use a plus sign between the range addresses.
Example: =Sheet1!$B$8:$D$12+Sheet2! $B$5:$C$11
Example: =Sheet1!$B$8:$D$12+Sheet2!
ASKER
All the sheets look like this but of course the sheet names change with the month.
What are you trying to accomplish with the named ranges?
ASKER
In the calendar, I need to count the number of days each of the employees are working per month. On the name sheet I was using a countif formula but that did not work for me.
ASKER
I wanted a named range for all the tabs instead of doing them individually.
Although a named range might span multiple worksheets, an actual RANGE object can only refer to a single worksheet. What's the problem iterating the worksheets? It is simple and fast.
If you're trying to do this with a formula, then the best you can do is to invoke a function to do the worksheet iteration.
If you're trying to do this with a formula, then the best you can do is to invoke a function to do the worksheet iteration.
ASKER
Can you give me an example? Let's say I have John working 4 days a week for the month of April and May and June, How can I count all those days on another sheet? Keep in mind the calendar is on separate tabs. :)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I got that but I need to use a cell reference with the name in there. Is that possible?
I need to use a cell referenceYou can certainly adjust the routine to use a subset of the usedrange of each worksheet. If the calendars are indivicually named, you can iterate those named ranges instead of the worksheets.
I'm guessing about your workbook and your actual needs. Why do you need a cell reference?
ASKER
I have a sheet called names, and on the sheet on the left side column A, I have 37 names. In this workbook I have tabs with months starting with April and ending in December, I need to count how many times the names show up on each of the calendars and display it in column B on the names sheet,
You can substitute a cell reference in place of the string literal I used in my example.
ASKER
I tried the cell reference but it did not work. I see you reference a Sheet3 in the VB code but I do not have one.
I used a standard workbook with three worksheets with their default names. I do not know on which worksheet you plan to put your formula that invokes the function, nor do I know what other worksheets you should ignore. I posted sample code meant to illustrate the solution in some generic environment.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I am using the names worksheet as stated above in comment ending 6073.
Use the UDF in column B on Names Sheet like this...
=GetCountOf(A2)
ASKER
That worked perfectly Neeraj! Thanks Aikimark and Neeraj for all the help.
ASKER
Thanks again !
You're welcome Edward!
What I feel that you should have accepted Aikimark's answer as a solution because I just tweaked his code as per your need. So basically all the work was done by him only.
If possible, raise a request attention and choose the answer again. :)
What I feel that you should have accepted Aikimark's answer as a solution because I just tweaked his code as per your need. So basically all the work was done by him only.
If possible, raise a request attention and choose the answer again. :)
ASKER
I will do that. Thanks!
Thanks. :)
ASKER
Thank you!
ASKER
It was re-opened in error. Closing it again.
@Aikimark
After you reopened the question first time, Edward assigned the points again successfully.
But I think somehow the question was reopened the second time.
After you reopened the question first time, Edward assigned the points again successfully.
But I think somehow the question was reopened the second time.