0

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.

Dead.Rabit
  • 1,965
  • 1
  • 28
  • 46
  • 1
    And just referencing the actual cell is not an option, i.e. `=SheetName!B32`? – MGP Nov 10 '15 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 Nov 10 '15 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 Nov 10 '15 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 Nov 10 '15 at 14:25

1 Answers1

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.

SQL Sifu
  • 71
  • 4