In order to reduce errors you can set validation tests on entered data. This can
In order to reduce errors you can set validation tests on entered data. This cannot show if something is correct, but it can show if something is ‘wrong’ or ‘needs attention’ by virtue of not meeting specified criteria.
Open the file T3 – Expenses v2 The link should prompt you to sign into a Google account if you aren’t already, and to create a copy of the file for your own use.
The config tab contains some lists of data that we can use in the validation.
We’ll be applying validation to the Expenses tab. In each case where we apply our validation, we’ll apply it to a whole column, to allow for extra data being added, but you may wish to subsequently remove any validation from the top row of headers.
-Date column: since this is an expenses sheet, the entered date should not be later than today. Set the validation to allow only dates, and specify they must be earlier than or equal to today using =NOW() for ‘today’. Reject the input if the date is invalid, and write a suitable error message.
Try entering a date later than today to check if it works.
-Staff column: to make sure the name is always spelt the same you could provide a drop-down list to pick from. -The list of staff is on the config sheet, at the top of column B. Make the whole column a named range. We’ll do the whole column to account for the possibility of new staff being added.
-Return to the Expenses tab and add a drop-down list validation to the Staff column,
using the named range you just created as the source of the list.
Configure it so values not in the list may also be entered.
-Category column: this could be another drop-down list, but as you
wouldn’t want staff inventing extra categories, it should be
configured so only values in the list are allowed. Start by
naming the list of valid categories in config!D:D.
-Cost column: there is already an IF function in column F to
check the value entered in column E, but the amount could
also be validated in column E itself. The value above which
entries need checking is defined in config!F1. Use this value
in a validation rule which will alert the user if the amount
entered into the Cost column is above the check limit, but
will still allow the entry to be entered. Switch to the Summary tab. -Cells B9:B12 need to count the number of claims made by each person. To do this you’ll need to use the COUNTIF function in each of the cells. Ensure your formula accounts for the possibility of more items being added to the list.
Hint: You’ll be counting in Expenses column B, and you should be able to reference the person’s name on the Summary sheet for the criterion.
-Cells C9:C12 need to show the total cost of expenses for each person. You will need to use the SUMIF function for this. Ensure it will remain correct if more items are added to the list.
Hint: the values for totalling are in Expenses column D, but the values for testing are in column B. As with COUNTIF you should also be able to reference the person’s name on the Summary sheet.
If all has gone well, trying making some changes:
-Change the check limit on the Summary sheet to a lower value (eg £10) and check the effect this has on the Expenses sheet.
-On the Expenses tab, add some more expenses (using the same staff names in column B).
Get this or a similar paper done in as fast as 4 hours, 24/7.
NB: We do not sell prewritten papers. All essays are written from scratch according to are specific needs and instructions
