I am using Excel 2008 for Mac, and I am struggling with data validation. I am new to this area of Excel so apologies if I use the wrong terminology, but I will try and be as clear as possible!

I have a spreadsheet which is set up like this:

Column A: data from a drop-down list pulled from a named range in another sheet
Column B: needs to have data pulled from other named ranges in the other sheet based on what is selected in column A. At the moment this is set up by using Data Validation and setting it to allow a list, then using =INDIRECT(A1) in the Source box. This seems to work.

But somewhere along the line it has got a bit mucked up further down the sheet, and the =INDIRECT reference is pointing to lines which are miles down the spreadsheet. Is there any way of copying this down over rows, so that the cell reference increments by 1 row? I have tried using the 'apply to all other cells with similar settings' but it only seems to work on the next few rows.

I think there may be an issue with the data validation process itself, as when I open up the box from the menu i get an error sound and it jumps to a different line than the one which was selected when I opened it. Has anyone else encountered this issue?

I hope that someone can make sense of this, and can help me!