Hi Folks,
I have a data set which has role titles in the first column and country names as headers. Each column contains a role cost for each country. I am trying to create a dynamic Data Validation list where it will show only roles where cost is greater than zero for the country selected.
The following formula when pasted into a cell and dragged down works (I thought I would then point the validation to this cell reference):
=IFERROR(INDEX($A$20:$A$38,SMALL(IF($J$20:$J$38>0,ROW($A$20:$A$38)),ROW(1:1))-19,1),"")
Although I want the "cost" column ($J$20:$J$38 in the above example) to float based on a country match. I have tried a combination of OFFSET and MATCH which works by itself. Although when added to the IF formula (encased inside SMALL) it simply doesn't work.
Any ideas?
Thanks,
N
I have a data set which has role titles in the first column and country names as headers. Each column contains a role cost for each country. I am trying to create a dynamic Data Validation list where it will show only roles where cost is greater than zero for the country selected.
The following formula when pasted into a cell and dragged down works (I thought I would then point the validation to this cell reference):
=IFERROR(INDEX($A$20:$A$38,SMALL(IF($J$20:$J$38>0,ROW($A$20:$A$38)),ROW(1:1))-19,1),"")
Although I want the "cost" column ($J$20:$J$38 in the above example) to float based on a country match. I have tried a combination of OFFSET and MATCH which works by itself. Although when added to the IF formula (encased inside SMALL) it simply doesn't work.
Any ideas?
Thanks,
N
