SOLVED

Forms, Excel and Formulas

Copper Contributor

Dear Experts


I have a google form which I am trying to migrate to Excel and need some help with formulas and functions. I am not an expert but can handle the basics. However, I am not sure where to start with this and my attempts do not seem to be producing any decent results. 

 

The context is a school in which employees can apply for specific amounts of time off during a day. Some of this time of comes out of x amount of hours of "special leave" whereas if it is medical, then it doesn't. There is also a 30 minute break which needs to be taken into account, so that if the time off bridges the 30 minute break, this is not deducted from said special leave.  This is an example of the excel sheet (as created from a Form). 

 

ABCDEFGHI
Name & SurnameDateStart TimeEnd TimeHoursReason for Special LeaveIncludes BreakActual TimeWeek
John08/11/201708:15:0014:15:006PersonalYes5.545
Mary09/11/201708:15:0014:15:006PersonalYes5.545
Peter26/09/201708:15:0010:00:001.75MedicalNo039
Agnes02/10/201708:15:0012:00:003.75PersonalNo3.7540
Paola22/09/201710:15:0012:00:001.75PersonalNo1.7538
John25/09/201708:15:0009:15:001PersonalNo139
Peter20/09/201710:30:0011:30:001PersonalNo138
Samuel25/09/201708:15:0010:30:002.25PersonalNo2.2539
Stan27/09/201711:45:0014:00:002.25MedicalYes039

 

I need several things: 

 

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. 

 

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. 

 

3. Column I provides the week number based on Column B. Not sure if I need this for the following steps. 

 

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. 

 

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?)

 

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. 

 

I have attached an example file. 

 

I would really appreciate some help with this. 

 

Thank you. 

 

Charlot Cassar

16 Replies
best response confirmed by Charlot Cassar (Copper Contributor)
Solution

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. 

 

  1. 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)
  2. Fill down by pressing CTRL + D.
  3. 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


Charlot

Hi 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.

 

 

Thanks for your prompt reply. 

 

I tried that but I am not understanding the syntax so still getting lost. 

 

Attaching a copy of the actual spreadsheet but have changed the names. 

 

In Sheet 1 I need the name, which should be simple enough. Then the total of actual hours used (only if personal) and then a simple formula which would pick this value and deduct it from a given number - 31.5. 

 

First, I'd recommend to give your tables more human friendly names instead of Table1, Table2, etc.

 

To rename stay on any cell within the table, after that type desired name in that box in the ribbon

image.png

and hit enter:

image.png

If you rename your tables later new names automatically substitute old ones in formulas

After that enter in Name column of the second sheet

=IFNA(LOOKUP(2,1/(COUNTIF(LeavesCollected[[#Headers],[Name]]:OFFSET([@Name],-1,0),LeavesLog[Name])=0),LeavesLog[Name]),"")

I change IFERROR on IFNA compare to previous formula. In  another one

=SUMIFS(LeavesLog[Actual],LeavesLog[Name],[@Name],LeavesLog[Reason],"Personal")

are your actual column names.

Attached

Hi again Sergei and thanks (again) for your help. 

 

The problem seems to be the fact that I am working on excel online. It will not allow me to rename a table. I am presuming that the table name is Table1 but when I use your formula, changing the name of the table to Table1 nothing happens. 

 

I tried to think round it - therefore, download the excel workbook, make the changes I need and then re-upload online. However, the problem then is that I cannot link the existing form to this new workbook. 

 

What am I missing? 

 

Thanks

 

Charlot

Hi Charlot,

 

In the formula names of two tables are used. Back to your sample they are Table1 and Table3

=IFNA(LOOKUP(2,1/(COUNTIF(Table3[[#Headers],[Name]]:OFFSET([@Name],-1,0),Table1[Name])=0),Table1[Name]),"")

In Excel Online it's not possible to rename the tables so far, so you shall use ones which Excel generate automatically. And I didn't find the friendly way how to recognize the generated table names. What I did that's in some cell to the right tried to enter the formula

=Table1[@Name]

(or with any other column name). If Table1 is correct it returns the value, otherwise an error. If the latest to test with formula Table2, Table3, etc.

After you enter the formula in the first row of the table the easiest way to expand the table down is to select that first cell and cells down in that column as many as you need, Ctrl+D after that.

Attached file I generated in Excel Online only.

Hi Sergei

 

It simply does not work. I now know that the Table generated from the Form I created is Table1 but still, in the online version, I cannot make the second table (named Table3) somehow link to this table. I think this is due to the limited functions in the online version of Excel. At best, what might work is referencing the entire sheet but even then, I have my doubts. 

 

I tried 

=IFNA(LOOKUP(2,1/(COUNTIF(FORM1!A1[[#Headers],[Name]]:OFFSET([@Name],-1,0),FORM1!A1[Name])=0),FORM1!A1[Name]),"")

 

FORM1!A1 being the sheet in question but either it still does not work, or I am getting the syntax wrong again. 

 

I will work with the simple version and download the workbook every time I need to take stock. Not the ideal but a good workaround. 

 

Thanks for your all your help with this. 

 

Regards

 

Charlot

Charlot,

 

Name of the table generated doesn't depend on the sheet it placed. Excel generates default names sequentially: Table1, Table2, Table3, etc.  Moreover, if you create some table, e.g. Table3, after that delete it for some reason (now you have only Table1 and Table2), and create another new table - it'll be Table4. Thus you have now Table1, Table2 and Table4 in the workbook.

 

Tables and sheets are different dimensions. You may create all tables in one sheet, or in different ones - sequence of table names will be the same. Table is not linked directly to the sheet in which it placed. Your syntaxes ...FORM1!A1[[#Headers]... doesn't work. It shall be table name here.

 

All that definitely works in Excel Online. The file I sent with previous post was modified in it only. In your final file most probably you use wrong table names. In the formula there are two of them. In the

COUNTIF(Table3[[#Headers]

Table3 is resulting table, which is in the Sheet1 of the sample file. Within the OFFSET part is another table

OFFSET([@Name],-1,0),Table1[Name])=0),Table1[Name])

Table1 which is Form1 sheet of the sample file.

Hi Sergei

 

Yes, you are right! I did not realise the reference to the different tables so I was putting Table1. Could kick myself for not realising! 


Working brilliantly now! Thanks for all your helps with this. 

 

Best regards


Charlot

Great! Was glad to help

@Charlot Cassar 

Utilizando o Excel da suíte de aplicativos do Office 365, em português, um policial precisa dividir a soma do intervalo de células de A3 até A6 pela média do intervalo de células de B3 até B6. Na célula que vai receber o resultado, deve utilizar a fórmula:

Ex¹)=SOMA(A3:A6)/MÉDIA(B3:B6)

Ex²)=SOMA(A3..A6)/MÉDIA(B3..B6)

JUSTIFICATIVA

é possível observar que o resultado é idêntico as figuras 1 e 3. Portanto, o Excel do pacote Office 365 interpreta as funções do exemplo 1 e do exemplo 2 são exatamente da mesma forma e ambas as operações dão o mesmo resultado.

 

 

1 best response

Accepted Solutions
best response confirmed by Charlot Cassar (Copper Contributor)
Solution

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.

View solution in original post