This is an advanced tip for Excel Database management, this was done with Excel 2007, I’ll update it to 2010/2013 when possible.

Concept and Idea

Salomon and Atomic were launching their new 2015/2016 range in Courchevel during a week-long event prepared by our Trade Marketing departement. This was the third edition of the event and for the previous two edition creating the summary of the event’s been proven to be a real nightmare.

Our goal was :

• to have a single file where we could scan the product and what product it was
• associate it to the borrower (and know who he or she was)
• define if the product was exiting or re-entering the test park
• define if the product was in or out of stock
• create an alert if someone was borrowing twice a single product type (ski, snowboard etc…)
• to have a file we could crunch some data from at the end of the event

During the event, our clients can borrow our gear whether it be skis, boots, snowboards, helmets, goggles etc, to test those out.
After close analysis we decided the best way to simplify the registering and lend out process was to buy a bar code scanner, add bar codes to all our test product and finaly create a sticker with a bar code for each of our guest tester.

To create the bar codes for our test parc we hired an external printing agency (which handles one of the biggest ski test event in France -Ski Force Winter Tour-).
On the other hand, we created and printed the bar code for our guest clients in house, via our regular printers.
I used Avery’s online solution to create the bar code and print them (after searching for some time… Avery’s website really proved to be the best, quickest and easiest solution)

Hence we had 1000 bar codes from 43 000 001 to 43 000 999. We handed them out to our guests when registering and welcoming them in Courchevel. Hence we had an easy to use database with all our guests :

I then created the real deal to manage our test parc. For that I used several tricks :

Creating the databases :

I created three tabs for three separate databases on my excel spreadsheet

• One for the guests which would be replicated every night from the first file which will serve as a reference for all the guests (same as above)
• One for the test park where we have a single table which serves as a reference DB for all the gear we lend out

  

• And the in & out database where the real deal happens

  

   

Pulling data from the databases

Everything is made through formulas.

The product number is entered in column A and it automatically fetches the product type/size/brand on the product database.
To do that I used the famous « Index / Match combination » (as every ski has a unique identifier through its bar code number)

=INDEX(« info_you_want_to_fetch »;MATCH(reference_you_fetch_that_info_from;where_you_have_that_same_reference;0))

so in my case =INDEX(produits[type];MATCH([Scan Produit];produits[Code barre];0))

> i’m looking to pull the product type from the product scan (=column A); which can be found in the product database under the bar code column

I used the formula to do that for column B, C, D and E to pull, the product type, brand, name and size.

The exact same pattern is used for the first name, last name and shop fetched from the guest database.

I had to tweak the forumla a bit to match my exact needs:

=IF(entrees_sorties[[#This Row];[Scan Produit]]<> » »;IFERROR(INDEX(produits[type];MATCH([Scan Produit];produits[Code barre];0)); »produit inconnu !! »); » »)

• If the product scan of this row is different from empty;
  • I fetch the data,
  • if my data is in error it means I don’t have that product info in my DB > I add an alert « unknown product »
• If the product scan is empty, my cell is empty (and awaiting data input)

So now we have associated a product number (+ type and stuff) to a guest.

Now the tricky part: what the bar code scanner does: entering and number and then pressing enter. So to simplify the process I had to:

• Lock the editing of the entire sheet, except column A and F (product scan and guest scan) (via « Allow user to edit range in the review tab »)
  
• When locking the sheet (protect sheet in review tab) I UNTICKED select locked cells, hence preventing the bar code scanner to select anything beyond column A or F

  

• Then, as the bar code scanner presses « Enter » as soon as finishing the scan you I had to make the enter button go RIGHT instead of down, and you can do that in the excel options :

  

Now we have a spreadsheet that’s ready: each time a bar code is scanned the selection moves automatically from column A to column F going back and forth from the product scan to the guest scan!

Now it’s only a matter of counting items:

The first time you scan a product number, you actually release the product from your test park, it’s therefore not in stock anymore… and the product is existing the test park.
The exact contrary happens the next time you scan the same product number.

That means that each time you make an overall ODD count of that product scan, the product is « OUT », if it’s an EVEN count, your product is « IN » your test park.

=IF(ISODD(COUNTIF(entrees_sorties[[#All];[Scan Produit]]; »= »&[Scan Produit])); »sorti »; »en stock »)

Here i’m counting the overall number of scan product equal to the scan product of that same line, if it’s odd : the product is out of stock, otherwise it’s in stock.

The same logic applies to the exit and entrance part… except it’s not the overall count you are making but the count from everything above that line.

=IF(ISODD(COUNTIF($A$1:$A4455; »= »&entrees_sorties[[#This Row];[Scan Produit]])); »sortie »; »entrée »); » »)

Here i’m at line 4455… and i’m counting the number of scan product of the lines above : if it’s odd the product is exiting the test park, if it’s even it’s even, it’s re-entering the test park

We now have two cells: one saying if the product is in our out of stock, and one saying if it’s entering or exiting the stock.

Final part : adding a timestamp to excel…

Here is the magic formula:

=IF(entrees_sorties[[#This Row];[Scan Produit]]<> » »;IF(entrees_sorties[[#This Row];[Date]]= » »;NOW();entrees_sorties[[#This Row];[Date]]); » »)

What it does: each time you enter a new product scan, it adds a timestamp

How does it work?

I’m using the Iterative calculation of excel which you have to enable in the Excel options :

What the formula does:

• In the date cell, i’m checking if the product scan is not empty;
  • if it’s not; i’m then checking if the date cell itself is empty;
    • if it’s empty; i’m adding the timestamp via the now() formula (hence not being empty anymore);
    • if it’s not empty; i’m leaving the data that’s there untouched,
• finally if the product scan is empty, i’m leaving the cell empty.

Got it?

This will help determine how much time the product was used.

Finalizing the file

Two more things:

First: the « alert » system. I needed to prevent any guest to borrow twice the same type of gear without giving back the previous same type of that gear (you cannot borrow two pair of skis at once)

=IF(entrees_sorties[[#This Row];[Statut Produit]]= »sorti »;IF(COUNTIFS([Type]; »= »&entrees_sorties[[#This Row];[Type]];[Type actuel]; »= »& »sortie »;[Scan Invité]; »= »&entrees_sorties[[#This Row];[Scan Invité]])-COUNTIFS([Type]; »= »&entrees_sorties[[#This Row];[Type]];[Type actuel]; »= »& »entrée »;[Scan Invité]; »= »&entrees_sorties[[#This Row];[Scan Invité]])>1; »alerte « &entrees_sorties[[#This Row];[Type]]; » »); » »)

Here i’m couting the number of exits of the same product type from that line and for that person (countifs) and subsctracting it from the count of entrances of the same product type and same person… and if that’s above 1 it means that this person is trying to borrow twice the same type of gear; I therefore display an alert with the type of gear.

Using conditional formating I highlight the full line when I have an alert.

Second : For a better ease of use on a daily I also slightly tweaked the product database with two additional columns:

One giving the product status extracted from the in & out database (which helps find lost products):

=IFERROR(INDEX(entrees_sorties[Statut Produit];MATCH(produits[[#This Row];[Code barre]];entrees_sorties[Scan Produit];0)); »jamais sorti »)

If I can’t find the product number in the in&out database the product never exited the test park; else I display its current in/out status

The second one giving the number of time the product was used (counting the number of exists from the in/out database)

You now have a fully working file to enter and exit your products…

Here is an example file you can use :

Let me know if you need anything!