Hi Db,
1. I don't have issues of (1).
2. My plan was to create a macro going over each cell with auditing "trace
dependents". Then if I could "select" all the cells with dependents and copy
to a new sheet, or so.
I don't know if its doable.
3. The workbook is very large and doing it manually is tedious.
Liparo
On Fri, Jun 3, 2011 at 6:19 PM, david braithwaite
<dbraithwaite@charter.net>wrote:
>
>
> Hi
>
> Your Problem: delete everything on SheetX that is not referenced from
> another sheet in the same workbook.
>
> 1) I think what you are trying to do is impractical (not necessarily
> impossible) except in very narrow circumstances.
>
> For instance:
>
> a. on sheet1, A1 is "10" and B1 is "=A1"
>
> b. on sheet 2 A1 = "Sheet1!$B$1"
>
> result... you would delete A1 because it wasn't referenced from
> another sheet, but that would destroy the value in B1.
>
> 2) There is no automatic way of doing this, it needs manual or vba
> processing
>
> 3) Assuming you don't have the issues of the type in (1) , you need
> to:
>
> a. search all sheets and the name space for formula references to
> "sheetX!"
>
> i.
> Manually (for sheets) Find All "sheetX!" (options, within "workbook",
> look in "formulas")
>
> ii.
> Manually(names) open "name manager" and filter for "workbook scope names"
> and find those with "sheetX!"
>
> b. Union all their ranges together
>
> c. Delete the content of every used cell not in that union.
>
> 4) The tabulation, union, and deletion could also be done via VBA
>
>
> Db
>
> [Non-text portions of this message have been removed]
>
>
>
--
Lipa Roitman
[Non-text portions of this message have been removed]
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar