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.

And just referencing the actual cell is not an option, i.e. =SheetName!B32? — MGP
– MGP, Commented Nov 10, 2015 at 13:49
I'd prefer to keep to table references if possible as we allow people to add new rows to the tables on the Validation sheet. Great shout though! — Dead.Rabit
– Dead.Rabit, Commented Nov 10, 2015 at 13:58
If the table where the reference is called from is big enough, then adding a new row won't be a problem. Only once the the table, from which you call the reference is "full", then this wont be added to your validation table. Can you use a Macro? — MGP
– MGP, Commented Nov 10, 2015 at 14:02
I recommend that you have only one table per worksheet and that all tables start in A1. I'm sure you don't want all those sheets, but it solves so many other referencing problems that it's worth it - IMO. — Dick Kusleika
– Dick Kusleika, Commented Nov 10, 2015 at 14:25

SQL Sifu · Accepted Answer · 2016-11-28 16:05:41Z

2

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.

answered Nov 28, 2016 at 16:05

SQL Sifu

714 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to reference a table ignoring the offset

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related