Indirect function not properly returning named range

Copper Contributor

I have a simple indirect formula that returns a named range, the range changes and on certain ranges there are values missing from the output. What could be causing this?



2 Replies

@rn518 Well, that screenshot doesn't say much. What's in G2 on the other sheet, for instance? Most likely, it contains a value that is not a valid named range. For example, if it contains "xxx yyy" the named range would be "xxx_yyy" as spaces are not allowed in names for ranges.


On you can read about the rules for named ranges.

Names have rules

When creating named ranges, follow these rules:

  1. Names must begin with a letter, an underscore (_), or a backslash (\)
  2. Names can't contain spaces and most punctuation characters.
  3. Names can't conflict with cell references – you can't name a range "A1" or "Z100".
  4. Single letters are OK for names ("a", "b", "x", etc.), but the letters "r" and "c" are reserved.
  5. Names are not case-sensitive – "home", "HOME", and "HoMe" are all the same to Excel.

@Riny_van_Eekelen G2 contains a formula that produces a named range based on other variables in the sheet, and I have verified that the output is a valid name. However, I did remove the IFERROR from my formula to check this and that seemed to fix it, not sure what the issue was lol.