Point 1 was solved, by omitting the quotation marks. Now I have a follow question, and thought it would be helpful to include what I have been working with as a reference. Point 2 holds my actual question.
1) I have created a small macro that allows me to change the date and have this be reflected in a cell, i.e. I press the spinner button up the date counts up, I press the spinner button down, the date goes down (by one day at a time). This is all returned in cell b4, with the date being displayed as xx/xx/xxxx. What I am trying to do is use the date as one criteria and a name as another to pull back data from another tab.
For example, on my "Dashboard" tab I have a list of names in column A, and my date selection being shown in B4 (let's say I have it set to show the date as today).
In another tab called "Data" I have the information I need to pull back, Column A has the dates the data occurred, column B the name of the person who the data belongs to, and in column C the data itself, which for this sake we will say is the number of calls that person took that day.
Now back to my "Dashboard" tab, in cell B2 I would like to have excel look at the name in A2, and the date in B4(remember this is my date selector formula run by a macro) and pull back the matching calls taken in my "Data" tab. Essentially Excel would look at the name and date as the criteria, then go to the data tab, find that matching criteria and pull back the calls taken. This way I can use my spinner buttons to quickly see how many calls people are taking each day.
I've tried using a sumifs formula, and an index formula, but nothing seems to be working.
Here is the sumifs formula I am using with no avail:
=SUMIFS(Data!$C$2:$C$86,Data!A2:$A$86,"&Dashboard!B4",Data!$B$2:$B$86,Dashboard!A23)
2) Now Let's say I want to pull back a weekly average with the same scenario as before. I do have a weekly option for my date spinner that will display a week range in two cells, i.e. cell p2 contains 7/22/2014 and p3 contains 7/28/2014. How could I pull back the average amount of calls that person took in a week with that specified date range?
This time Excel would need to look at the persons name in column A of my "Dashboard" then look at the date range as specified in cell p2 and p3, then go to my "data" tab. From here Excel would need to find all of the occurrences of that persons name in the week range, total the amount of calls that they took (in column C) and divide it by how ever many times their name appears in that week. I would assume an averageifs formula, I'm just not sure how to write it with a date range?
Bookmarks