Formula to identify data type according to criteria list

[Billaus]

Hi there,

I am dealing with a large amount of sheets and tables and I need to run some quality checks for some modelling. As part of this process I need to identify the "nature" of the data provided. More precisely, I need to assess whether the data is hard-coded, derived (with a formula) or linked (= to another cell in the model).

I have attached a picture to explain this better.

excel1.bmp

Number 4 and 5 in cells A2 and A3 were manually inserted (hardcoded), value in cell A4 is equal to "=A2*A3" (derived) and then value in cell A5 is simply "=A4" (linked).

Instead of assigning the data type manually, is there a formula or a macro on excel that will allow me to assign the data type automatically to all values in a sheet? I tried ISREF, ISNUMBER, ISTEXT, etc. as well as TEXT and others, but they don't seem to be working correctly.

Any idea? Happy to hear suggestions also on how to make it faster and easier!

[RobertMika]

Hello and welcome.
This would cover first two cases

Please Login or Register  to view this content.

with the "LINK" option that woudl be hard as formula is a link and we need to take into account all non calcaultions which is almost impossible.