Effective utilization of Excel resources with smart sheets XNUMX

Part Two　

first

In the first part, we took the equipment management Excel sheet as an example.I explained that you can manage smart equipment in a cloud environment by uploading to a smart sheet and making some relatively simple settings.

In the second part, we will also use Smartsheet mobile app and advanced logic features.We will explain how to further improve work efficiency by selecting equipment using barcodes and menus at the site where equipment is stored, recording warehousing and delivery, and displaying the current number on the dashboard etc.

Thing you want to do　

List what you want to do for simple inventory management of equipment.

■ Select from the menu and process the entry and exit on the spot *

You don't have to enter the equipment name one by one by selecting the equipment from the menu on your smartphone or tablet.

The seat of the smart sheet reflects the real-time inventory quantity.Prevents omissions and mistakes in paper and Excel ledgers, and improves the accuracy of inventory management.

* The term for warehousing and warehousing may be exaggerated for equipment management, but we consider replenishing equipment as warehousing and taking out and using equipment as warehousing, and match it with the general term for inventory management.

■ Streamline warehousing / delivery processing with barcodes and QR codes

Since the mobile app can read barcodes and QR codes, it is possible to use a method of reading the barcode of equipment and entering the number of warehousing and delivery.

Aggregation work is also performed automatically, eliminating troublesome work after work.

■ Manage the history of warehousing and warehousing on the smart sheet

Record who bought, used, and replenished equipment, when, and how many.It will also be possible to analyze the utilization status of equipment.

Specifically, every time we move in and out, we keep a record so that the current number can be calculated from that record.

■ If you fall below the order point, you will be notified by email.

When the number of stocks falls below the specified number, an alert email will be sent to the person in charge so that out of stock and omission of orders can be prevented.

Image of the finished product

It takes about XNUMX minute for the input result of the form to be reflected.

Specific realization method by Smartsheet

■ Manage the history of warehousing and warehousing on the smart sheet

Before inputting with a menu or barcode, think about what kind of sheet etc. will be configured.

Here, a record of warehousing and warehousing is attached to the "Easy equipment inventory management warehousing and warehousing record" sheet that manages warehousing and warehousing records.
Then, the current number is automatically entered by the function in the sheet that can be displayed such as Gantt that was made last time.
In addition, "Simple equipment inventory management table" (name changed from equipment management table)

① Read JAN CODE with your smartphone and register the number of incoming / outgoing goods.

(XNUMX) Obtain equipment names, etc. based on JAN CODE, and (XNUMX) use the equipment name as a key to count the number of shipments and receipts.

Formula between sheets (cross-sheet formula)

To aggregate the data from the "Easy Equipment Inventory Management Table" to the "Easy Equipment Inventory Management Entry / Exit Record" on another sheet, use the formula between the sheets.

What is the cross sheet calculation formula?
　Cross-sheet formulas refer to data in another sheet to perform calculations or search for information in a particular cell.SUMIF () and COUNTIF () are typical for calculation, and VLOOKUP is not representative for search.

SUMIF () function

The syntax of this function that can be used in formulas between sheets is

SUMIF (range, condition, [total range])
　Range — The group of cells to evaluate.　
　Condition — A condition that defines the number to add.
　Total Range — [Optional] A group of cells to add.
You can specify a different sheet in the range settings.

In particular
= SUMIF ({goods_name}, equipment name @row, {updated_qty})
And the formula is included.

As a range of cells to evaluate first {goods_name} Is specified, but this is in the following range of another sheet, "Simple Equipment Warehouse Management Entry / Exit Records".
Note that {goods_name} is any name you have given to make it easier to understand when setting the range.

Of expression{goods_name}, equipment name @ The meaning of the part is as follows.

　 "Simple equipment inventory management table"Equipment nameColumn data and the above range {goods_name} Compare the data of

As a result, the total is calculated for each equipment name.

Then, as a total range, {Updated_qty} The range of is specified, but this is a column of numbers next to each other.

From the above, "the total number of items with the same equipment name on the warehousing / delivery sheet based on the equipment name on the original sheet" is calculated.

This will give you the current number for each piece of equipment.

■ Select from the menu and process the entry and exit on the spot *

In the first menu, select office supplies, promotional items, PC-related items, etc. as the equipment classification.
When you select office supplies, you can select a list of office supplies in the next menu.
When you select a promotional item, you can select a list of promotional items in the next menu.

Hierarchical menu (Dependent Drop Down List)

I introduced this method in another article as one of the merits of using Appsheet by connecting it to a smart sheet, which is called a hierarchical menu (Dependent Drop Down List or Conditiona List in English).

The Appsheet hierarchical menu is easy to use as it allows you to dynamically change the list contents by creating a separate table for the drop-down list.

Appsheet pricing system proportional to the number of users

However, Appsheet's pricing is $ 5 or $ XNUMX (month) per user, and a $ XNUMX account is required to scan barcodes.
Also, anonymous forms are unsuitable for recording exactly who entered them, as described below.

For example, in the case of an organization such as Japan Post, to which I belonged before, there are hundreds of thousands of users who may use it, so the type of service in which the usage fee is proportional to the number of users is actually used. Is difficult.

Therefore, let's try to do something equivalent to the hierarchical menu layer only with the function of the smart sheet.

Of the formConditional logicDisplay columns with corresponding menus

