I have a table with an input column and have set up two tables with the allowable input values. The correct table depends on the value in a cell at the end of each row. To determine which table is allowed, I set up a data validation formula (like =if cellatendof row>0, table1, table2). I tried to drag the data validation down but it would not use a relative reference on the cell it has to check. So, I manually went into each cell in the column and updated the data validation formula to check the cell for that particular row (like =if cellatendofrow1>0... then next =if cellatendofrow2>0...).

This works but it will not allow for anyone to add or delete a row - or even sort the data - without making the data validation formula off. Instead of pointing to the new location of the cell, it points to the original row hardcoded. So if a row is added, now row 1 has moved to row 2 but the formula still looks at the cellatendofrow1 because it was hardcoded that way, as will all the rest of the rows, and the results will likely be invalid.

Is there a way to make the refernce relative rather than static and move with the rest of the row?

Sorry to say I do not know VB - but could try if that would fix it!

  • I have to ask: are you using A99, A$99, $A99, or $A$99? – Scott Sep 24 '12 at 18:43
  • Thanks @pnuts and @Scott!! The column at the end is always the the same, only the row reference would change. Tied to do a snipit to send a copy of the spreadsheet plus the DV screen but wouldn't paste. The exact formula is =IF($AM$4>0,ATTEND, NOATTEND). Then going down to the next one would change to =IF($AM$5>0,ATTEND, NOATTEND) and so on down the list. From the answer it sounds like I just need to remove the $'s in the formula. Will go try it - thanks!! Terry – Terry Sep 26 '12 at 15:24

If Table flag is say in ColumnC (always!) then formula as shown below seems to work (using named ranges):


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.