Bar codes and QR codes

I’ve began posting on LinkedIn to start laying the groundwork for the future: showcasing what I can do, how I think, how I learn. It’s a weird feeling to be posting as if I’m some kind of expert when I don’t feel like one, although I do think I’m pretty good at what I have done. My initial posts have focused more on the software tools that financial types should try to use in order to learn how to automate pieces of their work. It’s always good to streamline your work and set up predictive tools so that your work is done faster and your financials are more accurate and clean.

Two weeks ago, I wrote about a non-software type of improvement: it was more about moving your work around, especially those closing activities, to an earlier part of the month to make closing activities less hectic. Instead of thinking that all closing activities needed to take place during close week, one can move certain activities to the prior week to create more time during close week. This idea works when all information you need is available in the system before the end of the month.

My post was about trying to find activities that can be moved earlier in the month to make room in your close schedule and one of the comments came back as kind of a non-sequitur, but a very interesting one. The person asked can one do live bar codes in pivot tables?

Live bar codes in pivot tables? That didn’t have anything to do with the topic at hand.

But I did wonder what he was talking about. What live bar codes? Is he talking about those vertical lines that we see on products and that are often scanned? Why would he talk about bar codes? Was he talking about the vertical bars or just the numbers that can be found underneath the bars? Does he have pictures of these bar codes and was asking out to handle pictures? Or did he have just the numbers and thus pivot tables will be able to handle them?

Through Googling, I found out that one can actually create free bar codes from sites located on the web. I didn’t actually click on those links because I wasn’t sure if those sites were scammy, so I have no idea if the site comes back with a picture as a JPEG or PNG or comes back as a number. I want to know how those free bar code generators work but I’m afraid of going to those sites.

Also through Googling, Excel is apparently unable at this time to use pictures in pivot tables. I tried it but realized that pictures sit on top of the cell rather than within the cell, so pivot tables come up blanks instead of pictures. There is a way of linking a cell to a picture and having a dropdown box to select which picture you want to select.

I’m going to embed the file but the pictures won’t work in Excel online; instead you will have to open the file as a “full-size workbook” (icon found on lower right) and then open it up in the desktop app – change the word “Viewing” found in the center just underneath the menu system.

The first tab “The set up” tab has the cell D3 linked to a picture or rather to a formula Named range. In cell C3 is a data validation list where you can choose your picture and cell D3 will come up with the associated picture with the name in C3. Cell D3 has the picture that is linked and the named range formula is LookupPictures (you can see it in the formula bar).

The second tab “List and how-to” used to have the list of pictures but I erased them. That tab now has just the instructions on how to set up the linked pictures and the data list. I found these instructions through Googling; there is no way I would have figured this out. Cells D18 and D19 provides two sites that contain how-to instructions. (Yay, I actually remembered to make a note of where I discovered the instructions.)

The last tab “Pictures” is where the pictures and the associated names are found. The data validation list in “The set up” tab is off of column D and the pictures, of course, comes from column A.

I don’t know how one can use this for work but this picture trick is available.

Back to bar codes. I asked the guy who asked the question about the bar codes to explain to me about the bar codes that he was thinking of. It turns out that when he goes to sports games, he gets his ticket through an app on his phone, and when he arrives at the stadium, he opens up his app pull up his ticket and a bar code scrolls across his screen for a scanner to pick up. Something like that. One possibility is the app is generating a bar code (maybe linking to a bar code generator site?) right then and there. How to capture that bar code is you want to use it in Excel? I have no idea. That is the first hurdle: capturing that bar code. The scanner I think is capturing it but does the scanner store it? If it does, then you would have to figure out how to access it. Then the second hurdle is what kind of format is that piece of data: jpeg or png or some kind of text or numeral data? If text or numeral, then excel will handle that piece of data fine (although it will not be in a form of bars). But if you want that piece of data as bars, then I imagine that the data would have to come in as an image. In that case, currently Excel will not be able to handle it in a pivot table; some other mechanism will have to be figured out. I haven’t really pursued this line of thought further as I don’t know how to get pass hurdle one (getting to the data itself).

There is a YouTube how-to by Leila Gharani on generating a bar code. This YouTube is about generating a bar code for a manufacturing good so the whole structure relies on a set of rules for generating the numerical codes and generating the bars. Part of the generation is done via formulas and the bars are created via VBA. You would enter the manufacturer code and the product code and the spreadsheet will generate the proper bar code for that good.

I also looked at a YouTube on generating QR codes. Leila Gharani tells how to create a batch of QR codes and this method utilizes Google sheets as the starting point. There is an IMAGE function in Google that kicks off the creation of the QR code. You can copy that formula over to Excel to create a range of QR codes for various links.

Kevin Stratvert has another method where he uses an add-in in either Excel, Word, or PowerPoint. This one seems easier to use but is done for one by one rather than a batch process.

Before I end this post, I do want to say why one would want to use a QR code. According to Kevin’s method, you can use the QR code for websites, emails, SMS, and maybe one or two other things. Instead of writing out the entire website on your PowerPoint, for example, you just supply a QR code that the audience can read using their phones. One would need a QR code scanner on one’s phone before reading the QR code.

Okay, that’s it for tonight. Yes, I rushed through at the end but I still need to exercise and do my nightly data gathering before I can go to bed. So, off to other things.