Trace dependents

[Dean]

I have (sigh) inherited an EXCEL file that uses range names extensively, a
practice that, while valuable for some users doing sanity checks, is not
that valuable when you are trying to carefully check everything, at least as
I see it. I notice a number of curious things.

How do I get the equations to show the underlying cells rather than their
range names?

Does using range names have any effect on trace dependents and trace
precedents? For example, when I go to one cell and hit trace precedent, it
shows me a precedent. When I select that precedent and go there, then hit
trace dependents, it says there are none! I notice that the latter cell has
a range name. Would that cause this not to show up?

It seems that, for some range names, the same range name is used multiple
times, i.e., only once per worksheet, but on multiple sheets. Is it true
that EXCEL has no problems with the same name being used multiple times in a
file? If so, does the range of cells always need to be in the exact same
locations on each sheet? I wanted to have a map of all range names, but
when I do the commands: insert, name, paste, paste link, I'm not sure it
knows which of the identical range name cells to map to - perhaps it just
chooses the one on the nearest worksheet. Does anyone know?

Please answer ONLY what you know right away. I don't need every answer
right away.

Thank you very much,
Dean

 

[Debra Dalgleish]

If you're working with names, you might like the Name Manager, a free
add-in that makes it easy to work with defined names. You can download a
copy:

http://www.bmsltd.ie/MVP/Default.htm

under the heading for Jan Karel Pieterse

 

[Dean]

Thanks, this looks really nice, though may be a bit over my head. I just
installed it. Do you also know any of the answers to my questions?

Thanks again.
Dean

 

[Debra Dalgleish]

If you use the Paste List command, it lists only the global names, and
names for the active sheet. The Name Manager will show all the names.

For example, each sheet can have a range named Print_Area. The range
doesn't have to be in the same cells on each sheet in order to use the
same name.

If you spend some time exploring the workbook with the Name Manager, it
may give you a better understanding of how things are set up, then it
may be easier to dissect the formulas.

 

[Dean]

Thank you. Regarding the trace dependents question, when I saved the file
and closed out of EXCEL, and got back in, the trace dependents worked. This
seems to be the phenomenon I've seen before - that sometimes, for whatever
reason, EXCEL gets overloaded and does inexplicable things (like if you
update cell c1; but cell c2 which is set equal to C1, doesn't show the
update), and that all it takes is to close out of EXCEL and get back in. It
drives me crazy that you can never be sure when such errors temporarily
exist. But that's a separate issue.

I still have the following question: How do I get the equations (near the
upper left, is it called the formula bar)to show the underlying cells rather
than their range names? Is there an option in EXCEL to do this?

Thank you.

D

 

[JBoulton]

If you delete the range name, the formula bar will change to the cell address.

HTH

 

[Robin Hammond]

Dean,

There's a routine in the XspandXL add-in on my site that might help. It will
produce a report of multi-level precedents or dependents up to a level that
you choose. Here's some abridged output from a 3 level trace. You'll see
that the named range is referred to by address in the HasPrecedent column.
This will probably get messed up by text wrapping, but you should be able to
get the idea.

Test Cell
With Formula
Has Precedent
Next Stage

DbInput!$F$57
=IntIncCustomer+IntIncTreasury
DbInput!$F$55
Testing DbInput!$F$55

DbInput!$F$55
=+Main!E341
Main!$E$341
Testing Main!$E$341

Main!$E$341
=SUM(E331:E340)
Main!$E$331
Max level exit

DbInput!$F$57
=IntIncCustomer+IntIncTreasury
DbInput!$F$56
Testing DbInput!$F$56

DbInput!$F$56
=+Main!E344+Main!E345
Main!$E$344
Testing Main!$E$344

Main!$E$344
=+E355*(D66+E66)*0.9/2
Main!$E$355
Max level exit



Robin Hammond
www.enhanceddatasystems.com

 

[Jim Rech]

It will only do this is you have Transition Formula Entry under Tools,
Options, Transition set on.