First, select the classification of office supplies, promotional items, etc. on the form.
Next, based on that selection, for example, if a promotional item is selected, the column with the promotional item CalorieMate, Cacao XNUMX, etc. set as a drop-down list will be displayed.

Aggregate into one column by IF function

If you do the above, the equipment names will be entered in separate columns, so use the IF function to aggregate them into one column.

In particular
= IF (menu @ row = “promotional supplies”, promotional items menu @ row, IF (menu @ row = “office supplies”, office supplies menu @ row))
The formula is included.

The formula means that in a row, if the menu column contains a promotional item, the menu equipment name column will contain the equipment name in the corresponding row of the promotional item menu column.

Here, the IF statement that requires promotional items and office supplies is written, but if necessary, we will add classifications such as PCs and consumables.
The logic of the above form is similar.

■ Streamline warehousing / delivery processing with barcodes and QR codes

Smart sheet mobile app

With the Smartsheet mobile app, you can access Smartsheet wherever you are, so you can type and do things wherever your equipment is in stock.

You can fill out the form as a web form, not just in the Smartsheet mobile app.
But from your smartphoneBarcode scanningYou need the Smartsheet mobile app to do this.

In addition to this, the Smartsheet mobile app写真Can be taken and attached on the spotGPS functionIt is also possible to enter the latitude and longitude by.

How to launch the mobile app (from the QR code on the form)

Select Share from Form Management to get the URL of the form.

You can open the form from this URL as well, but make this URL a QR code so that you can easily access it from your smartphone.The QR code below is an enlargement of the QR code pasted on the dashboard above.

You can get the URL of the form by reading the above QR code, so read the JAN CODE of the sample Calorie Mate or Kokuyo Note as shown below and check if the current number is updated by registering the goods in and out.
(It takes about XNUMX minute to update on the dashboard.)

Formulas between sheets

"Easy equipment inventory management entry / exit record"JAN codeFrom "Equipment Master" on another sheetEquipment name, etc.To get, use the formula between the sheets.

VLOOKUP () function

The syntax of this function that can be used in formulas between sheets is

VLOOKUP (value to search, cell range to search, column position of returned value, search method)
　Value to find: Must be in the first column.
　Range of cells to search: Contains both the leftmost column and the return value as a search result.
　Return value column position: Column 1 at the left end of the range of cells to search is 1 to specify which column has the value to search.
　Search method: Specify whether to search by exact match (false) or approximate match (true).

You can specify a different sheet in the range settings.

In particular
= IFERROR (VLOOKUP ([JAN CODE] @row, {bar_code_to_goods_name_2}, 2, false), “”)
And the formula is included.

As a range of cells to search {bar_code_to_goods_name_2} Is specified, but this is the following range of another sheet, "Equipment Master".
Note that {bar_code_to_goods_name_2} is an arbitrary name given for easy understanding when setting the range, and _2 is a personal note indicating that the return value is the second.

The expression means that the bar_code column looks for the same code as the original sheet and returns the corresponding second column, goods_name or equipment name.

■ Manage the history of warehousing and warehousing on the smart sheet

The form with the QR code posted on the dashboard above does not require you to log in to Smartsheet.This is so that readers can feel free to try it.

However, you should be asked to log in to Smartsheet to keep track of who registered your entry and exit.

The figure below shows the same note being issued and received with and without login.

In situations where login is not required, the creator is anonymous.Web-form@smartsheet.comAlthough it is,After requesting login,jmiyazaki@cloudsmart.jpIt is recorded that I entered.

The creation date and time and author of this table is a column type called "System Generated Columns", which is automatically retrieved by System in Smartsheet.

This allows you to record who, when, and who recorded it in a non-misleading way.

If you look at the cell history, you can see who made the edit and when.

■ If you fall below the order point, you will be notified by email.

Use a workflow to automatically perform some action (eg, email notification) when certain conditions (triggers) are met.

　

Click Automation from the menu above to create a workflow.

As shown in the figure on the rightTriggerSelect "If a row is added or changed" as-In the following casesAs, "Usage rate" is changed to some value,条件When "usage rate is XNUMX% or more",Send to contacts contained in cellSelect, to send an alert to the "ordering person", and set the workflow.

If you edit the sent text as follows, you can send a customized message by substituting the information in the cell surrounded by {{}}.

This makes it possible to send an alert email to the person in charge when the number of stocks falls below the specified number (usage rate exceeds 100%) to prevent out of stock and omission of orders.

Summary

This time compared to the last time
XNUMX Sheets were divided into three types: "sheets that display the current number of equipment," "sheets that record warehousing and delivery," and "equipment masters that show the relationship between equipment names and JAN CODE, etc."
XNUMX Among the three sheets, there were many things that used logic such as aggregation by mathematical formulas, reference to values, form logic, workflow, etc.
It may have been a little difficult to understand. (In fact, if you read it back and understand it in one shot, it's a pretty smart person.)

YoutubeIf you are not sure, please use this article as a reference when it is uploaded.

• What I learned
  • Formulas between sheets
    • For counting the current number →　SUMIF ()
    • Get the equipment name from the barcode →　VLOOKUP ()
  • Form logic
    • For dependent drop-down lists
  • Mobile appEnter barcode etc.
  • IF expressionSummary of values ​​across columns by
  • Simple workflow (utilization of {{cell value}} in sent text)

Remaining tasks (from next time onwards)

I didn't pick it up this time because it seems difficult,InventoryI would like to challenge the flow of adjusting the number on the books and the actual number of stocks from the next time onwards.