Forum Discussion
Referencing all non-blank values in a row/column
Hello Microsoft Community,
I've been building up a large worksheet that has several sheets using functions such as XLOOKUP & VLOOKUP from a series of tables. I expect to use this sheet for several months and constantly add more data points to the tables. I'm running into issues though, where, if I add data to the tables I have to manually go over every sheet and update the lookup formula's range.
In Google Sheets you can reference an entire column/row within a formula and it will return the array of all non-blank cells. Is this possible in Excel 365?
If not, the other solution I was thinking of was using INDIRECT everywhere and reference the last row/column, but I fear this might be expensive and bulky.
Any other methods in which I could optimize the worksheet?
All ideas are welcome 🙂
Thanks!
7 Replies
That is Excel 2003-thinking.
Since Excel 2007 you can use Excel tables and structured references.
- Hey Detlef, thanks for the answer.
The issue with Tables is that they don't allow to have formulas inside of them; I always get #SPILL errors if I try to change my data to Tables 😕Depends on how you build the formula. In structured tables that is per-row reference, formula is automatically populated on entire table.