--
Jim Rech
Excel MVP
| If you delete the range name, the formula bar will change to the cell
address.
|
| HTH
|
| "Dean" wrote:
|
| > Thank you. Regarding the trace dependents question, when I saved the
file
| > and closed out of EXCEL, and got back in, the trace dependents worked.
This
| > seems to be the phenomenon I've seen before - that sometimes, for
whatever
| > reason, EXCEL gets overloaded and does inexplicable things (like if you
| > update cell c1; but cell c2 which is set equal to C1, doesn't show the
| > update), and that all it takes is to close out of EXCEL and get back in.
It
| > drives me crazy that you can never be sure when such errors temporarily
| > exist. But that's a separate issue.
| >
| > I still have the following question: How do I get the equations (near
the
| > upper left, is it called the formula bar)to show the underlying cells
rather
| > than their range names? Is there an option in EXCEL to do this?
| >
| > Thank you.
| >
| > D
| >
| > | > > If you use the Paste List command, it lists only the global names, and
| > > names for the active sheet. The Name Manager will show all the names.
| > >
| > > For example, each sheet can have a range named Print_Area. The range
| > > doesn't have to be in the same cells on each sheet in order to use the
| > > same name.
| > >
| > > If you spend some time exploring the workbook with the Name Manager,
it
| > > may give you a better understanding of how things are set up, then it
may
| > > be easier to dissect the formulas.
| > >
| > > Dean wrote:
| > >> Thanks, this looks really nice, though may be a bit over my head. I
just
| > >> installed it. Do you also know any of the answers to my questions?
| > >>
| > >> Thanks again.
| > >> Dean
| > >>
| > >>
| > >> | > >>
| > >>>If you're working with names, you might like the Name Manager, a free
| > >>>add-in that makes it easy to work with defined names. You can
download a
| > >>>copy:
| > >>>
| > >>> http://www.bmsltd.ie/MVP/Default.htm
| > >>>
| > >>>under the heading for Jan Karel Pieterse
| > >>>
| > >>>
| > >>>Dean wrote:
| > >>>
| > >>>>I have (sigh) inherited an EXCEL file that uses range names
extensively,
| > >>>>a practice that, while valuable for some users doing sanity checks,
is
| > >>>>not that valuable when you are trying to carefully check everything,
at
| > >>>>least as I see it. I notice a number of curious things.
| > >>>>
| > >>>>How do I get the equations to show the underlying cells rather than
| > >>>>their range names?
| > >>>>
| > >>>>Does using range names have any effect on trace dependents and trace
| > >>>>precedents? For example, when I go to one cell and hit trace
precedent,
| > >>>>it shows me a precedent. When I select that precedent and go there,
then
| > >>>>hit trace dependents, it says there are none! I notice that the
latter
| > >>>>cell has a range name. Would that cause this not to show up?
| > >>>>
| > >>>>It seems that, for some range names, the same range name is used
| > >>>>multiple times, i.e., only once per worksheet, but on multiple
sheets.
| > >>>>Is it true that EXCEL has no problems with the same name being used
| > >>>>multiple times in a file? If so, does the range of cells always
need to
| > >>>>be in the exact same locations on each sheet? I wanted to have a
map of
| > >>>>all range names, but when I do the commands: insert, name, paste,
paste
| > >>>>link, I'm not sure it knows which of the identical range name cells
to
| > >>>>map to - perhaps it just chooses the one on the nearest worksheet.
Does
| > >>>>anyone know?
| > >>>>
| > >>>>Please answer ONLY what you know right away. I don't need every
answer
| > >>>>right away.
| > >>>>
| > >>>>Thank you very much,
| > >>>>Dean
| > >>>
| > >>>
| > >>>--
| > >>>Debra Dalgleish
| > >>>Excel FAQ, Tips & Book List
| > >>>http://www.contextures.com/tiptech.html
| > >>>
| > >>
| > >>
| > >>
| > >
| > >
| > > --
| > > Debra Dalgleish
| > > Excel FAQ, Tips & Book List
| > > http://www.contextures.com/tiptech.html
| > >
| >
| >
| >

 

[Jim Rech]

You might try this macro. Select the range of formulas to be "de-named"
first:

Sub DenameFormulas()
Dim Cell As Range
ActiveSheet.TransitionFormEntry = True
With Selection
If .Areas.Count > 1 Then
For Each Cell In .SpecialCells(xlFormulas)
Cell.Formula = Cell.Formula
Next
Else
Selection.Formula = Selection.Formula
End If
End With
ActiveSheet.TransitionFormEntry = False
End Sub


