How to determine which columns are used in formulas?

[Maury Markowitz]

This is going to be a bit strange, so bear with me...

I have a report that consists mostly of formulas pointing to a large
"block" of data pasted into the sheet starting at column BA. The data
in question comes from our SQL based accounting engine via a stored
proc. I call the proc, use GetRows, rotate the array (is there a built
in function for this?) and the paste it in starting at BA5.

Now over in cols A to Z is the "user area" where the user types in
whatever formula they want, mixing and matching the accounting data as
they see fit.

The problem is that there are about 140 columns of data in the
accounting dump. This makes the spreadsheets rather large, about 10
MB. What I would like to do is find out which columns are actually
being referred to from the formulas, and delete any that aren't being
used.

So, is there a way to do this?

Maury

 

[Gary''s Student]

It is clearly possible to loop over the user area, find each equation, find
the precedents of the equations and map them into the data area, and then
delete un-used columns.

HOWEVER

I don't recommend doing this:

1. If the user wants to change equations, necessary data may have already
been deleted

2. The process will have to be repeated each time the data is refreshed.

3. Configuration management will be more difficult since two copies of the
data will need to be maintained.

I suggest using linking formulas to an external workbook. The user workbook
can be small and the data can be more easily controlled.

 

[Maury Markowitz]

It is clearly possible to loop over the user area, find each equation, find
the precedents of the equations and map them into the data area, and then
delete un-used columns.

So how does one "find the precedents"?

I don't recommend doing this:

I might consider the linked-spreadsheet idea, but I have found in the
past that the slightest network hick makes the links stop working and
random errors start appearing in dialogs. You also start running into
all sorts of problems with locking behavior, which is never a good
thing in Excel.

Maury