Search code examples
excellistvalidationexcel-formula

Excel validation not accepting a relative row reference

My goal is to create a list validation that is dependent on what is selected for the cell directly to the left of it. Here is my formula: =OFFSET($I$2,1,MATCH($D2,$I$2:$O$2,0)-1,COUNTA(OFFSET($I$2,1,MATCH($D2,$I$2:$O$2,0)-1,20)),1) The problem seems to be with the '$D2'. Now when I paste this formula directly into the cell it lists out the expected results. If I paste the formula into a below empty cell I can also see the '$D2' change its row value accordingly and I see the expected list of options print out. enter image description here

If I copy and paste this into a list data validation I receive an error. enter image description here

If I change the relative row reference and make it an absolute cell reference '$D$2' the validation works, but I need each 'Location Section' row to be relative to the 'Object Type' on its' same row. Why would this reference not work as part of a validation?

NOTE: I only have access to a Sharepoint Microsoft 365 version of Excel. I don't have access to Excel options/settings. I've also noticed I don't have access to paste validations.

enter image description here]

Solution

  • I was able to fix this problem by using the actual Excel app. For whatever reason the in browser version of Excel does not allow for this type of reference. It also has limited paste special options and also doesn't have access to the name manager under formulas tab. I also did significantly reduce down my formula to =INDIRECT($D2) like @ScottCraner suggested.