In Calc, I am attempting to make a function that will search within an area that is defined by another cell. More precisely, I want to be be able to enter the name of the sheet.
As an example, suppose I want to find the row in area Sheet2.A1:A50 where the cell has the value “apple”. I could do this with the function
=MATCH(Sheet2.A1:A50;“apple”;0)
which would return the row number for “apple” if it exists, and return an area if it does not.
However, I would like to apply this to a series of sheets with different names, so I would like to get the sheet name (“Sheet2” in the example) from a different cell. I have tried things like
=MATCH(B1&".A1:A50";“apple”;0)
to get the sheet name from the contents of cell B1. But I only get an error message.
Is a thing like this possible, and how?