View Your Form Data

After setting up your Google Form to your liking, you’ll notice that a Google Sheet is automatically created to store the data that’s input and submitted into your Form. A Google Sheet is a spreadsheet program that is extremely similar to Microsoft Excel, which I’m sure you all have used at some point in your life. This data is a great log of entry, but we’ll have to take it a few steps further to create our system.

Reference Form Data in Another Sheet

So, to make your form data into a live, automatically updating inventory system, first go to the tab titled “Sheet 2”, and reference your data from the “form responses” tab using an equation like the one in the picture below. It’s important to have your “inventory #” be in the first column for future steps, which is why I rearranged the order.

Assign a Factor

Next, after you reference all of your form data like above, create a new column titled “factor”. We need to assign a factor for an item that is entering our inventory and one that is leaving. The IF-statement formula shown below tells the spreadsheet to assign any item that says “in” a factor of 1 and any item that says “out” a factor of -1

Create a Pivot Table

Select all of your data in sheet 2 plus a few hundred more rows for future additions, and create a pivot table, which will be embedded on a separate sheet. A pivot table compiles data from another table and easily compiles it for easy to read summaries. We will use this to sum our quantities (you’ll notice item #12 has a line that says “in” and also a line that says “out”).

Input the settings as shown below into your pivot table. Item 12 now shows a quantity of zero, indicating that that specific item came into our inventory and then left.

Create Final Inventory Spreadsheet

Finally, create one final sheet, this will bring everything together. First, reference the item numbers in the pivot table sheet like we did for sheet 2, to transfer those numbers to this new sheet. Next, we’re going to use one of my favorite spreadsheet formulas, VLOOKUP, which will allow us to grab data that corresponds to the item number from other sheets without entering it manually. Below, I show you how I set up my VLOOKUP function for the Item Description. The basic idea of this formula is so: =VLOOKUP(cell you wish to lookup (in our case: item number), the range of data that you wish to look through, the column you want to reference (count columns starting with the first one), whether you want an exact match or a similar match (we want exact, so enter FALSE)). When you type in =VLOOKUP into your form, Google also shows you a helpful popup screen which explains further. DO that for all columns, but remember to reference the pivot table you created when you get to the “Current Quantity” column.

And there you have it; by using some basic formulas, you have created a live updating inventory system for free. Once you’re comfortable with setting up this very basic inventory, feel free to explore the many other options Google Forms and Google Sheets have available and make this form your own.

Still have questions or need some help getting yours to work? Comment below!

In what has become a popular trend for construction technology leaders, Procore recently held their yearly Groundbreak construction technology conference in Austin, TX from November 13-15. These yearly conference allow companies to reach a core of their user base and announce their past and future enhancements, in addition to performing hands-on training sessions with product experts. Construction Junkie was in attendance for this year’s Gorundbreak and we’ve got you covered with all the details.

This week, Procore held their annual construction technology conference called Groundbreak in Austin, TX. Through a series of keynote speeches and breakout sessions, Procore announced their latest releases and plans for the future. I was in attendance this week and I’ll have a full recap of the event coming soon. One of the company’s biggest announcements at this year’s Groundbreak was the release of a new product called Design Coordination, for Virtual Design and Construction (VDC) teams.

CAT phones has made our headlines in recent years by matching their rugged design with a suite of features that no other phone can match, like the built-in thermal imaging, laser measure, and humidity and VOC sensors that the Cat S61 has. Those past phones have been unlocked and compatible with certain networks of the user’s choosing, but their latest release is starting out only available on the Sprint Network.

It’s been a long time coming, but the world’s first 3D printed steel bridge has passed all of its required testing and is ready to be installed. But, before it gets to its final home, it will go on a short publicity tour.

Tracking your construction project’s submittals and their approval status can be a tedious and frustrating process, but thankfully several project management applications are helping solve that issue with technology. At the beginning of this year, PlanGrid announced the release of an automatic submittal log creator tool, which scans through your project’s specification book and creates a trackable log of each submittal. The company has recently added several new features to make the Submittal platform, which allows users to manipulate the submittal log, even more useful.

I’ve mentioned this several times before, but the single greatest thing technology companies can do for the construction industry is to allow cross-platform integration. That’s essentially what construction is at its core, anyway, a bunch of different entities working together for a common goal. Autodesk’s BIM 360, which already integrates 60+ different softwares into its platform, has recently added NoteVault to its list.

When we’ve talked about construction robotics in the past, it’s mostly been about really large machines working on exterior structures, like this brick-laying robot, or this self-driving track loader. A technology institute in Japan is busy working towards bringing robotics to the interior finish side of the construction world with the development of a drywall installing robot.

PlanGrid users may have noticed, or been frustrated with that fact, that some features that are available on the program’s Android and iOS apps are not available on the Windows app. Windows’ Surface tablets have become a popular option for construction teams in recent years, so those users will be happy to hear that the Field Reports function is now available on PlanGrid for Windows.

ConstructionJunkie.com is a participant in the Amazon Services LLC Associates Program, an affiliate advertising program designed to provide a means for sites to earn advertising fees by advertising and linking to amazon.com.