EXPAND DATA-VALIDATION-FORMULA TO OTHER ROWS

Copper Contributor

OK so First Time here and I have searched the internet and can't find answers anywhere. 

 

IN ROW C4 I USED LIST DATA VALIDATION LIKE THIS: >>

ALLOW TO: >>LIST:

>>SELECTED THE CELL THAT CONTAINED THE FORMULA. IN THIS PARTICULAR CASE THE FORMULA IS IN CELL V4 SO IN SOURCE IT LOOKS LIKE THIS =$V$4#.  

ENTER

THE FORMULA IS=FILTER($R$4:$R$212,ISNUMBER(SEARCH(C4,$R$4:$R$212)), "NO EXISTE") where C4 IS CONCEPT 

IN THE ERROR ALERT I deselect "show error alert after invalid data is entered" .>>>

this allows me to click cell C4 and type in the first few letters of the "concept" im interested in finding

then I hit enter then a list of possible matches pulls down. Everything until now works FINE.  

 

My issue is I want to pull down the formula from C4 to possible C50? it doesn't pull down correctly. I have an idea as to why but don't know how to fix it. 

I have tried using PASTE as Validation.

The issue here is I need the formula to change as I expand to other rows, for example:

when I expand the formula to row C5, the formula would need to change to =FILTER($R$4:$R$212,ISNUMBER(SEARCH(C5,$R$4:$R$212)), "NO EXISTE") 

if I want to expand to C6, the formula would need to change to =FILTER($R$4:$R$212,ISNUMBER(SEARCH(C6,$R$4:$R$212)), "NO EXISTE")

 

is there a way to do this, IM so new at this, literally got this far watching YOUTUBE. Thank y'all for any help.

 

Attached worksheet in case I didn't know how to explain myself.

 

Thank You,

Maria

0 Replies