Using Data Validation OFFSET and MATCH populate field if only one match

0
votes

I need to know how to force an answer to be entered when there is only one match in the data validation formula below. I know there is a simple answer to this, but it escapes me.

I have a table (Table1 on sheet 1) with 2 columns. Column 1 has numbers (item numbers), some of which are duplicates. Column 2 has descriptions, none of which are duplicates.

Table1

Table2

On a different sheet (sheet2) in the same workbook, we have to be able to select the item number first (in column H), then select a description for that item number (column I).

I created a separate table (Table2 - also on sheet 1) of all of the Table1 column 1 numbers and removed all duplicates.

I then created 3 named ranges:

  • ItemColumn - from Table1 column 1 (everything in Table1 column 1)
  • ItemLabel - from Table1 column 1 (this is only the Table1 column 1 header)
  • Item_Only - from Table2 (this is the item number table with no duplicate values)

On sheet 2, the data validation formula for each cell in column H is from Table2. The data validation formula is:

=Item_Only

This works and gives a drop down with all possible item numbers! Column H is (and must be) blank until an option is selected.

On sheet 2, the data validation formula for each cell in column I is from Table1. The data validation formula is:

=OFFSET(ItemLabel, MATCH($H2, ItemColumn, 0) -1, 1, COUNTIF(ItemColumn,$H2), 1)

This works and gives a drop down of all possible descriptions where the item number matches what was selected in column H of that row! Column I is (and must be) blank until an option is selected (or unless we can get the option below working).

What we would like is: If there is only one description match for the column H item number selected, that it be automatically populated in column I and that there be no need to use the drop down arrow. Otherwise, column I would be blank and the drop down arrow be available. (Yeah! We're lazy and proud of it!)

I'm thinking it should be an IF statement similar to the following, but I can't get anything to work. Can you have an OFFSET formula in an IF statement, or an IF statement in and OFFSET?

=IF($H2 = "", "", IF(<COUNTIF to see if there is only 1 matching value to $H2>,<VLOOKUP formula>,<OFFSET/MATCH formula>))

Any suggestions?

1
excelvalidationmatchoffset

1 Answers

0
votes

I'm getting a bit lost in your explanation, but conceptually:

Yes, it is possible to have a formula in a data validated cell. To do that, first enter the formula, then add the data validation.

If you use the data validation drop-down to populate the cell, then the formula will be overwritten and cannot be restored.

If I understand your description correctly, the formula in the cell would just have to point to the last range name in the chain of cascading data validation range names, check the number of its elements and return the element if the count equals 1. I can't tell from your description what that range name is. 

=if(counta(TheRangeName)=1,TheRangeName,"")

If you want the formula to be restored when a previously picked value is cleared from the cell, you will need to engage VBA.