Non-adjusting cell references

The problem is less with Excel and more with the way you have chosen to set up your workbook, from what I have read so far.

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

Have you asked this question anywhere else? I only ask because of the name of the sample file. If you have, the forum rules require you to provide cross-post links, please.

I will take a look.

As I suspected, your design is not going to work. Why does the validation need to be on a separate tab at all? What is the purpose of the validation? There will be better ways of achieving what you want rather than trying to work around what is Excel's default and perfectly logical behaviour.

PS Why are you not using Excel tables?

Why can't it be in hidden columns?

The problem is the way you are allowing your people to work.

This is at best haphazard and is just asking for problems:

Telling users just not to move data around the sheet, is not really a sufficient solution.

Any solution you get here based on your current set-up will be no more than a sticking plaster solution. I would seriously urge you to reconsider your layout. What about using data validation on the first sheet? As I implied in my previous post, using tables might mitigate some (but not all) potential issues.

This is an accident waiting to happen - sorry.

Simply this: you are not going to find a way to reliably prevent the type of issues you are describing with the layout as it is and wanting to allow your workers to continue adding and pasting data in such a haphazard fashion. Wonky brickwork!

Also FWIW, Excel tables weren't something I've played around with before.

You seem to be where I was about ten years ago in your understanding of how best to arrange data. Been there, done that and got all the blooper medals!

If you can provide a slightly more detailed, desensitised workbook showing the structure of the data and explaining the validation checks you need to do, someone can advise you how best to structure this. You need a root and branch rethink. Unfortunately, I'm just off to my dental check-up, so can't help you right now.

Use below formula in Records Check Sheet
A2=IFERROR(IF(1/INDEX(Records!$1:$1048576,ROW(),MATCH(A$1,Records!$1:$1,0)),"VALID"),"INVALID")
B2=IFERROR(IF(1/(INDEX(Records!$1:$1048576,ROW(),MATCH(B$1,Records!$1:$1,0))+1),"INVALID"),"VALID")
C2=IFERROR(IF(1/(INDEX(Records!$1:$1048576,ROW(),MATCH(C$1,Records!$1:$1,0))+1),"INVALID"),"VALID")
D2=IFERROR(IF(1/INDEX(Records!$1:$1048576,ROW(),MATCH(D$1,Records!$1:$1,0)),"VALID"),"INVALID")
E2=IF(AND(INDEX(Records!$1:$1048576,ROW(),MATCH(E$1,Records!$1:$1,0))<>"",ISNUMBER(INDEX(Records!$1:$1048576,ROW(),MATCH(E$1,Records!$1:$1,0)))),"VALID","INVALID")

Try the above, Copy and paste towards down

Feel free to take advice here - there is a wealth of experience here, so you might as well use it rather than change things and then find you've driven yourself up another blind alley.