I have a worksheet using raw data that Excel does not identify as Tables (ie. plainly putting values in adjacent cells), and on another part of my worksheet, formulas doing lookup in that data, like
Things are going perfectly well, until I decide to declare my raw data as a table (Ctrl-T) to get some extra functionality, like auto-formatting and auto table expansion.
Once the table declared, all my formulas find #N/A in that table, whether I reference the search zone as cell reference (
Config!$C$4:$E$8) or by its new table name (modified via the name manager).
A clue (I don't know what it's worth) is that the lookup key (
D$1 in my formula) and the data keys (leftmost column of
Config!$C$4:$E$8) are numeric (values like
2015). Excel seems to use text lookup when inside a table and fail to match
2015 (in the table) with
D$1). This is only a clue, I may be wrong on this.
What can I do to start using tables (auto expanding is really useful) without breaking my fomulas?
EDIT : Things break when the lookup key (
D$1) starts being part of a table: 2015 is now seen by formulas as "2015"