Forum Discussion
Indirect function not properly returning named range
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 https://exceljet.net/articles/named-ranges you can read about the rules for named ranges.
Names have rules
When creating named ranges, follow these rules:
- Names must begin with a letter, an underscore (_), or a backslash (\)
- Names can't contain spaces and most punctuation characters.
- Names can't conflict with cell references – you can't name a range "A1" or "Z100".
- Single letters are OK for names ("a", "b", "x", etc.), but the letters "r" and "c" are reserved.
- 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.
