Drop downs to drive spreadheet

I have some questions that are all related to the same issue.

I’ve named a range of cells. Using the data validation function, I’ve created a simple drop down menu. Now I need this drop down to drive many things in the spreadsheet.

For example, I need formulas to change depending on the option selected in this drop down (i.e. they need to refer to different cells depending on the options selected from the drop down). I also need some cells to simply return the contents of different cells depending on the option selected from the drop down.

I’ve got some of the way towards this, but as the spread sheet gets bigger, its proving difficult. Currently I’m using the indirect function to read the contents of the drop down menu. As wel as naming all the cells as a range (for the drop down validation), I’ve also had to name each entry on the dropdown individually so I can use the indirect function again to return the row number to use in a formula. This appears to work, but as I continue it means I’m having to name every cell and its all getting a bit of a headache.

See attached, greatly simplified but it explains the basic problem. As I add rows (to sheet 2), I’m going to need to name a lot of cells… this would be troublesome.

Either I need to know how to automatically get it to name cells by its contents, or need to find a better way of doing this. Is there a way to get the row function to return the row value of the item in the drop down cell, without naming each cell in the drop down? Can anyone help? I’m really trying to avoid VBA if I can. I’m just not that good.

Thanks for your time