OFFSET DV List stops working on each close and open of the workbook

It's not a huge issue but I was wondering if anyone else has come across this happening and if you found a solution for it.

I'm using Excel 2016 and have a data validation list with an OFFSET formula in E9 that provides the selections based on the values chosen from two DV lists before it.

OFFSET(ProjectRequests_ProjectName,MATCH(C9&D9,ProjectRequests_ForYearCol&ProjectRequests_ForPropertyCol,0)-1,0,COUNTIFS(ProjectRequests_ForYearCol,C9,ProjectRequests_ForPropertyCol,D9),1)

It works great and finds exactly what it needs to without fail. However, when I close the workbook and then open it again it acts as if the formula doesn't work or there is no data in the previous cells referenced until I unlock the sheet, open the DV list and click OK. At which point, it works great again until the next time I close and open it.

Weird thing is I have a few other dependent DV lists setup nearly the same other than multiple COUNTIFS criteria and they have no issues...

Is there something wrong with my COUNTIFS syntax? Or, possibly do I have an obscure excel setting that might be causing it?

If you have any ideas you can pass along, thank you.

Hi David,

Thanks for the suggestion. I checked and F9 makes no difference. Also checked the setting in options and workbook calculation is set to automatic. I'm stumped. Never had one do this.

Hi Sandy,

Thanks for the suggestion. Neither are calculated; DV or cell formula.

I will try to trim this workbook down today and post an example workbook. Maybe in doing so, I'll find the problem.

Hi Sandy,

As promised, an example workbook. It's still doing the same thing when closed and opened on that DV List. Really, for the DV List problem, it only needs the 2 Project related sheets but, I included other sheets too, mostly for the Vendor related dependent DV lists so you could see they calculate fine.

Let me know if you have any questions.

Thanks again for having a look.

I think you need edit all DV lists and remove check mark from Ignore blank.
I did it and it works. (except of others errors )

WRONG
Analyse workbook by INQUIRY tab and all dependencies of DV lists.

I ran the workbook analysis with cells with dependents and for validation criteria. I don't see anything bad reported... It listed the OFFSET formulas for those DV lists but, nothing saying "hey we have a problem".

I've never used the Inquiry tab. Didn't even know it was available. Am I just not seeing something in the output file?

INQUIRY
Copy formula from not-working DV into blank cell, confirm Control+Shift+Enter, save and try INQUIRY relationship

for now I got: DV don't like MATCH() with coop. to OFFSET().
What can be used for row number insted of MATCH() ?

In fresh new workbook, your construction (OFFSET etc...) in DV doesn't work

If I change MATCH(...) to e.g. 3, save, close, open, DV works well

=OFFSET(J10:J16,4-1,0,SUMPRODUCT((E10:E16=H2)*(H10:H16=J2)),1) =Ok.

MATCH($H$2&$J$2,E10:E16&H10:H16,0) = 4

I changed COUNTIFS to SUMPRODUCT to eliminate steps of errors

(ranges are not suitable to your example )

I think I found solution

everywhere where MATCH($G$4&$H$4,PR_ForYearCol&PR_ForPropertyCol,0) is used directly in DV change it to name (e.g. matchrow) and use this name insted of MATCH.

Sorry , I changed some names to shorter names

DV probably does not like concatenated ranges

or simply change on:
ProjectRequests
G4&H4 to name lookupvaluePR
PR_ForYearCol&PR_ForPropertyCol to name lookuparrayPR
and use in DV: .... MATCH(lookupvaluePR , lookuparrayPR , 0) ....

ProjectEstimates
C9&D9 to name lookupvaluePE
and, becaue this is the same range, use lookuparrayPR
DV: ... MATCH(lookupvaluePE , lookuparrayPR , 0) ....

♩♪♬♫♭♮♫♩♭♬♩♪♬♫♭♮♫♩♭♬

DMG and Sandy,

Thank you both. Both solutions work! Thank you both for helping me. I definitely learned some new things and that's always good.

I don't understand why it would work with the named formula, or part of it being a named formula and not when just using the formula itself? It wouldn't have occurred to me to try this.... Is this an Excel bug, like a memory leak that gets closed by naming the formula?