coasterreal
New Member
- Joined
- Jul 5, 2011
- Messages
- 10
In the attached workbook I created a calendar that uses an export of data from our Vacation Database. Our Team Leaders and Employees right now use an Access Database client to submit their vacation time and I approve, deny, whatever the team leader needs. Right now their only view of all of their requests is in spreadsheet form. They can filter on a host of options, but overall looking at a datasheet view of things that should be in calendar view, its not the easiest way to look at your time. I created this based on various things I found on the net and works well enough untill we get an upgrade to one of our systems (will render the Database obsolete)
Look down to B37 in the January Tab. Notice how its got the 1/0 and not blank? Now look at the same cell in the February tab. Literally the only difference in the code is 1 number, the number that denotes which month to reference against my table of data. Im using the MONTH Function and I have a feeling that thats my problem. Every other month works fine. I only included Jan and Feb as a reference, but literally in B37 in both sheets, there is 1 number difference.
Seems like when using the MONTH Function and using 1 for January, it negates my IFERROR function and thus the cells fill with the error.
Let me explain how the sheet works. You will see a blueish gray box next to red text. If you click in there you get a drop down menu. Select either Canada or Customer Support (nothing else has data) When NOTHING is selected, down at the bottom in the "Notes" area, it should be completely blank. When someone selects a Team from that box, it will populate the team members requests, approved or pending into the calendar. At the bottom, their notes for each request off populate as well. This works perfectly on every other sheet than January.
Im using the MONTH function to take data, only return values within that month, then return certain cells in that row. Sure some of you guru's see that already. I have tried several different ways to make this work, nothing works so far that I tried - though I could have gotten it wrong.
Essentially, when the box that you select the team from is empty, the calendar and notes field should be empty (like February). Once you select a team, it will fill in the team members and notes at the bottom. May seem like a small gripe that the only issue is the IFERROR on January, but it makes my workbook look unfinished. As a perfectionist, its driving me insane and I am supposed to send this out to all the team members today, lol.
Any and all help will be greatly appreciated.
-- JUST now realizing I cant attach the Excel workbook. My work also prevents me from accessing sharing sites. Maybe someone can help me using that code. If not, I will post from home tonight.
-Anthony
Look down to B37 in the January Tab. Notice how its got the 1/0 and not blank? Now look at the same cell in the February tab. Literally the only difference in the code is 1 number, the number that denotes which month to reference against my table of data. Im using the MONTH Function and I have a feeling that thats my problem. Every other month works fine. I only included Jan and Feb as a reference, but literally in B37 in both sheets, there is 1 number difference.
Seems like when using the MONTH Function and using 1 for January, it negates my IFERROR function and thus the cells fill with the error.
Let me explain how the sheet works. You will see a blueish gray box next to red text. If you click in there you get a drop down menu. Select either Canada or Customer Support (nothing else has data) When NOTHING is selected, down at the bottom in the "Notes" area, it should be completely blank. When someone selects a Team from that box, it will populate the team members requests, approved or pending into the calendar. At the bottom, their notes for each request off populate as well. This works perfectly on every other sheet than January.
Code:
January Code:
=IFERROR(INDEX(DATA!$A$2:$G$9794, SMALL(IF((MONTH(DATA!$A$2:$A$9794)=1)*($C$2=DATA!$B$2:$B$9794), ROW(DATA!$A$2:$A$9794)-MIN(ROW(DATA!$A$2:$A$9794))+1, ""), ROW(C1)),COLUMN($A$1)),"")
February Code:
=IFERROR(INDEX(DATA!$A$2:$G$9794, SMALL(IF((MONTH(DATA!$A$2:$A$9794)=2)*($C$2=DATA!$B$2:$B$9794), ROW(DATA!$A$2:$A$9794)-MIN(ROW(DATA!$A$2:$A$9794))+1, ""), ROW(C1)),COLUMN($A$1)),"")Im using the MONTH function to take data, only return values within that month, then return certain cells in that row. Sure some of you guru's see that already. I have tried several different ways to make this work, nothing works so far that I tried - though I could have gotten it wrong.
Essentially, when the box that you select the team from is empty, the calendar and notes field should be empty (like February). Once you select a team, it will fill in the team members and notes at the bottom. May seem like a small gripe that the only issue is the IFERROR on January, but it makes my workbook look unfinished. As a perfectionist, its driving me insane and I am supposed to send this out to all the team members today, lol.
Any and all help will be greatly appreciated.
-- JUST now realizing I cant attach the Excel workbook. My work also prevents me from accessing sharing sites. Maybe someone can help me using that code. If not, I will post from home tonight.
-Anthony
Last edited:
