0

So, I'm wanting to use the following line of code:

=image(CONCATENATE("https://chart.googleapis.com/chart?chs=150x150&cht=qr&chl="&A2&+B2&+C2&+D2&+E2&+F2&+G2))

But, this formula needs to change from A2/B2/C2 to A3/B3/C3 and so on, but automatically.

USE CASE:
I'm wanting to create unique QR codes, based on the incoming data, which is written to a Google sheet. So, a form is filled out capturing certain information, is written to row A2/B2/C2 etc, in Google sheet, and the resulting QR code is generated at the end of that row. based on the above line of code. This line of code/formula is not present on the spreadsheet, but rather, is pushed to the cell when the rest of the information is submitted.

The current problem of course, the code above does not change the QR code dynamically to represent the next row of data. It hard references the previous row ad infinitum. I need it to reference the next row, dynamically.

Is this possible?

1

3 Answers 3

0

Google Sheets does this already.

Alternatives

  1. Copy and paste the formula one cell below the position of the formula to get A3/B3/C3 and so on, two cell below to theg A4/B4/C4 and so on, and so on.
  2. Do a fill down. Point to the bottom right corner of the cell having the formula click and drag until you get the required cells to have the desired formula.
0
0

Rather than using INDIRECT which is a volatile function, use an array formula that updates every time a new form record is received:

=INDEX(
   IF(A2:A, 
   IMAGE("https://chart.googleapis.com/chart?chs=150x150&cht=qr&chl="& 
   A2:A& B2:B& C2:C& D2:D& E2:E& F2:F& G2:G),))
-1

You can use indirect and reference it's current row like this:

=image("https://chart.googleapis.com/chart?chs=150x150&cht=qr&chl="&join("+",indirect("A"&row()&":G"&row())))

if you do need to keep the cells separate you can wrap each one with indirect("ColumnLetter"&row()) but the above is far simpler. Also since your using the & symbols, you do not need to use concatenate, it has the same result.

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.