Setting up Reminders in Excel 2003

[badcompany]

I first want to say "HI!" to all of you members. I usually sneak a peak at this site for some quick help, but this time I am really stuck.

I am trying to create a complex timeline of audit controls for a company. This excel workbook includes three worksheets -- Summary, Status, and Details. Of these three worksheets, I am trying to figure out how to have the Summary sheet display "Overdue" in the Status column by controls that are past due on testing.

The Details sheet is the 'metadata' for the other spreadsheets that includes all the information. In this sheet, I have the months lined up across the top row; For annual controls I merge 12 columns together, semi-annual would be 6 columns wide, and monthly would be a single cell. For each control there is either a Y, N, or Retired (if the control is no longer in use) for each time during the year.

Example: If a semi-annual control was done only during the second half of the year then there would be only a 'Y' in the second of the two cells for the year.

The Status sheet is more high-level than the Details sheet. It displays Pass, Test, or Retired for each month, semi-annual, annual control using an IF statement that depends on the manual entry of Y, N, or Retired on the Details sheet.

The Summary page includes a strict high-level view of the controls. I want to have this page display "Pass" or "Overdue" depending on =today() or =now() or whatever function necessary to take the current date and display which controls do not have a "Pass" or "Retired" in the column for that month.

P.S. I cannot copy and paste this information (security issue), so hopefully I am being as clear as possible. Let me know if I need to ellaborate on something.

Thanks,
bC

 

[jwloon]

Detail
if you have detail on each control with columns A(hidden) that will have the formula to do "=IF(MATCH("x",E3:X3,0)<MONTH(NOW()),IF(D3="","Overdue",""),"")" this will assess if the column of the testing done, which in my case i just put an x in the month it had to be completed by is after the current date and if there is no value in the status column(D).

Column A (hidden with formula)
column B Control name
Column C Type of test(Annual, Semi, Monthly)
column D Status(pass, Failed, Retired or blank)
Column E through P - Month/Year(Jan 2011, Feb 2011...)

Summary
Column A - Status(Pass, Failed, Overdue, Left to test)
column B - =COUNTIF(Detail!$D$2:$D$7,A2) for each status

I don't understand the value of the Status Tab for this example

Hope that helps

 

[badcompany]

Detail Worksheet Contents:

Column A = Control No.
Column B = Department
Column C = Frequency (Annual, Semi-Annual, etc.)
Column D = Key or Non-Key (Type of control)
Column E-J = Description, comments, etc.
Column K-AF = Jan-11 -- Oct-12 (just stopped there for the heck of it)

NOTE: For controls that are tested less frequently than a month, like quarterly, semi-annual, etc., they have merged cells.

Example: I want Summary to use the current date (eg., August 10, 2011) and goto the column for August-2011 on either Detail or Status tabs and check if there are any controls that do not have a pass or retired status for that column. If there are then it will show up on the Status tab as "Needs Testing" or something along those lines.

A | B | C | D | E | F
1) Jul-11 | Aug-11 | Sept-11 | Oct-11 | Nov-11 | Dec-11
2) [Monthly] [Monthly] [Monthly] [Monthly] [Monthly] [Monthly]
3) [---------======-SEMI-ANNUAL CONTROL--------------]

 

[badcompany]

If it cannot be done with merged cells, is there a way to do this without merged cells?

Create an search to start from $A$1 -> Todays Date (August 10 = August = Column B) then $B$1 -> etc...


A | B | C | D | E | F
1) Jul-11 | Aug-11 | Sept-11 | Oct-11 | Nov-11 | Dec-11
2) [Pass] [Pass] [Monthly] [Monthly] [Monthly] [Monthly]
3) [NULL] [Pass] [Monthly] [Monthly] [Monthly] [Monthly]

Therefore Row 2 would Pass since there are only "Pass" in both July and August
Although Row 3 would "Require Testing" since there is a blank/null input for the previous month still.

Is this type of lookup/search/match possible? If so, how would one recommend doing so?

Thanks,
bC

 

[jwloon]

I would recommend that you put the type of test frequency in one field like my example, because your data does not depend on that, it just has to interpret if it was supposed to run in a "month" and if it did was it pass, retire or fail, right?

You could set the frequency to an annual amount(like 1 - annual, 2 Semi-annaually, 4 - Qtr, 12 - Monthly). you could use that to compare to "today" and identify if the test is late or not. But Search/match would seem to have a hard time interpreting what you are asking. I also think the Merging is more complex that you need it.

Are all your annual tests set to be done the same time- Dec and all your Semi Annual June and Dec... or can these be set for different annual/semi annual dates(this goes for Qtrs too)?

You could do something like this:
[f] =if(c2=12,if(MATCH("annual",E3:X3,0)<Month(Now()),if(D2="","Overdue",""),(MATCH("Semi",E3:X3,0)<Month(Now()),if(D2="","Overdue",""),(MATCH("Monthly",E3:X3,0)<Month(Now()),if(D2="","Overdue",""),"")))[f]

 

[badcompany]

I have a formula that searches the matches the current date, using =today(), that is able to find the correct column.

Example: If it's August 10, 2011, it will find the correct column on the Status tab.

B2 = today()

=(HLOOKUP(VLOOKUP($B$2,Dates!$A:$B,2),Detail!$K$1:$AF$1,1))

How can I implement this into a search to do the following:

If the date is August 10, 2011, then it will do the search and find Aug-11 on the Status sheet (using the formula above). Then search for that particular control (maybe another vlookup?) and search that row between Jan-11 and Aug-11 to find anything that is not ="Pass". If it comes up true for anything not ="Pass" then it should display "Needs Testing," otherwise "Pass".

Any ideas?

Thanks,
bC

 

[badcompany]

Are all your annual tests set to be done the same time- Dec and all your Semi Annual June and Dec... or can these be set for different annual/semi annual dates(this goes for Qtrs too)?

<?XML:NAMESPACE PREFIX = Month(Now()),if(D2="","Overdue",""),(MATCH("Semi",E3 /><Month(Now()),if(D2="","Overdue",""),(MATCH("Semi",E3:X3,0)<Month(Now()),if(D2="","Overdue",""),(MATCH("Monthly",E3:X3,0)<Month(Now()),if(D2="","Overdue",""),"")))[f][ p QUOTE]<>

These controls are tested a different times through out the year.</Month(Now()),if(D2="","Overdue",""),(MATCH("Semi",E3:X3,0)<Month(Now()),if(D2="","Overdue",""),(MATCH("Monthly",E3:X3,0)<Month(Now()),if(D2="","Overdue",""),"")))[f][>

 

[jwloon]

sorry, there is more to that formula, but this site is cutting it off and I can't find a way to add good coding.

 

[jwloon]

=IF(MATCH("x",E2:X2,0)<MONTH(NOW())[CODE]< p> [CODE],IF(D2="","Overdue",""),"")[CODE]<MONTH(NOW()),IF(D2="","OVERDUE",""),"") p [f] CODE]< [>

 

[badcompany]

=IF(MATCH("x",E2:X2,0)<MONTH(NOW())[CODE]< p> [CODE],IF(D2="","Overdue",""),"")[CODE]<MONTH(NOW()),IF(D2="","OVERDUE",""),"") p [ CODE]< [f]>[/QUOTE]
 
Where you are placing [CODE], is this where I should place my formula?
 
I'm a little confused by the "x" part as well, what should be here? =today()?