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,
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
Hi Charlot,
Yes, Ctrl+D works fine. Alternatively you may select your cells with the formulas in the first row of the range and drag them down till end of the range.
If you work with the table, not range, your formulas will be autofilled for all added to the table rows. And if for some reason you'd like to change the formula it's enough to do that for any one cell, changes will be populated to all other rows automatically.
Dear Sergei
I come back to this after some time.I realised that working with tables facilitates matters a lot (at least in this case).
So, I created an excel sheet on one drive and then inserted a form in the sheet. I needed to do this so that the excel sheet is online, accessible to others and automatically updated. I managed to insert the formulas I needed and because it is a table, I can filter data to my specific needs. Fairly simple actually.
The data is being collected in a sheet called FORM1. I need a new sheet which shows a column with a name, total time used, and total time left from a given. I copied the formula from your last example but I cannot make it work:
=IFERROR(LOOKUP(2,1/(COUNTIF($A$12:A12,LeavesLog[Name])=0),LeavesLog[Name]),"")
Not sure where I am going wrong and any further help is appreciated.
Thanks
CharlotHi Charlot,
Perhaps that due to references - in the formula A12 shall be the cell right before your list, other words the reference on the header of that column. To avoid direct reference you may use
=IFERROR(LOOKUP(2,1/(COUNTIF(LeavesCollected[[#Headers],[Name]]:OFFSET([@Name],-1,0),LeavesLog[Name])=0),LeavesLog[Name]),"")
where LeavesCollected is the name of your table.
Attached.