I need to extract some data from a rather complex workbook with about a bunch of worksheets. Starting from one worksheet, I need to trace the source of some cells back through two or more worksheets to their origins, then fetch the data from other cells in the originating workseet. (Hopefully my terminology is valid.)
(I.e. Worksheet First cell A1 is a direct copy of Worksheet Second cell B2. The formula for A1 is "=Second!B2" My VBA code needs to discover worksheet Second and fetch data from that worksheet. And maybe worksheet Second is fed from worksheet Third cell C4.)
My current thoughts are to check the formula of the cell. If it contains "=" then I can examine the text of the formula and extract the name of the next worksheet. When there are no more "=" symbols the originating cell has been discovered.
However, I suspect there is a more elegant method of accomplishing this. What is the best method for this task?
(I.e. Worksheet First cell A1 is a direct copy of Worksheet Second cell B2. The formula for A1 is "=Second!B2" My VBA code needs to discover worksheet Second and fetch data from that worksheet. And maybe worksheet Second is fed from worksheet Third cell C4.)
My current thoughts are to check the formula of the cell. If it contains "=" then I can examine the text of the formula and extract the name of the next worksheet. When there are no more "=" symbols the originating cell has been discovered.
However, I suspect there is a more elegant method of accomplishing this. What is the best method for this task?
