Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
List referenced columns in formulas?
I have a LARGE workbook that consists of a large block of data copied
out of our database (the "input block"), and a set of formulas that combine the values to produce user-readable results (the "user block"). I don't know beforehand what columns of data in the input block will be referred to by formulas in the user block, that's up to the user. What I would like to do is remove any unreferenced columns in the input block, after the input block is copied in and recalculations complete. I can't seem to find any way to "get all the column references used in this range" sort of method. Any pointers? Maury |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
List referenced columns in formulas?
Check out the Precedents and Dependents properties. Maybe you can work with
that. "Maury Markowitz" wrote: I have a LARGE workbook that consists of a large block of data copied out of our database (the "input block"), and a set of formulas that combine the values to produce user-readable results (the "user block"). I don't know beforehand what columns of data in the input block will be referred to by formulas in the user block, that's up to the user. What I would like to do is remove any unreferenced columns in the input block, after the input block is copied in and recalculations complete. I can't seem to find any way to "get all the column references used in this range" sort of method. Any pointers? Maury |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
List referenced columns in formulas?
On Feb 23, 1:02*pm, JLGWhiz wrote:
Check out the Precedents and Dependents properties. *Maybe you can work with that. As it turns out, you can! The one annoyance is that Precedents returns an error instead of null if there are none in the range, which means you have to use a trap. Other than that it's pretty easy to use. Simply loop over the area you're interested in, ask for a range's Precedents.Areas, and record which columns are in use. Then delete the ones that aren't. Excel's formula re-writer fixes everything up as they're removed. It cut my spreadsheet by about 10% in size. Maury |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting list of referenced data | Excel Discussion (Misc queries) | |||
Can't view formulas referenced tab | Excel Discussion (Misc queries) | |||
Excel Columns should be referenced in numbers not letters | Excel Discussion (Misc queries) | |||
multiple columns / rows to be referenced through a listbox | Excel Discussion (Misc queries) | |||
How to automatically change the referenced worksheet name in all formulas? | Excel Programming |