Cell Precedents

[Kaval]

I would like to determine for particular cells whether

1) they contain a formula
2) they have precedents
3) they have precedents on another sheet
4) they have precedents in another workbook

I can see what seems a longhanded way to determine the above (using
directprecedents method) but I wondered if there were any cute shortcuts?

For example, range("A1").directprecedents.count gives an error if there are
no precedents so I would need an error handling step.

Thanks, Kaval

 

[Jim Cone]

Kaval,

No shortcuts, just lots of code...
http://makeashorterlink.com/?K1AE311DA

Jim Cone
San Francisco, USA

 

[Robin Hammond]

Looks like Jim's code does 1 and 2 fairly exhaustively. For 3, you need to
use the navigate method for precedent arrows to actually determine anything
since the precedents property only returns cells in the same sheet. There's
a utility in my add-in below that will do exactly what you want (with a 30
day free trial).

For 4, I don't think it is possible. You can however, open the other
workbooks and find external references in them (again this is in my
utilities) and see if they match the book you are concerned about.

http://www.enhanceddatasystems.com/ED/Pages/XspandXLHome.htm

Robin Hammond
www.enhanceddatasystems.com