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,
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
Great! Was glad to help
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
CharlotCharlot,
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.