0

Whenever I try to make a named range in Excel I keep getting an error. I believe my formula is correct: 

=OFFSET($B$2,0,0,COUNTA($B$2:$B$200),1)

However when I press OK I keep getting the dialog screen which states Excel found a problem with my formula. Then it highlights the following part of the formula: $B$2,0,0,COUNTA.

I looked through various tutorials where this formula should be correct.

Can someone help me out on this?

pnuts
  • 58,317
  • 11
  • 87
  • 139
Sjoerd
  • 1
  • Does your locale use semicolons `;` for the argument separators? (Or some character other than the comma `,`) – Ron Rosenfeld Jun 02 '15 at 11:34
  • As other posters have said - probably down to the argument separators. I've no idea how large your workbook is, but the OFFSET formula can cause slowdown and I'd suggest using this alternative formula: **=$B$2:INDEX($B:$B,COUNTA($B:$B))**. Have a look at this site for further info on volatile functions: http://chandoo.org/wp/2014/03/03/handle-volatile-functions-like-they-are-dynamite/ – Darren Bartrup-Cook Jun 02 '15 at 11:45
  • As a named range ensure you're referencing the worksheet that the formula should apply to, otherwise it will use the same sheet as the named range is used in. **=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B$2:$B$200),1)** – Darren Bartrup-Cook Jun 02 '15 at 11:48
  • Does the range B2:B200 contain any values when you are trying to define the range? – Mark Fitzgerald Jun 02 '15 at 11:55

2 Answers2

0

One way to see this error message is if you are using the incorrect argument separators. For example, many locale's use the semicolon ;.

Try replacing the commas in your formula with semicolons

=OFFSET($B$2;0;0;COUNTA($B$2:$B$200);1)

or whatever your locale argument separator is.

Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
0

Thanks, it were indeed the seperators which caused the problem: I have to use ; instead of a , - this tricked me as all of the tutorials I watched used the comma as well.

Sjoerd
  • 1