Validation issue when copying cells

Hi All,

I'm trying to make a spreadsheet and duplicate the function of pull down lists by using copy and past. I have a series of recipes which I want to choose key ingredients from. I want the pull down list for each key ingredient to be restricted to the recipe for that row, choosing ingredients in from the cells to the right. The spreadsheet will be automatically populated with content from the recipes. Sample below:

ID Recipe Key ingredient1 Key ingredient2 Key ingredient3 Key ingredient4 Full ingredients list
0001 fish pie potatoes hake haddock haddock hake potatoes peas
0002 steak pie mash beef beef pastry onions mash
0003 beans on toast butter tabasco bread beans bread butter tabasco
0004 pasta bake cheese parsley pasta tuna cheese parsley

This is all OK, the problem I am having is that when I copy the validation formatting and paste it beneath the for more recipes, the validation still refers to the original ingredient list and not the cells on the same rows I have copied to. There may be a better way of creating drop down lists based on values in the same row. If so, I'm all ears!

Thanks!

Jimbo

Sorry, just realised the formatting did not come through properly! Let me know if you need more clarity on this!

Might help with attachment:

Hmmm... wondering if this is in the right group... not had any answers...

This is the formula you are using

Please Login or Register  to view this content.

Change the same like this by editing in Data validation box itself.(Removing absolute reference by deleting "$")

Please Login or Register  to view this content.

Now You copy.

Perfect, I should have seen the absolute reference in the validation. It's strange that it defaults to that.

Thanks!

Pl mark the thread SOLVED>