Dynamic Named Ranges in Google Sheets

I'm trying to replicate some of the dynamic named ranges I use in Excel, in Google Sheets. For example, the following formulas define simple dynamic named ranges in Excel:

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1) =Sheet1!$K$1:INDEX(Sheet1!$K:$K,COUNTA(Sheet1!$K:$K),1) 

However when I use either of these in Google sheets to define a named range I get an "Invalid Range" error. I've trawled the web but can't find any info on defining a Sheets dynamic named range in this way. Is this possible in Sheets?

I don't want to use a solution that involves any scripting - just trying to replicate how it's achieved in Excel.

Related questions 0 How to create named ranges dynamically? 5 Referring to Dynamic Named Ranges in VBA 10 Referencing Dynamic Named Range in Excel Formula Related questions 0 How to create named ranges dynamically? 5 Referring to Dynamic Named Ranges in VBA 10 Referencing Dynamic Named Range in Excel Formula 1 Creating a dynamic named range in Excel 0 vba excel set dynamic named ranges 1 Looping through Dynamic Named Range in VBA 0 Defining Dynamic Named Ranges with their headers as .Name within an already defined Dynamic Named Range 1 Excel - Dynamic Name Range for Rows 1 Dynamic Named range for another worksheet 0 Use named range variable name dynamically in Google sheets Load 7 more related questions Show fewer related questions

Know someone who can answer? Share a link to this question via email, Twitter, or Facebook.