Master the Indirect Function in Google Sheets - A Guide for Make.com Users

Have you ever found yourself in a situation where you have a new row created in a Google Sheet and you'd like to copy a formula in one column to the new row? You know, just like when you drag-copy in sheets and the formula is updated relatively so the formula applies to the new row. Well, you're not alone. This is a common challenge many users face and the good news is, it's possible to solve it.

Step-by-Step Solution

The solution to this problem lies in using the indirect() functions. Here’s an example of a scenario:

={{3.data.data.total_karma}}-indirect("C"&row()-1)

In the sheet itself, it then looks like this:

By using the indirect function, you can create ‘dynamic’ references to other cells, just like the dragdown functionality would do.

Troubleshooting Tips

If you're having trouble implementing this solution, make sure you're using the correct syntax for the indirect function. You can find more information about this function here.

Conclusion

In conclusion, copying a formula to a new row in a Google Sheet and having it update relatively to apply to the new row is possible by using the indirect function. This function allows you to create dynamic references to other cells, similar to the dragdown functionality. So, the next time you find yourself in this situation, remember this solution and save yourself some time and frustration.

You do have further questions?

Ask your question via E-Mail: Richard@make-app.de
Book a paid Support & Consulting Session
New Make.com user? Sign up through this Affiliate Link to receive 10.000 operations for FREE.