Jun 20 2022 10:11 AM
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!
Jun 20 2022 10:26 AM
That is Excel 2003-thinking.
Since Excel 2007 you can use Excel tables and structured references.
Jun 20 2022 11:19 AM
Jun 20 2022 11:52 AM
Depends on how you build the formula. In structured tables that is per-row reference, formula is automatically populated on entire table.
Jun 20 2022 04:10 PM
Jun 21 2022 02:07 AM
Jun 21 2022 09:47 AM
Jun 21 2022 10:11 AM
@ElRafaVaz33 , you are welcome