How do I pass a range on another sheet within the spreadsheet file to a custom function?

Posted on

How do I pass a range on another sheet within the spreadsheet file to a custom function? – Whether you’re just starting out with Google Sheets or are a seasoned pro, sooner or later one of your formulas will give you a formula or script parse error message rather than the result you want. However, every Formula user, irrespective of whether it’s a beginner or expert, has invariably come across a formula parse error in Google Sheets at least once in their life (and if you haven’t. you soon will). It can be frustrating, especially if it’s a longer formula where the formula parse error may not be obvious. In this article, we will talk about some common script or formula parse errors in Google Sheets, like How do I pass a range on another sheet within the spreadsheet file to a custom function?, and how to fix it.

I know how to pass simple ranges to a custom function (ranges in the same sheet as the function usage). How can I pass the address of a cell in a different sheet to that function. For example
A1 has =myfcn(othersheet,row(a1),column(a1))

and function myfunction(sheetname, rownum,colnum, cells)

I only need to pass the address of one cell of the other sheet.

Answer :

Assuming that the ANOTHER sheet name is Sheet2 and the cell that you want to refer is A2 then the reference to be used should be Sheet2!A2, then the resulting formula is

=myfcn(Sheet2!A2,row(a1),column(a1))

Leave a Reply

Your email address will not be published.