Free Microsoft Excel 2013 Quick Reference

- Attendance formula
- Employee attendance tracker using COUNTIF
- Edit formulas to display the required range
- Count attendance of each unit per session
- Formulas: moving 6 week average
- Formula that Evaluates Dates with Growing Chart
- Formulas : Attendance problem
- Formula which can calculate how many days in a month and create a column for each day
- Attendance sheet-copy formula
- Attendance form-copy formula
- Copy And Paste Columns Based On Formula
- Not sure - but help with formula
- Controls for Attendance sheet
- Difficult formula advice pls
- How do i count in excel? a formula for 2 colomns
- Stop Formula Column Reference Changing On Insert But Not Row Reference
- If Formula assistance
- Attendance Tracking:
- Formula for random seating
- Evaluate in VBA using Array Formula

I am having a very hard time with the following:

This is a sheet to track attendance

Column A = codes

Column B = names

Column C-BN = Dates of the year

I need to find out how many people attended that have a specific code listed in column A

So, if John Doe has a specific code that I am looking for and he is markes as Yes in column H for attending, as well as Jane Doe and so one, I need to find the running total.

=COUNTIF(January!E4:AI4,"ML").=COUNTIF(January!E4:AI4,"ML")but this formula is only caluculating only for january and i wanted to calculate for whole year.Leave details are on first sheet Shows number of maximum number of days an employee can take what leave. I am attached the file i prepared please let me know where i am making mistake. Thanks in advance.

I'm facing problems doing up the formulas for the PreSearch tab, upper and lower range month columns (columns I &J). This current database is built to record training workshops that the employees have attended. The formulas should bring about the same results as column L & M (upper and lower year columns). I.e. to ask SearchEngine macro to return the data in the required range.

For example, I want to know the employees' data for month range of Jan to Apr, it should give me data in the SearchResults for Jan to Apr only and not the rest of the months. So far I've only figured out that the formulas for "Year" range works because it is a self keyed in range while the month range is based on a drop down box linked to Miscellaneous tab. However, I do not know how may I edit the formuals so that it functions as intended. Please advise. Thanks.

I've cross-post to http://www.excelforum.com/search.php?searchid=6378 as I could not upload the file here. In the meanwhile, I'll try my best to upload a sample file again.

Regd,

Kay Yoke

I tried =countif(range,"12WT") which I guess is wrong because it only counts the number of times the word 12WT appears. It doesn't count the number of time a person from 12WT attended the session which is what I'm looking for. How do I go about getting the percentage of people that attended from each unit?

For all the units that didn't attend, is there a formula that would list the units that didn't attend according to the session number? For instance for session one, someone for EC, 9WT, didn't attend. Is there a way that the end result for the formula could be: EC,9WT. Or, does the result for every formula always have to be numeric?

Sub UpdateSheets()

Dim myrng As Range, ThisWeek As Date, EmpName As String, c As Range

Dim reg, hol, vac, sic

Sheets("Week End").Select

ThisWeek = Range("B2").Value

Set myrng = Worksheets("Week End").Range("a7", Range("A65536").End(xlUp))

For Each cell In myrng

EmpName = cell.Value

reg = cell.Offset(0, 1).Value

vac = cell.Offset(0, 2).Value

sic = cell.Offset(0, 3).Value

ove = cell.Offset(0, 4).Value

oth = cell.Offset(0, 5).Value

hol = cell.Offset(0, 6).Value

On Error GoTo NewSheet

Sheets(EmpName).Select

With Range("A:A")

Set c = .Find(ThisWeek, LookIn:=xlFormulas)

If Not c Is Nothing Then

MsgBox ("Weekly Data for " & ThisWeek & " has already been updated.")

GoTo Finish

End If

End With

Range("A65536").End(xlUp).Select

FillInData:

ActiveCell.Offset(1, 0).Value = ThisWeek

ActiveCell.Offset(1, 0).NumberFormat = "mm/dd/yy"

ActiveCell.Offset(1, 1).Value = reg

ActiveCell.Offset(1, 2).Value = vac

ActiveCell.Offset(1, 3).Value = sic

ActiveCell.Offset(1, 4).Value = ove

ActiveCell.Offset(1, 5).Value = oth

ActiveCell.Offset(1, 6).Value = hol

ActiveCell.Offset(1, 7).FormulaR1C1 = "=SUM(RC[-6]:RC[-1])"

Sheets("Week End").Select

Next cell

GoTo Finish

NewSheet:

Set NewSheet = Worksheets.Add

NewSheet.Name = EmpName

Sheets(EmpName).Select

Range("A1").Value = EmpName

Range("A1").Font.Size = 18

Range("A3").Value = "Week Ending"

