Fastest ways to trace formulas (especially when cells link to other worksheets)

Hello, I am finding it challenging to trace my formulas when they link
to other spreadsheets. It is a manual and tedious process, and I am
thinking that there has got to be a faster way than physically
searching for the cells to which the formulas are referring.

Does anyone know of a fast way to jump to precedents and dependents of
formulas.  (I did notice the formula auditing toolbar, but it seems to
only work when you are trying to trace formulas in the SAME
worksheet).

Thanks for any suggestions.
0
2/6/2008 2:52:12 AM
excel 39879 articles. 2 followers. Follow

4 Replies
1458 Views

Similar Articles

[PageSpeed] 40

For tracing across sheets, if you were to double-click on the dotted arrow 
that points to/from the little spreadsheet icon, that'll bring up a "Go To" 
dialog to navigate quickly to either the precedent(s) / dependent(s)
-- 
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
--- 
"Dave K" <fred.sheriff@gmail.com> wrote in message 
news:a13f9c17-34bd-4be1-8886-258d3dea4811@v67g2000hse.googlegroups.com...
> Hello, I am finding it challenging to trace my formulas when they link
> to other spreadsheets. It is a manual and tedious process, and I am
> thinking that there has got to be a faster way than physically
> searching for the cells to which the formulas are referring.
>
> Does anyone know of a fast way to jump to precedents and dependents of
> formulas.  (I did notice the formula auditing toolbar, but it seems to
> only work when you are trying to trace formulas in the SAME
> worksheet).
>
> Thanks for any suggestions. 