--
Jim Rech
Excel MVP
| Thank you. Regarding the trace dependents question, when I saved the
file
| and closed out of EXCEL, and got back in, the trace dependents worked.
This
| seems to be the phenomenon I've seen before - that sometimes, for whatever
| reason, EXCEL gets overloaded and does inexplicable things (like if you
| update cell c1; but cell c2 which is set equal to C1, doesn't show the
| update), and that all it takes is to close out of EXCEL and get back in.
It
| drives me crazy that you can never be sure when such errors temporarily
| exist. But that's a separate issue.
|
| I still have the following question: How do I get the equations (near the
| upper left, is it called the formula bar)to show the underlying cells
rather
| than their range names? Is there an option in EXCEL to do this?
|
| Thank you.
|
| D
|
| | > If you use the Paste List command, it lists only the global names, and
| > names for the active sheet. The Name Manager will show all the names.
| >
| > For example, each sheet can have a range named Print_Area. The range
| > doesn't have to be in the same cells on each sheet in order to use the
| > same name.
| >
| > If you spend some time exploring the workbook with the Name Manager, it
| > may give you a better understanding of how things are set up, then it
may
| > be easier to dissect the formulas.
| >
| > Dean wrote:
| >> Thanks, this looks really nice, though may be a bit over my head. I
just
| >> installed it. Do you also know any of the answers to my questions?
| >>
| >> Thanks again.
| >> Dean
| >>
| >>
| >> | >>
| >>>If you're working with names, you might like the Name Manager, a free
| >>>add-in that makes it easy to work with defined names. You can download
a
| >>>copy:
| >>>
| >>> http://www.bmsltd.ie/MVP/Default.htm
| >>>
| >>>under the heading for Jan Karel Pieterse
| >>>
| >>>
| >>>Dean wrote:
| >>>
| >>>>I have (sigh) inherited an EXCEL file that uses range names
extensively,
| >>>>a practice that, while valuable for some users doing sanity checks, is
| >>>>not that valuable when you are trying to carefully check everything,
at
| >>>>least as I see it. I notice a number of curious things.
| >>>>
| >>>>How do I get the equations to show the underlying cells rather than
| >>>>their range names?
| >>>>
| >>>>Does using range names have any effect on trace dependents and trace
| >>>>precedents? For example, when I go to one cell and hit trace
precedent,
| >>>>it shows me a precedent. When I select that precedent and go there,
then
| >>>>hit trace dependents, it says there are none! I notice that the
latter
| >>>>cell has a range name. Would that cause this not to show up?
| >>>>
| >>>>It seems that, for some range names, the same range name is used
| >>>>multiple times, i.e., only once per worksheet, but on multiple sheets.
| >>>>Is it true that EXCEL has no problems with the same name being used
| >>>>multiple times in a file? If so, does the range of cells always need
to
| >>>>be in the exact same locations on each sheet? I wanted to have a map
of
| >>>>all range names, but when I do the commands: insert, name, paste,
paste
| >>>>link, I'm not sure it knows which of the identical range name cells to
| >>>>map to - perhaps it just chooses the one on the nearest worksheet.
Does
| >>>>anyone know?
| >>>>
| >>>>Please answer ONLY what you know right away. I don't need every
answer
| >>>>right away.
| >>>>
| >>>>Thank you very much,
| >>>>Dean
| >>>
| >>>
| >>>--
| >>>Debra Dalgleish
| >>>Excel FAQ, Tips & Book List
| >>>http://www.contextures.com/tiptech.html
| >>>
| >>
| >>
| >>
| >
| >
| > --
| > Debra Dalgleish
| > Excel FAQ, Tips & Book List
| > http://www.contextures.com/tiptech.html
| >
|
|

 

[Dean]

I do not want to de-name the range names, just would like to see through
them more easily. It seems like what you suggested below might do the
trick, but it doesn't seem to be working for me.

For example, if cell A3 says = $a$1*joe

and joe is really cell A2, how can I have it show me the formulas as being
=$A$1*$A$2.

I also tried transition formula evaluation, but that didn't work either.
BTW, I assume you mean that I should simply check the unchecked check box,
right?

Thanks
Dean

 

[Dean]

Since no one has replied to this latest post, and it has gotten old, I will
reply to myself so it looks fresher! Please see the post just below.
Without invoking anyone else's add-ins, isn't there a way that EXCEL will
not show range names. I thought I remembered some routine way to do that
(see example below).

Dean