How to reference a table ignoring the offset

Question

Excel 2013: I have several single-column tables on a Lookup's sheet which are used to validate various columns around my workbook.

I now need to add a mapping table to map all values from one of these tables (tbl_Validation) to text values on a Settings sheet, so I'm creating a new table on Settings, I setup the table to be 2 columns and in the column formulae for the first:

tbl_Validation[ColumnA]

but since tbl_Validation starts in cell B23 where my new table is in B2 I'm getting #VALUE! errors on all rows. Swapping the formulae to INDEX(tbl_Validation,1,1) offsets the data correctly and finds a value but this doesn't work for a column formulae as it'll only select a single value for the whole column.

I've tried various approaches to making this a static reference: tbl_Validation[@ColumnA], tbl_Validation[[ColumnA]:[ColumnA]] and tbl_Validation[@[ColumnA]:[ColumnA]], the column doesn't display any data unless I move the whole table to B23 like the source table.

Answer 1

Try a slight tweak to your first attempt:

INDEX(tbl_Validation,ROW($A1),1)

Using a function and cell reference, "ROW($A1)" will force Excel fill-down the formula using a relative reference and hence give you the correct row numbers for the INDEX formula to work.