0
demechanik (4694)
2/6/2008 7:28:23 AM
Ctrl+[

Pressing control+[ while on a formula will jump to the first cell or range 
that the formula refers to. This can be particularly useful when the ranges 
are external references as it will then open the workbook and jump to the 
appropriate range.

"Dave K" wrote:

> Hello, I am finding it challenging to trace my formulas when they link
> to other spreadsheets. It is a manual and tedious process, and I am
> thinking that there has got to be a faster way than physically
> searching for the cells to which the formulas are referring.
> 
> Does anyone know of a fast way to jump to precedents and dependents of
> formulas.  (I did notice the formula auditing toolbar, but it seems to
> only work when you are trying to trace formulas in the SAME
> worksheet).
> 
> Thanks for any suggestions.
> 
0
lori1 (126)
2/6/2008 3:18:00 PM
On Feb 6, 1:28=A0am, "Max" <demecha...@yahoo.com> wrote:
> For tracing across sheets, if you were to double-click on the dotted arrow=

> that points to/from the little spreadsheet icon, that'll bring up a "Go To=
"
> dialog to navigate quickly to either the precedent(s) / dependent(s)
> --
> Max
> Singaporehttp://savefile.com/projects/236895
> xdemechanik
> ---"DaveK" <fred.sher...@gmail.com> wrote in message
>
> news:a13f9c17-34bd-4be1-8886-258d3dea4811@v67g2000hse.googlegroups.com...
>
>
>
> > Hello, I am finding it challenging to trace my formulas when they link
> > to other spreadsheets. It is a manual and tedious process, and I am
> > thinking that there has got to be a faster way than physically
> > searching for the cells to which the formulas are referring.
>
> > Does anyone know of a fast way to jump to precedents and dependents of
> > formulas. =A0(I did notice the formula auditing toolbar, but it seems to=

> > only work when you are trying to trace formulas in the SAME
> > worksheet).
>
> > Thanks for any suggestions.- Hide quoted text -
>
> - Show quoted text -

Thanks! Incredibly helpful.
0
js2k111 (48)
2/8/2008 3:54:05 AM
welcome, Mike.
-- 
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
--- 
"Mike C" wrote
Thanks! Incredibly helpful. 


0
demechanik (4694)
2/8/2008 4:06:54 AM
Reply:

Similar Artilces:

how to terminate a thread in afriendly way?
I have an application that uses many threads to do various things. Occasionally, one of the threads hangs when it is communicating with another computer that stops responding. Is there a way that I can terminate the hanging thread in such a way that it can clean up for itself before actually terminating? I've looked at exceptions, but I can't see how one thread (that is monitoring things)can throw an exception for another thread (that is hung). Brian Westcott wrote: > I have an application that uses many threads to do various things. > Occasionally, one of the threads ...

Links update.
I have a file containing external links to another site and was prepared in Excel 2000. I am opening the file on other PC's without access to the external links. The file when opened in Excel 97 and clicking No on prompt for update links, it shows previous values.When file is opened on PC with excel XP / 2000 without access to those links, it asks for update links. Clicking No displays #NAME?. But in Excel 2k / Excel XP, if "Automatic calculation" is disabled, then the file shows old values. How can I get the file to show previous valuse, WITH AUTOMATIC CALCULATION enab...

Merging and Linking Worksheets #2
I'm fairly new to Excel and I have a question that I don't know if this even works in Excel. Currently, I have five employees working on five different workbooks all day. They have about forty columns (each workbook has the same columns) and the records are continually added and removed. These workbooks NEED to stay separate files. What I want to do is create a new workbook that would have a sheet that combines the five sheets. It would be a separate file from the other five, but have one spreadsheet that is the combinate of all five and updates with their updates. Since a...

Formula Explanation
Hello I wanted to create a list with weekdays only and I have found this formula which works perfectly but I wondered if someone could explain it to me so that I can write one myself if I need to in the future. =IF(WEEKDAY(A2+1)=7,A2+3,IF(WEEKDAY(A2+1)=1,A2+4,A2+1)) Thank you The formula is not correct if you want the next Monday if the date in A2 is either Fri, Sat or Sunday, it works for Fridays and Sundays but not for Saturdays, if you put today's date in A2 it will return Wednesday the 15th Change it to =IF(WEEKDAY(A2+1)=7,A2+3,IF(WEEKDAY(A2+1)=1,A2+2,A2+1)) Weekday returns 1...

How do I graph a cell vs one that it changes for a range of valu.
One cell changes another cell as determined by a formula not explicitly contained in either cell. I want to range a cell from 0.5-1 in small increments and line-plot it vs the other. How do I express a range of values and the steps of increments? Hi Kentlm, > One cell changes another cell as determined by a formula not explicitly > contained in either cell. > I want to range a cell from 0.5-1 in small increments and line-plot it vs > the other. > How do I express a range of values and the steps of increments? It sounds like you want a Data Table. Look in Excel's Help ...

Why are the two codes below not getting the right formula for my worksheet event change formula?
Why are the two codes below not getting the right formula for my worksheet event change formula? The remaining ones do work, but these two are not getting the right results. Target.Offset(0, 6).FormulaR1C1 = "=RC[-2]&RC[-5]" Target.Offset(0, 7).FormulaR1C1 = "=RC[-2]&RC[-5]" I'm trying to get both column G & H to show a combined value of data in other columns. Column G should show result by combining data from column E & B, while column H should show result by combining data from column F & C. Column G is showing: =RC[-2]&RC[-5] ...

Copy Constant Formula
I have a formula that I want to spread down 1000 rows. =Change!$D$2 However, when I try to drag this formula into the other rows, the formula stays the same when I really want it to be: =Change!$D$3 =Change!$D$4 =Change!$D$5 =Change!$D$6 and so on. I also tried to manually enter the formula for the first 5 and load it that way, but it doesn't work. How can I fix this? I can not change this formula as it is linked to another worksheet the keeps getting re arranged. Thanks. To drag and fill the formula the $ sign before the row number needs to be removed. Using =C...

how to fflush the output of TRACE
I use TRACE to monitor the status of my program. When it finish one step, it traces one line such as "step ? error ??". To my astonishment, it will stop tracing after it has traced several hundreds of lines, but, if I step over the trace statement, it will output every lines In DOS, it may result from the size of the output buffer. I want to know why trace will stop working and how to solve it Thank you! I am not sure, whether FlushTimer(...) is what yuo require. See MSDN for Event Tracing and Performnace Monitoring "fengxvhui" <anonymous@discussions.microsoft.com>...

easy way to add all contacts from a DL out of Global address book to personal contact
hi, is there an easy way to add all contacts that are assigned to a distribution list within the global address book to my personal contacts? I could only add them contact per contact, but didn't find a way to e.g. add all 70 contacts from a DL automatically to my address book . any ideas? I don't want to add the DL, just the contacts of the DL.... thanks, stefan ...

The date a cell is modified
Hi all, I am hoping someone can help me with the following problem. I have an excel spreadsheet with 5 columns. Column A - Date Column B - Number Column C - Colour Column D - Size Column E - Date last modified If i was to enter the following information: 20/08/04 999999/88 red 550 ? I would like formulas or macros to do two things. The first is to count the number as two seperate numbers when it is seperated by the "/" sign. ie. 999999/88 means 999999/999988. If I was to use the count function as an example, the answer should be two for the amount of pieces. The second...

Reference Sheet name in a cell?
Hi, I have a cell (A1) that contains a formula which returns a sheet name depending on some other criteria. How do I reference that sheet name in a formula in another cell? eg: =Vlookup(b1,a1!$A:$M,2,false) Any help appreciated. thanks Nathan =VLOOKUP(B1,INDIRECT("'"&A1&"'!A:M"),2,FALSE) -- Regards, Peo Sjoblom "Nathan Sanders" <pas97ltd@xtra.co.nz> wrote in message news:8rbie.504$U4.78841@news.xtra.co.nz... > Hi, > > I have a cell (A1) that contains a formula which returns a sheet name > depending on some other...

Fastest way to do this?
I have 10000 logins and their respective passwords. Logins are in col A and passwords are in B. Logins contain serial no. like LGN00001 to LGN10000 What I want is to have the first 200 logins/ pwds on the first page like 1 to 50 in col A,b then 51 to 100 in C,D then 101 to 150 in E,F and 151 to 200 on G and H, The same thing is repeated for the rest of logins. 201 to 250 on A,B etc. any cool macro would help. thx Here's a very straight-forward macro Sub ReFormat() Dim iSource As Long Dim iTarget As Long iSource = 1 iTarget = 1 Do Cells(iSource, "A")...

proper way to close a socket?
I have a problem with CAsynsocket. I can connect to a remote socket. When I want to close the connection, I just delete the socket object (which closes the connection) and have no problems. But if I've started to connect to a remote socket that doesn't exist (or doesn't accept), and I delete the local socket object while waiting for the connection, I sometimes get an assert in CAsyncSocket::DoCallBack(). See below. Actually, it's not in the middle of an Accept call because I test with an address and port that I know doesn't exist. Any idea how I should be ...

Trouble with IF THEN ELSE formula
I know what I want the formula to do but cannot create the end result. Here's what I hope you can solve. The results are to show in cell N16. If cell J13 is >0 then I want N16 to check cell B16 for "x" (text), if B16 is marked with an "x" then N16 is to show $1000, but if B16 is blank then N16 is to show N/A. I'm not sure that I should even use the IF THEN ELSE formula in this instance. Any help will be appreciated! MPSr, Try this: =IF(AND(J13>0,B16="x"),1000,"NA") John "MPSr" <kpharris@nc.rr.com> wrote in message ne...

Extending Excel with more formulas
Subject: Extending Excel with more formulas Hi, Is there a way to extend Excel foruma base to include more formulas? Thanks, DDUP. Yes, by writing UDFs (User Defined Functions) either within VBA or an add-in. -- Andy Wiggins FCCA www.BygSoftware.com Excel, Access and VBA Consultancy - "death" <death@SupportX.com> wrote in message news:0A2C35B44976@123456789.spx.com... > Subject: Extending Excel with more formulas > > Hi, > > Is there a way to extend Excel foruma base to include more formulas? > > Thanks, > > DDUP. > > death wrote: &...

Converting worksheets to workbooks. Is there an easy way?
Workbook contains 150 worksheets. Is there an easy way to convert all worksheets to workbooks? (Break them out) Hi jim Try http://www.rondebruin.nl/copy6.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Jim" <Jim@discussions.microsoft.com> wrote in message news:88031DF5-B9C8-4298-B72F-A083E356FDBD@microsoft.com... > Workbook contains 150 worksheets. Is there an easy way to convert all > worksheets to workbooks? (Break them out) ...

Add the Content of a number of cells from multiple worksheet
I have a folder with a number of identical workbooks each one saved under a different department. Each workbook contains 13 worksheets identified as Wk1 to Wk13 representing 13 weeks In week 13 I wish to identify in cell P5 the total of the contents of cell M5 in each of the 13 worksheets. I have tried several formulations but I either get an error message or it brings up the folder with all the workbooks as if I have hit the open icon. Each of the M5 cells will only have a number in it. Any help would be appreciated-- with kind regards Mick If I understand you correctly, you can us...

Concatenation of a Formula
I have a bunch of formulas in Column D. All these formulas are incorrectly written, so I would like to remedy that by pre-pending/appending two text strings to them; this would create new, correct formulas in the process. For the sake of simplicity, let's assume that in Cell D1 I have this original formula: =A1*B1+C1 To this formula, I would like to pre-pend the string "=if(E1>5," and append the string ",A1*B1+(C1*4))", thus obtaining the new formula: =if(E1>5,A1*B1+C1,A1*B1+(C1*4)) Unfortunately, I cannot simply revise the formula in D1 and then c...

Is there a way to elliminate the "Save Before Quitting" Message?
I'm running many excel sheets on my website and everytime, it change excel forms, I don't want it to ask if the user is sure if they want t exit the form without saving. Thanks in advance ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com anyone ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com Do you want to save the save file or not save the file...

Formatting Cell Text #2
I have a spreadsheet that contains about 61 pages of information. Each row is 1 cell that we've made larger to accomidate an address being centered in the cell using spaces and word wrap. In Excel 2000 it was fine. We've upgraded to Excel 2003 and now the formatting is lost. The cell shows the text all over the place kind of like it is taking the spaces as text entry and without manually going into each cell and backspacing we can't get it to line up. Is there some option that we don't have ticked or did the upgrade change the formatting of the cell someway? In 20...

is there a way to do this... ?
A B 1 3 $1.12 2 8 $0.14 3 15 $1.71 4 etc etc 400 6 $3.83 I want to have one cell at the bottom be = A1*B1 + A2*B2 + A3*B3 etc ....... A400*B400 I know that I could add a column C to store the multiplication on each row, and then SUM column C, but is there a way to do this without adding a column C? one way: =SUMPRODUCT(A1:A400,B1:B400) In article <Q3DMd.7247$e11.724@twister.socal.rr.com>, "AFN" <newsDELETETHECAPSgroupaccount@DELETETHISyahoo.com> wrote: > A B > 1...

Quicker way to assign transactions to categories?
I imported the last 6 months of my credit card activity. Although some of the transactions were correctly categorized, most were listed under the Miscellaneous category. 1. Is it possible to setup money so that if I tell it , say, "Russian River Brewing Company" is Food:Dining Out, it will automatically reclassify *all* of those transactions as Food:Dining Out, or do I have to go through one-by-one and select the correct category for every Russian River Brewing Company transaction over the last 6 months? In other words, can I tell money *once* what category a transaction belongs to...

Sending copy worksheet to mailrecipient
Hey gyus :) I beleive there is a way to make a macro that will send a copy of my worksheet to a dedicated mailrecipient indicated in a cell in the worksheet, but I cant figure it out. Are there any compassonate Excel-guru who can lead me to this devine level of knowlegde? Most kindly regards Snoopy Check your other post. Snoopy wrote: > > Hey gyus :) > I beleive there is a way to make a macro that will send a copy of my > worksheet to a dedicated mailrecipient indicated in a cell in the > worksheet, but I cant figure it out. > Are there any compassonate Excel-guru who can...

multiple worksheet formula help
I have a 7 page workbook. Page 1 is the "master" sheet containing formulated data from the other 6 sheets in the workbook. Each of the 6 other sheets are set-up with the exact same rows and columns - they are simply assigned to a different sales representative. I have the representatives "complete" a ROW on their sheet and "copy/paste" that row to the "master" sheet which then formulates the data via "sumproduct" formulas into charts. My problem is that I have 6 representatives who can't even copy/paste without making errors! Wha...

Formula Syntax Help
I recently saw someone using the following formula on these boards: =SUMPRODUCT(--(Sheet1!E2:E65536="east"),--(Sheet1!I2:I65536 >38717)) I'm not familiar with this "--" and can't seem to figure out it's exact usage. Also, what's the difference between Sumproduct and Sum? Thanks much, Ikaabod -- Ikaabod ------------------------------------------------------------------------ Ikaabod's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=33371 View this thread: http://www.excelforum.com/showthread.php?threadid=534961 Bob Phil...