0

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.

user3045525
  • 45
  • 5
  • Do you need and ending row? Sheets supports leaving out an ending row so your named range is dynamic – RemcoE33 Jun 26 '22 at 19:46
  • @RemcoE33 - thanks - I'm not clear what you mean, do you have a link? – user3045525 Jun 26 '22 at 23:29
  • If the linked duplicate answers doesn't answer your question, [edit] your question to explain how the answers don't satisfy your question. If you're having trouble implementing a solution in the duplicate or don't understand a solution provided, ask a new specific question with the part you're having trouble with. – TheMaster Jun 26 '22 at 23:30
  • @TheMaster - I've had a look and tried various different formats as used in that question (e.g. replacing $A$1 with A1) but can't get anything that works. I've edited the original question - I don't want to use any scripting. – user3045525 Jun 26 '22 at 23:31
  • try `=ARRAYFORMULA(WEEKNUM(A2:INDEX(A2:A,COUNTA(A2:A)),2))` – TheMaster Jun 26 '22 at 23:33
  • @TheMaster - thanks but curiously that also gives me an "Invalid Range" error. But in any case I need something that returns a range reference as in Excel. – user3045525 Jun 26 '22 at 23:38
  • That's what my formula does. Your second formula `=Sheet1!$K$1:INDEX(Sheet1!$K:$K,COUNTA(Sheet1!$K:$K),1)` also works perfectly for me. In any case, reopened this. – TheMaster Jun 27 '22 at 00:01
  • Ah I guess you're entering this directly in the worksheet? I'm using it to define a named range. I just tracked down a clunky solution using INDIRECT but it appears that Sheets isn't capable of defining dynamic named ranges like Excel. My Sheets adventure ends early! Thanks for the help! – user3045525 Jun 27 '22 at 00:06
  • 1
    I see. Sheets is better than excel in plenty of other ways though. – TheMaster Jun 27 '22 at 00:09
  • You could also consider requesting this feature via `Help > Help Sheets improve` in the editor. – Iamblichus Jun 27 '22 at 07:33

0 Answers0