Columns("A:A").ColumnWidth = 14

Range("b3").Value = "Regular"

Range("g3").Value = "Holiday"

Range("c3").Value = "Vacation"

Range("d3").Value = "Sick"

Range("e3").Value = "Overtime"

Range("f3").Value = "Other"

Range("h3").Value = "Total"

Columns("B:H").HorizontalAlignment = xlCenter

Range("A3:H3").Font.Bold = True

Range("A4").Select

Resume FillInData

Finish:

Sheets("Week End").Select

Range("A1").Select

End Sub

I am not sure if I can add this function into this macro or create 70+ macros (1 for each employee) on the individual worksheets?

Any ideas or suggestions or help would be greatly appreciated.

Thanks

Drops points accumulated more than 365 days agoDrops 2 points if there has been no infractions in the past 30 days.

I've got some of it figured out. Here is what you will find in the demo data posted:

Dates in Column 7 are just dummy dates, used to evaluate if the formula is working.

Accumulated Points (Column H) is the total number of points that person has accumulated since counting began.

Expired Points (Column J) are points that were accumulated more than 365 days ago.You may notice that rows 3 and 4 are missing. These rows contain a "Begin Date" and "End Date" for a formula that only considers points accumulated more than 365 days ago.

Active Points is the number of points that employment decisions will be based on, the current, active count

So what is the problem? Right now, "Active Points" is off. It should be showing 3.5 points, instead of the current total of 1.5 points. If you check my formula which is:

=IF(L8>=30,H8-2,H8)-J8

to make it more consumable:

=If('Last Infraction' was 30 or more days ago, subtract 2 from 'Accumulated Points', if not show 'Accumulated Points')-'Expired Points'

It should be showing 3.5 because the last infraction was LESS than 30 days ago, so they don't get to drop 2 points.

I think Excel is evaluating 'Last Infraction' as a number (instead of a date), which is greater than 30, and automatically subtracting 2, regardless of the date so I need help in that regard.

Also, this table will grow with each day. We need the formula to automatically adjust itself to consider all dates up to today/current date. One last request if I may: Is there a way to have two freeze panes? She needs the names of the supervisor and agent to always be visible AND she needs columns I-O always visible. In the middle will be the range of dates from n - Today, which will be growing, which means I-O will always be getting pushed further out. And if its possible to have 'Active Points' show with a background color of Yellow when the amount is >= 8, and Red when the amount is >= 12, that would just be the icing on the cake!

Any help you can provide in this project will be MUCH appreciated, thank you!

***The file was being edited as this post was being created so some of the field/ranges may not exactly match up, please accept my apologies in this regard***

I have a template for grading courses, in the attendance sheet attendance is marked as follows: X = abscence, T = late, H = holiday and N/A is for days that the class doesn't meet. Each student is a row. I need to get the last day that that student attended with this conditions:

Dates range = e1:bi5

Students attendance range is e9:bi9.

The date of the last attendance should appear in bk9. Couldn't add the whole sheet becaus of size but there should be 54 dates, 5 months aproximadely. Thanx just for reading and I hope you can help this is the only detail left on my template.

Here's the sheet:

I’m trying to build a “Leave, Attendance Management” system using Excel”. I am about 40% there so far.

I have a few problems to tackle which I will post in separate treads and link them back to together; so others can hopefully learn from the help I receive (and my mistakes):

The basic structure of the workbook is as follows:

12x main monthly ‘Leave Attendance Record’ worksheets (i.emonths April 2012 through to March 2013). [I will refer to these sheets as LARSheet1 to LARSheet12].

1 x worksheet which holds ‘data validation’ list andvlook up info. [“FormulaListSheet”]

1 x worksheet acting as a database of staff details andpay numbers. [“StaffdBaseSheet”]

1 x summary page which shows staff reaming leaveallocation. [“SummaryLeaveSheet”]

What I’m having problems with at the moment is as follows:

A formula or a macro which works out how many days are present in a given month [starting date of month located in cell B10].

Days 1-27 will be already set up on the sheet. Based upon the monthly value I would like a solution which can calculate how many days are in a month and create a column for days 28, 29, 30 or 31 (depending on the month).

Hopefully I would like it to work like this:

When the user opens a new worksheet, they first entre the month start date in CELL B10. Hopefully this will trigger columns E13:AH22 to update with the relevant week day & date.

I also need the formulas and formatting in cells AH13:AH100 (column for day 27) to copied over automatically.

Can anyone help? Cheers moshjosh!

Sample of sheet setup:

screenshot_LARSheet1.jpg

====== ====== ======== ====== ============= ========== ============ ============ ===== ========= =======

