software

I have been able to develop a number of smart lookups using dynamic lists and
conditional lists. I now need to take things a step further and to create a
dynamic list that does not contain a (or certain values)

For example I have three columns

Sample Standard Reference
UG1234
UG1235
UG1236 REPLICATE UG1234

The Reference column can contain a value from the list of values in the
Sample column when the value of the Standard column equals Replicate, this
list of values should not include the value of the Sample column in the
current row (in the above case the lookup would by UG1234 or UG1235. I have
a ormula which gives th list of all samples when the Standard column equals
replicate, now I need to exclude the current sample value.

The current formula I am using is
=OFFSET(INDIRECT($F2),0,0,COUNTA(INDIRECT(F2amp;quot;Colquot; ))-COUNTBLANK(INDIRECT(F2amp;quot;Colquot;)),1)

On a related subject how are you supposed to edit and move around in the
Data : Validation : Source. The only way I have found to do it is to copy
the ormula to an empty cell, in the spreadsheet, edit it and copy it back.
This does not seem right.

Thanks for any help anyone can render