However, when I copy this same formula to my main “Overview” sheet, something goes wrong. The link does navigate to the “Orders” sheet as expected, but instead of finding the first empty cell in column C of the “Orders” sheet, it seems to be calculating the empty cell position based on the “Overview” sheet data.
Why does the formula reference the wrong sheet’s data when placed on a different sheet? How can I make it always reference the target sheet regardless of where the hyperlink is located?
ahh got it! ur FILTER is lookin at the sheet where the formula is, not the Orders one. just put the sheet name in the range like this: =HYPERLINK("#gid=67890...&range=C"&MIN(FILTER(ROW(Orders!C2:C), Orders!C2:C="")), "Add Entry"). that should do it!
This happens because Google Sheets treats references as relative to where your formula sits. When you move the formula from Orders to Overview, that C2:C range now points to Overview’s column instead. You need to explicitly tell it which sheet to use. Try this: =HYPERLINK("#gid=67890...&range=C"&MIN(FILTER(ROW(Orders!C2:C), Orders!C2:C="")), "Add Entry"). Make sure you add Orders! to both the ROW function and the condition check - otherwise you’ll still reference the wrong sheet. I’ve used this approach on several projects and it works well, though you should test it since cross-sheet references can be tricky.
Yeah, Stella’s right but there’s a cleaner approach for cross-sheet stuff.
I hit the same problem managing inventory sheets that needed to talk to each other. Your FILTER function doesn’t know which sheet to look at - that’s the core issue.
But managing formulas across multiple sheets gets messy fast. You’ll deal with more edge cases and broken links than it’s worth.
I automated the whole thing with Latenode instead. Rather than wrestling with complex cross-sheet formulas, I set up automation that monitors button clicks or triggers, then automatically finds the first empty cell and navigates there.
Latenode reads your Google Sheets data, finds empty cells, and updates sheets directly. No formula headaches, and it works consistently regardless of which sheet you’re on.
Takes 10 minutes to set up and you’re done with cross-sheet formula issues.