This is a multi-part project which is collectively known as “Leave, Attendance Management” system using Excel” . Other parts are found on other treads. I hope to link the thread solutions together so others who wish to do a similar task can hopefully learn from help I receive (and my mistakes).

The other parts of the system are:

‘Leave attendance record’ (this thread) worksheets (i.e months April 2012 through to March 2013). [I will refer to these sheets as "LARSheet1" to "LARSheet12" ].

‘Data validation’ worksheet which holds list and vlook-up info for formulas. [I will refer to these sheet “FormulaListSheet” ].

‘Database’ worksheet acting as a of staff details database. [ “StaffdBaseSheet” ]

‘Summary reaming leave’ page which shows a summary of staff used leave and calculates reaming leave allocation. [ “SummaryLeaveSheet” ]

http://www.ozgrid.com/forum/showthread.php?t=165474

I have attached a spreadsheet that has 3 worksheets, summary and 2 individual. I am trying to drive the individual sheets from the summary page.

Basically, I enter the week ending and hours worked and the individual sheets would retain the accumulated individual info.

The formula I am using is as follows:

=IF(A5='Week End'!$B$2,'Week End'!$B$4,"")

What I have discovered is that as I change weeks the prior week become blank, just like I told it too!.

Is there a copy command I can use in a formula to copy or some other way to save the prior weeks info? I thought about Lookup but would run across the same problem.

Any help would be appreciated,

Thanks

Beeker

I am simply trying to create a attendance record file. On the main sheet the week ending date is input and then a list of 75 employee names, input with Hrs, OT hrs blah blah. Then, I have created seperate worksheets for each respective employee that would maintain each week end total. I have been use the following formula:

IF(A5='Week End'!$B$2,'Week End'!$E$4,"") (see attached).

What I have found when the week end change the formula becomes "False" and the cell is empty. So I am losing my accumulation of prior weeks.

Questions:

Is the "IF" statement the easiest for this

and

How do I save the prior weeks info.

I have attached a sample

Thanks

Beeker

I apologise in advance for being dim.

I need to copy certain columns from one sheet to another sheet based on a formlua on the date.

So, on 'Master Sheet' I have a column called 'Date'.

I would like a formula, or macro, which does the following.

