List the cells with data validation rules referring to other sheets/books.

Hello!

Is something like this already out there? :

"On a new sheet, list the name of any sheets and those cells which contain data validation rules which refer to values on other worksheets or other workbooks."

?

VR/Lost

Pike,

I put your code in a module (Excel 2003, BTW), and ran it, but I get the Run-time error 1004 (Application-defined or object-defined error.)

I am not savvy enough in VBA to fix it.

Any ideas?

VR/Lost

All,

I appreciate your help and am giving your codes a try.

BTW, this is not a assignment question for any class. It is a follow-up to my earlier post http://www.excelforum.com/excel-gene...s-on-list.html

For some cells, so I don't have to set up the conditional formatting and data validation, I cut-and-paste cells from other workbooks. I am thinking that when I do that, the path gets carried into the current workbook and then perhaps gets added to that Go To list in addition to the Named ranges.

When I open the workbook using Excel 2010, the compatibility checker gives me the error "One or more cells in this workbook contain data validation rules which refer to values on other worksheets. These data validation rules...."

But it only tells you the sheet, not the specific cells on the sheet causing the error.

So I asked that question and am hoping that the codes will reveal any problems.

HTH!

VR/Lost

All,

I really appreciate your help!

I modified Nimrod's code to include more properties that I wanted to see:

Please Login or Register  to view this content.

It doesn't like the OFFSET type of ranges (dynamic, growable). (=OFFSET('Sheet1'!$O$1,0,0,MAX(1,COUNTA('Sheet1'!$O:$O)),1) (It gives those a #VALUE error.)

But it does show places where I checked "Ignore Blank" for one cell and missed it on others. So, it's a good reporting tool. Thanks!


snb,

I tried your code, but am missing something somewhere because it is causing an Error 92 (For loop not initialized):

Please Login or Register  to view this content.

romperstomper: Well, that explains some things. Thanks!

VR/Lost