How to error proof QR code

Alfredo Brioli

I have a function that is dependent on the operator scanning a QR code:

=(IF(NOT(ISBLANK([QR Code]@row)), LEFT([QR Code]@row, FIND("#", [QR Code]@row) - 1), ([Job number]@row)))

If the operator doesn't scan a QR code or doesn't have one, I want the cell to show what the operator typed or scanned from a 2D bar code rather than opening a new input (Job Number) to populate the cell or retuning #invalid value

Genevieve P.

Hi @Alfredo Brioli

First, I cleaned up your formula to remove extra brackets that don't need to be there. This helps me see the structure of your instructions. I also updated the NOT(ISBLANK()) portion with <> "" (which says "not blank") because this helps me read through the formula, but it's a personal preference:

=IF([QR Code]@row <> "", LEFT([QR Code]@row, FIND("#", [QR Code]@row) - 1), [Job Number]@row)

This says:

If the QR Code is not blank, then return everything to the left of the # sign, otherwise, if the QR Code IS blank, return the Job Number.

It sounds like you don't want the Job Number portion of the formula. In this case, you can reference the cell that would contain the 2D Bar Code or typed number instead. Replace the bolded portion with the name of the column you have set up to receive the typed number:

=IF([QR Code]@row <> "", LEFT([QR Code]@row, FIND("#", [QR Code]@row) - 1), [Alternate Number]@row)

Let me know if this works for you! If not, it would be helpful to see a demo version of your sheet with a full explanation of how you want the formula to be automated.

Cheers,

Genevieve

Alfredo Brioli

Genevieve, thanks for your reply.

What if the QR Code is not a QR code, i.e. they typed in because they didn't have a QR code? Is there a way to error proof it, and/or just print what they typed in the QR code?

Genevieve P.

Hi @Alfredo Brioli

Do you have a database sheet that stores all of your current QR Codes? If so, you could use a cross-sheet formula to ensure that the value entered exists in that other sheet.

If it doesn't you can simply return what they typed in. If it does exist, you can run your current LEFT formula.

Alfredo Brioli

It retuns an #INVALID VALUE if it is not a QR Code

=IF([QR Code]@row <> "", LEFT([QR Code]@row, FIND("#", [QR Code]@row) - 1), LEFT([QR Code]@row))

Genevieve P.

Hi @Alfredo Brioli

Can you confirm what you mean when you say "It is not a QR code"? Do you mean when that cell is blank, or when the value typed in does not correlate to one of your specific codes?

Alfredo Brioli

If a number or characters are entered (not a QR Code), it returns an #INVALID VALUE in the worksheet.

7619630#30767300#307673#R00#4 is a QR code they could scan

JOB NUMBER#PART NUMBER#RAWING NUMBER#REV LEVEL#JOB QUANITY

If they scan the wrong code, or enter say, 7619630, it errors

only when a QR code is scanned does the data populate correctly, regardless what is in the "Else" of the IF statement

Genevieve P.

Hi @Alfredo Brioli

Formula errors can create a domino effect, meaning that if a cell has an error, when you reference that same cell in another formula that formula will automatically show the same error.

Can you clarify what formula you're using in the "Job (QR)" column?