If date (column D) is over 2 weeks old (today's date - 14 days) then copy columns A, D, S and T to another work sheet called 'Not Attended' and paste them into columns A,B,C, and D.

Is this possible?

Many thanks in advance.

AL

My girlfriend is a private music teacher. I am trying to help her get a bit more organized etc.

So - I created an Excel workbook to help her keep the kids info, the parent info and keep track of how much people owe, how much they paid, and the attendance of the kids in lessons.

On a second workbook inside the same file - I created an invoice. I have linked cells together to automate as many fields as possible. Now I would like to automate some of the calculations etc.

So - here is my problem. The attendace section is the most logical place to link the number of hours from. She actually tracks hours and it totals up at the end of the month.

But - is there a way to have the invoice sheet look at the date and pull in the hours from the dates belonging to the same month as the invoice? I guess you would have to reference the cell with the total amount of hours to the date cell - and then have the invoice sheet look at the invoice date - and reference over to the currect date in the 1st sheet.

Make sense? LOL

Ideally - I would like to make the invoice essentially generate itself from the info provided on the main workbook page.

Any help - would be much appreciated.

I am creating a attendance tracker. I need your help for the completion.

I am looking for a control which can track the changes. To be more specific, If a person updates the data in the sheet once, it should be locked. The next time, he/she should not be able to update it. It should be possible only with a controller who can in turn give a password and then make the necessary changes. I am not using any macros and it runs only on formulas. Please help me in this for the completion. I am already breaking my head for this.

Your help is appreciated,

Thanks in advance,

Praveen

For the example, please see the original sheet and notice rows 19-23. It should keep row 22 and delete the other 4 rows with the same entry in column a.

If anyone can figrue this out, as a teacher at the school I attend cannot, you would greatly be respected, admired, and worshipped!

Thanxz

About half of the class are seniors the others juniors. In the fist colomn there's a number(1-27) the second colonm is the rank(senior or junior) and the third colomn is the attendence for a single day(sept. 13) . What formula would i use to calculate how many seniors and how many juniors have attended on a certain date. I need these numbers separately, seniors in one cell and juniors in another.

Maybe there's a way to do it simpler in Access... any ideas?

Thank you in advance

Trying to keep it concise,

I have

A 'Days Attended' cell (N8) and a 'Days Absent' cell (O8).

N8 needs to count the number of "Present" values there are on another worksheet.

The other worksheet has dates across the top and names down the side.

When i use

=COUNTIF("Attendance!C9:Z9", "Present"),

and the next date comes along the formula changes to

=COUNTIF("Attendance!D9:AA9", "Present")

ie. the reference moves a column across - the new date's absent or present is not counted.

Using

=COUNTIF(INDIRECT("Attendance!C9:Z9"), "Present")

is no good because when i add a new name i need the row reference to move down as a row is inserted.

ie. both person's formulas count the same row.

So, my question: I need the columns to stay the same - C:Z (leyway for future dates) and the rows to change as i insert or delete people from the system. Any ideas?

Thanks

C1 contains info for a class that is being scheduled and states if it has been attended or not. To calculate a percentage of completion rate I need C2 to show 100%. What formula should I use?

I have the "countif", "sum", etc. formulas, but I'm having trouble putting them together so that if for example, I missed this day last year, then today those points should roll off and if I missed this same day then these points should be added to the tracker.

Can anyone please help me with this?

There is a 6 day convention, with 240 people attending, & 45 tables that the people attending will be seated at. However, each day of the convention, each person sits at a new table, and each day they have to be seated with entirely new people - they aren't allowed to sit with anyone that they've already been at a table with on any previous day. Not all 45 tables have to be used.

Any thoughts? I've been testing out the formula used in this thread (Post #12):

http://www.mrexcel.com/forum/showthr...repeats&page=2

=SMALL(IF(COUNTIF(B1:$G1,ROW(1:10)-1)=0,ROW(1:10)-1),1+INT(RAND()*(COLUMN()+4)))

posted by barry houdini & PGC.

My problem is figuring out how to adjust it, or if I should utilize something else! Thanks for any help!

Code:

Sub test() Dim counter As Long, LR As Long counter = 5 LR = Sheets(2).Cells(Rows.Count, "D").End(xlUp).Row For b = 2 To LR If Application.WorksheetFunction.CountIf(Range("A:A"), Sheets(2).Range("D" & b).Value) = 0 Then Cells(counter, "A").Value = Sheets(2).Range("D" & b).Value Cells(counter, "C").FormulaArray = "=MIN(IF('" & Sheets(2).Name & "'!$D$2:$D$" & LR & "=A" & counter & ",'" & Sheets(2).Name & "'!$M$2:$M$" & LR & "))" Cells(counter, "D").Value = Evaluate("{=MAX(IF('" & Sheets(2).Name & "'!$D$2:$D$" & LR & "=A" & counter & ",'" & Sheets(2).Name & "'!$M$2:$M$" & LR & "))}") Cells(counter, "K").Value = Application.WorksheetFunction.SumIf(Sheets(2).Range("D:D"), Range("A" & counter).Value, Sheets(2).Range("Z:Z")) Cells(counter, "M").Value = Sheets(2).Range("H" & b).Value Cells(counter, "N").Value = Sheets(2).Range("F" & b).Value counter = counter + 1 End If Next b End SubIf I enter as a formula array which is the red code, it works fine and I get correct results, but there is a formula in the spreadsheet, now I know I could copy and paste values within the macro, but this has to be possible without having to do that? Or is it not possible. The blue code is what i thought I'd try, but it doesn't work.

Here is an example of what the summary sheet looks like even though seeing it probably doesn't help much:

Microsoft Excel - Attends Healthcare1 weeks data supplier test.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutC5C6C7C8=

ABCDEFGHIJKLMN4xpedxÂ LocationLocationÂ IdentifierStartÂ DateÂ Â (MM/DD/YY)Â -Â (includeÂ slashes)EndÂ DateÂ Â (MM/DD/YY)Â -Â (includeÂ slashes)InvoiceÂ dateÂ Â (MM/DD/YY)Â -Â (includeÂ slashes)DateÂ IssuedGrossÂ AmountDiscountÂ AvailableNetÂ AmountCreditÂ InvoiceÂ NumberÂ OriginalÂ AmountÂ Â Â Â Â Â RequestedÂ InvoiceÂ RemarksVendorÂ NumberInternalÂ Â Â Â Â Â Â Â Â Â Â Â Â xpedxÂ JDEÂ #5SaalfeldÂ NWGÂ -Â SeattleÂ 5/5/2008#VALUE!Â Â Â Â Â Â $113.55Â 2837003976MetroÂ NewÂ YorkÂ CommercialÂ 5/7/2008#VALUE!Â Â Â Â Â Â $746.64Â 102357006007HarrisburgÂ 5/5/2008#VALUE!Â Â Â Â Â Â $9,790.54Â 102357006558HarrisburgÂ RedistributionÂ 5/9/2008#VALUE!Â Â Â Â Â Â $839.74Â 10235700659Credit Summary SheetÂ

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box

PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

No luck finding an answer? You could always try Google.