CODEWITHSUNDEEP
excel
rlw2235
rlw2235

Reputation: 19

Excel Data Validation using Offset

The range that i am wanting to place into my data validation is the following:

 =Offset(task_tbl,,G3-1,I3)

where:

 task_tbl - Data Table
 G3 = Column number of the data Table to search from
 I3 is the number of rows in that column which contain information

I am not sure why this is not working because When i try this formula external from the Data Validation (i.e in the excel sheet) and use the array function it returns the list perfectly.

If anyone has any suggestions i would greatly appreciate it.

Upvotes: 0

Views: 538

Answers (1)

Tom Sharpe
Tom Sharpe

Reputation: 34255

Well it is possible as this mini example is meant to show using this custom formula:-

=MATCH(G2,INDIRECT("Table1["&INDEX(INDIRECT("Table1[#Headers]"),$G$1)&"]"),0)

so it looks up the appropriate column name in the table headers then gives you the whole column with that name as the header. G1 selects the column and G2 is the cell we are trying to validate.

enter image description here

This also works as a drop-down list using this formula:-

=INDIRECT("Table1["&INDEX(INDIRECT("Table1[#Headers]"),$G$1)&"]")

This offset formula also worked perfectly for me with the same table, 1,2 or 3 in G1, 3 in I3 and task_tbl defined as $A$2:-

=OFFSET(task_tbl,,$G$1-1,$I$3)

Upvotes: 2

Related Questions