The Problem:
I've come across an incredibly simple solution that's solves a rather complex problem. I need to return the currently selected cell so I could determine the row the user was selecting to setup dynamically created dependent validation lists. I initially had a SelectionChange VBA code that did this however as with most uses of the SelectionChange event the side effect was that UNDO was disabled. To get around this I started looking for a formula method to report the currently selected cell and found I could do it with the Cell("row") function. The problem is that Cell() function only updates when a change occurs. As long as the user step through my validation lists it would work however if they changed 2 of the 3 validation lists and then moved to a different row for the final validation list that validation list would dump the list referencing the values selected in the first two validation lists since the move to the other row didn't trigger a change. And thus we have our problem with using the Cell() function.

The Solution:
Please Login or Register  to view this content.
Below is the formula on Sheet 2 that's hidden in the Z column as well as "Z12" above. This is one of two reference cells used to pick the criteria to match for the validation list.
=INDIRECT("Sheet1!A"&CELL("row"))

What this does is just write "Magic" to Z12 every time the user selects a different cell which causes the Cell("row") function to update reporting the currently selected row and doing this doesn't break UNDO.

Now if can just figure out how to solve the two wide validation lists problem I'd be done. I have no merged cells on this sheet and have recreated the lists and they still keep coming back two wide intermittently. Very strange problem/Bug.