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!
A99,A$99,$A99, or$A$99?