Data Validation using a Named Formula

[craig159753]

So I am using data validations to create drop down menus, however they act dynamically, and so the list generate depends on the value on the current sheet.

To do this i select the cells i want to add the data validation to, and then select data validation from the toolbar, then set Allow to LIST and then past this code in the SOURCE bar

=IF(ISBLANK($B$2),"",INDIRECT("'SDTMIG 3.1.2'!$E$"&MATCH($B$2,'SDTMIG 3.1.2'!$C$2:$C$872,0)+1&":$E$"&LOOKUP(2,1/('SDTMIG 3.1.2'!$C$2:$C$872=$B$2),ROW('SDTMIG 3.1.2'!$C$2:$C$872)-ROW('SDTMIG 3.1.2'!$C$2)+2)))

It works fine for the first sheet I define the new formula name. But if i go back to look at the code it has changed ever so slightly, and does not work on the other sheets properly. The change makes the formula look like this

=IF(ISBLANK(SHEET1!$B$2),"",INDIRECT("'SDTMIG 3.1.2'!$E$"&MATCH(SHEET1!$B$2,'SDTMIG 3.1.2'!$C$2:$C$872,0)+1&":$E$"&LOOKUP(2,1/('SDTMIG 3.1.2'!$C$2:$C$872=SHEET1$B$2),ROW('SDTMIG 3.1.2'!$C$2:$C$872)-ROW('SDTMIG 3.1.2'!$C$2)+2)))

So you can see for some reason it adds the extra "SHEET1" to three parts of my formula, but i simply want to be $B$2. The scope of the named formula is the whole work sheet.

Is there a way to stop SHEET1 popping in?

 

[Domenic]

Try replacing each instance of...

SHEET1!$B$2

with

!$B$2

Hope this helps!