Forum Discussion
Forms, Excel and Formulas
Hi Charlot,
1. In Column E, I need Column D - Column C. When I try to add an array formula, the value does not extend to include all cells in the column.
That could be
=(D3-C3)*24
2. In column H, I need a formula that will deduct 0.5 from the value of Column E if the value of Column G is yes.
As
=E3-(G3="Yes")*0.5
3. Column I provides the week number based on Column B. Not sure if I need this for the following steps.
You may use
=WEEKNUM(B3)
4. In a separate sheet in the same workbook, I need to know how much special leave each member of staff has utilised and how much time remains.
To pick-up unique names use in first cell for names
=IFERROR(LOOKUP(2,1/(COUNTIF($A$2:A2,Sheet1!$A$3:$A$11)=0),Sheet1!$A$3:$A$11),"")
and for hours used=SUMIF(Sheet1!$A$3:$A$11,Sheet2!A3,Sheet1!$H$3:$H$11)
5. In a separate sheet in the same workbook, I need to be able to see who has applied for Special Leave by date and by week. (Hence the need for the week number?)
Could be done with Pivot Table, to correctly show the time the workaround to repeat Start/End columns in General format and add these columns to Pivot Table. Filter weeks by slicer. Rest is formatting. Please see attached.
6. Finally, in a separate sheet in the same workbook, I would like to be able to select a name to see the special leave of just that particular person.
Perhaps similar tp previous one.
Attached.
Hi Charlot,
1. In Column E, I need Column D - Column C. When I try to add an array formula, the value does not extend to include all cells in the column.
That could be
=(D3-C3)*24
2. In column H, I need a formula that will deduct 0.5 from the value of Column E if the value of Column G is yes.
As
=E3-(G3="Yes")*0.5
3. Column I provides the week number based on Column B. Not sure if I need this for the following steps.
You may use
=WEEKNUM(B3)
4. In a separate sheet in the same workbook, I need to know how much special leave each member of staff has utilised and how much time remains.
To pick-up unique names use in first cell for names
=IFERROR(LOOKUP(2,1/(COUNTIF($A$2:A2,Sheet1!$A$3:$A$11)=0),Sheet1!$A$3:$A$11),"")and for hours used
=SUMIF(Sheet1!$A$3:$A$11,Sheet2!A3,Sheet1!$H$3:$H$11)
5. In a separate sheet in the same workbook, I need to be able to see who has applied for Special Leave by date and by week. (Hence the need for the week number?)
Could be done with Pivot Table, to correctly show the time the workaround to repeat Start/End columns in General format and add these columns to Pivot Table. Filter weeks by slicer. Rest is formatting. Please see attached.
6. Finally, in a separate sheet in the same workbook, I would like to be able to select a name to see the special leave of just that particular person.
Perhaps similar tp previous one.
Attached.
Dear Sergei
Thank you so much for your help and for explaining the workings. I appreciate that.
In the following:
4. In a separate sheet in the same workbook, I need to know how much special leave each member of staff has utilised and how much time remains.
To pick-up unique names use in first cell for names
=IFERROR(LOOKUP(2,1/(COUNTIF($A$2:A2,Sheet1!$A$3:$A$11)=0),Sheet1!$A$3:$A$11),"")
and for hours used
=SUMIF(Sheet1!$A$3:$A$11,Sheet2!A3,Sheet1!$H$3:$H$11)
Can I add an if function so that the formula will only calculate leave that is marked as Personal only?
And can pivot tables be made to update automatically? Once the form goes live, staff members will be making requests through the form throughout the scholastic year and ideally, the tables just update themselves.
Thanks again for the support.
Charlot
Hi Charlot,
To sum only Personal leaves you may add one more criteria using SUMIFS
=SUMIFS(Sheet1!$H$3:$H$11,Sheet1!$A$3:$A$11,Sheet2!A3,Sheet1!$F$3:$F$11,"Personal")
You can't update Pivot Table each time new value is added, if only with VBA programming. However, creating the Pivot Table you may check the option Add to data model. When in ribbon click Queries & Connections in Data group, right click on WorkSheetConnection and in properties you may select to update Pivot Table on file opening and/or refresh every few minutes. In any case Refresh All button works.
Another bonus will be you don't need helper columns for the Start/End time in such case.
In general better to organize you data in tables and use structured references for formulas. When you don't depend on your source data range size.
I added some tables in attached.
Dear Sergei
Thanks again for your help.
So essentially, and if I understood correctly, once you have the basic data, in Excel it is easier to work with Pivot Tables rather than more formulas (if this is at all possible).
I will experiment with the Pivot Tables. Might come back to you if you don't mind.
Just one almost silly question. How do you get formulas to automatically work down a column? I found this and it works but was wondering if there is something more straightforward.
- Select the cell containing the formula and press CTRL + SHIFT + DOWN to select the rest of the column ( CTRL + SHIFT + END to select up to the last row where there is data)
- Fill down by pressing CTRL + D.
- Use CTRL + UP to return up
Thanks
Charlot
