Quick view of Cell References??

Is there a way to get a quick view of all the cells that have a formula that 
reference a particular cell?

For example-
If Sheet2!B3 is being used in 10 different formulas in 10 different cells, 
can I see that without manually checking?
0
Mariano (11)
1/5/2006 4:09:02 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
403 Views

Similar Articles

[PageSpeed] 51

BTW, I thought Trace Dependents would work, however the arrows weren't to 
helpful, and it looks like it only traces on the current active worksheet, I 
need one to go across all worksheets.
A list would be a lot easier to use then arrows.

"Mariano" wrote:

> Is there a way to get a quick view of all the cells that have a formula that 
> reference a particular cell?
> 
> For example-
> If Sheet2!B3 is being used in 10 different formulas in 10 different cells, 
> can I see that without manually checking?
0
Mariano (11)
1/5/2006 4:25:03 PM
Click in the cell. Then try Tools>Formula Auditing>Trace Dependents.
-- 
ERR229


"Mariano" wrote:

> Is there a way to get a quick view of all the cells that have a formula that 
> reference a particular cell?
> 
> For example-
> If Sheet2!B3 is being used in 10 different formulas in 10 different cells, 
> can I see that without manually checking?
0
ERR229 (29)
1/5/2006 4:30:05 PM
Sorry, saw your second post after I replied with the Trace Dependents answer. 
Another possibility is to use Find All (in the Find dialog box). This isn't 
perfect, since you have to know whether to include absolute references or 
not, but it may get you started. 

Unfortunately, I don't know of any information function that would do what 
you want. 

There may be a VBA solution.

-- 
ERR229


"Mariano" wrote:

> Is there a way to get a quick view of all the cells that have a formula that 
> reference a particular cell?
> 
> For example-
> If Sheet2!B3 is being used in 10 different formulas in 10 different cells, 
> can I see that without manually checking?
0
ERR229 (29)
1/5/2006 4:45:02 PM
Thanks for the quick response....
Yea I haven't been able to find anything to do the trick yet either... 
suprising that this isn't more readily available.  It could be really useful.

"ERR229" wrote:

> Sorry, saw your second post after I replied with the Trace Dependents answer. 
> Another possibility is to use Find All (in the Find dialog box). This isn't 
> perfect, since you have to know whether to include absolute references or 
> not, but it may get you started. 
> 
> Unfortunately, I don't know of any information function that would do what 
> you want. 
> 
> There may be a VBA solution.
> 
> -- 
> ERR229
> 
> 
> "Mariano" wrote:
> 
> > Is there a way to get a quick view of all the cells that have a formula that 
> > reference a particular cell?
> > 
> > For example-
> > If Sheet2!B3 is being used in 10 different formulas in 10 different cells, 
> > can I see that without manually checking?
0
Mariano (11)
1/5/2006 4:51:03 PM
As you say, when you use "Trace Dependents", an arrow appears to represent 
dependencies on other sheets. Point at this arrow with the mouse (the arrow 
itself, not the icon at its head) and the cursor will change from the normal 
thich cross to a pointer. Double-click, and the "Go To" window will appear 
with a list of all these dependencies on other sheets.

Of course, you can select any one and Go To it from here if you wish.
Hope this helps.
Stephen

"Mariano" <Mariano@discussions.microsoft.com> wrote in message 
news:4EAB4F13-3631-4309-A261-3243E01208EE@microsoft.com...
> BTW, I thought Trace Dependents would work, however the arrows weren't to
> helpful, and it looks like it only traces on the current active worksheet, 
> I
> need one to go across all worksheets.
> A list would be a lot easier to use then arrows.
>
> "Mariano" wrote:
>
>> Is there a way to get a quick view of all the cells that have a formula 
>> that
>> reference a particular cell?
>>
>> For example-
>> If Sheet2!B3 is being used in 10 different formulas in 10 different 
>> cells,
>> can I see that without manually checking? 


0
Stephen
1/5/2006 5:02:38 PM
Reply:

Similar Artilces:

Merged cells.
Hi, Please can someone help me in regards to a merged cell difficulty. When I type a couple of lines of text into a merged cell, I find that a couple of things do not happen. First of all I notice that if I do not have wrap text on, the text does not extend into the next cell. Secondly, when I do have wrap text on, and type in several lines of text, the row height does not automatically grow to accomodate the lines of text. I am left to do this manually which is not as satisfactory. Can anyone comment on these differences between normal and merged cells. Regards, David Thomas. Melbourne. T...

Associated View Buttons
Hello Is there a supported way of removing buttons from the nav bar on an associated view ? We are using some custom forms for certain entities and would like to remove the Add, Add existing and delete functions from the associated view. We cannot amend the security roles as they do require these rights against the entity. Any help gratefully received. Thanks...Phil Hi Phil, I have a support call with Microsoft along these lines at present. With regards to hiding or removing the buttons, I have been told there is no supported way to do this. I am currently querying the functionality ...

Select a certain number of cells in a row
Hi, I'd like to have the macro to select row 5 to 10 in the active column. May I know what is the VB code to write? Regards, Valerie maybe... dim myRng as range dim myCol as long with activesheet mycol = activecell.Column set myrng = .range(.cells(5,mycol),.cells(10,mycol)) end with myrng.select ======= or with activesheet .cells(5,activecell.column).resize(6).select end with I'm not quite sure why you want to select that range. But for the most part, if you act directly on the range (and avoid .selects), you're code will work faster and be easier to modify. Dolph...

INBOX contents displayed in Calendar, Contact and Task views
About two days ago, icons for Calendar, Contact, and Task suddenly appeared in my email Favorite Folder window on the left side of the overall email view. Trying to remove these icons from the Favorites view and then running SCANPST apparently got rid of these icons. At the same time, the Inbox list (center panel) now displays when I switch to Calendar, Contacts, or Tasks, using the navigation icons on the very bottom of the left panel. The only way that I can display the Calendar, Contacts or Tasks is to select the Files navigation icon, select the right folder from the files list, and t...

Quick help with excel formula
Ok, i need a forumla. this is what I want, i have two columns like this Time Start: ............. Time Finish: ............ I fill out these two columns like this Time Start: ...17:42... Time Finish: ...18:25.. Now i have another Column that says Training Length: ........ I want this to work out the time between 17:42 and 18:25 Any one help pleas -- Message posted from http://www.ExcelForum.com Hi simply =A2-A1 and format as time If your training time can span midnight use the formula =A2-A1+(A2<A1) >-----Original Message----- >Ok, i need a forumla. > >this is what ...

Sort order views
Is it anyhow possible to change the standard field searchbox in views ? For standard search it's a bit annoying having to go in advanced search ! Jack No you cannot do this unless you want to attempt to dig into the sourcecode etc. So the answer is not in standard CRM. I have not heard of anyone making this change. -- John O'Donnell Microsoft CRM MVP http://www.microsoft.com/BusinessSolutions/Community/CRMFaqLanding.aspx "Jack Nielsen" <no_spam jack.nielsen@get2net.dk> wrote in message news:eo9$w0htEHA.1720@TK2MSFTNGP14.phx.gbl... > Is it anyhow possible to...

Help with copying dynamic column selected based on remote cell value and dynamic formula fill
Before posting my nightmare I would like to direct my thanks to all of you who posted answers before. Browsing trough the response I complete maybe 80% of my task in a very neat way. The remaining 20% of the task it seems that will require more than 80% of the time! There are 3 worksheets named S1, S2, and S3. I use a macro to first fill the no value NA() a range sayA1:C700 in S3! And second to open a text file and format data in it (get rid of empty rows via sorting since data in first column is ascending, etc). At the end of this macro there is another worksheet named P1.txt which has 12 co...

inventory unit of measure
We sell brakes in sets of four, but purchase them as an Each. When we use this system, the quantity on hand shows the amount of Each's on hand -- same with inventory counts. Because a Set of Four is set up in our system as 4 Eachs, If I set the UofM Schedule ID as Set of Four, the Base Unit of Measure (which shows on the vendor and purchasing cards) remains Each. Is there any way to get the "Set of Four" to be th Base Unit of Measure? The Base UOM is the UOM you stock in. So if you make 'Set of Four' the Base UOM, you'll have to stock it that way. You'll ...

Odd problem
Just opened a financial workbook that has several several columns. The data in one column is missing for the past five days. This column is updated daily and the data was current as of yesterday but now it is missing. I would understand, if only the last days information was missing (could have forgot to save), but not the last five days. Has anyone else seen this problem or have an idea what happened? Thanks Ted Just a guess. You have multiple workbooks of the same name--but in different folders. And you're opening the wrong one every once in awhile. Ted wrote: > > J...

Can I show email and calendar in one view?
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Email Client: Exchange I am trying to figure out a way to have a calendar view to the right of my inbox, either in place of the preview plane or next to it. <br><br>Is there any way to do this? Hi, you can only have the mini-calendar show up right next to the inbox. If it is not already visible, just click on the tiny triangle button in the lower left corner of the Entourage main window. If you want a calendar or a list showing your appointments (as you can in the Windows version of Ou...

is there a way of viewing all pages on Publisher at once?
It would be very helpful to be able to see all the pages i have created at the same time - and even better to be able to print them all on one page In print preview you can see most of the pages depending on how many, do a print screen... paste into Paint and print. Can't guarantee the results. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "HJD" <HJD@discussions.microsoft.com> wrote in message news:9534796A-69FA-48C9-8EBD-18E2F7337322@microsoft.com... > It would be very helpful to be able to see all the pag...

View actual "to field"
I receive my e-mail via exchange server. I have several aliases, all configured on the exchange server. Is there any field that could tell me which messages were addressed to which alias. The "To" field seems to convert my alias to my actual account so that all received message seem to be adressed to the same e-mail address, although in actuality they are not, as can be verified by looking at the internet headers where you can see the actual "to" address as entered by the sender. Is this information in any field so that incoming messages could be better sorted ? ...

Viewing a Return Document
I have a return doc I would like to view, but when I drill off the Vendor Payables Transactions it doesn't let me drill back - Where do I go to look at the Return?? Thanks ...

Connecting entities in one View
Hello, all! Is it possible to customize my own view to show documents with conditions, that uses other entities? I mean something like hard SQL-queries (with INNER JOIN may be...) I think Deployment Manager can help me to make mappings from one entity to another (using my own new fields) and then use that new fields in query. But can anybody say me clearly what is mapping in Deployment Manager?? ;) Views will only work with one CRM entity at a time. As always can create custom pages to act as views using asp.net and the SDK. The mappings in deployment manager allow you to tranfer data bet...

Clear cell when value is 0
Dear All, I saw an excel spreadsheet whereby when a user enters any number in any cell the numbers remain with no change. But when a user enters 0 (number zero) in any cell, that specific cell becomes empty (clear of all contents) Please advice how can i do the same in my own spreadsheets! Thanking you in advance, Carlos. Think zeros have been suppressed in the sheet .. This is done via: Click Tools > Options > View tab > Uncheck "Zero values" > OK -- Rgds Max xl 97 --- GMT+8, 1� 22' N 103� 45' E xdemechanik <at>yahoo<dot>com ---- "carlo...

Using a relative SHEET reference for source data in a chart
Hello All, I have a workbook with sheet 1 containing data, and sheet 2 containing charts based on the data in sheet 1. I want to be able to use these two sheets as templates for new sheets in the same workbook like so: sheet 1 data -------> sheet 2 chart sheet 3 data -------> sheet 4 chart sheet 5 data -------> sheet 6 chart etc. When I try to copy sheet 1 to sheet 3 all is well, but when I copy the charts in sheet 2 to sheet 4, those charts source data is still gathered from sheet 1. I need a method to replace the "sheet reference" in the source data to refer to ...

view opportunity by business unit
Hello, Can anyone tell me how I can create a view or Advanced Find in 3.0 to see an opportunity by Business Unit? If not, can a report be created to pull an Opportunity by Business Unit? Thanks. AMCK If you know the Business Unit you can create a query in Advanced find. 1. Open Advanced find. 2. Create a new query. 3. Select the Opportunities for the 'Look for' 4. Under the Fields, select the 'Owner' under Related Fields. This should add a new group. 5. Under the 'Owner' section select the 'Business Unit' field. 6. Select 'Equals' 7. Specify...

Copying certain column information quickly
As you know in Excel you could quickly copy the information you needed with a drag of the mouse. Because I need information copied repeatedly and easily for my access unfirmiliar co workers. Example JOB CUSTOMER QUOTE # A B1 110701 A(copied) A2 110701 (copied) A(copied) C4 110701 (copied) How do I do this quickly and efficiently for the computer challenged. Sorry, Access is a database, not a spreadsheet, and can't be used like one. You can write some code to select the rows that you want to ...

Delete cell and move to next
Hi all Can anyone help with a macro. It is supposed to check if the cell in column A is empty and if it is delete and move the contents of B into A on deletion. I would post my workings but am too ashamed to do so. Please help. Rob Rob, Not sure if you mean any cell in column A, and by move whether you want to shift everything over. You could use: Sub Macro1() On Error Resume Next Columns("A:A").SpecialCells(xlCellTypeBlanks).Delete Shift:=xlToLeft End Sub which would do all cells at once, and shift everything over by one cell whereever there is a blank. If it is just a few c...

Can I view an MS Publisher pub, in full screen view?
I want to preview an MS Publisher pub, as a full screen picture (like PowerPoint's Slideshow, in contrast with slide edit view)? Idon't have a color printer so print preview shows a greyed out picture. Should I install a dummy printer and go print preview, or what? Print Preview won't show you a full screen image. You've already seen what it looks like apparently. If you download a dummy driver, you could at least see it in color. -- JoAnn Paules MVP Microsoft [Publisher] "J.K. August" <J.K. August@discussions.microsoft.com> wrote in message news:...

Quick Question
I have been using Office 2007 and OneNote for some time now and have recently set up the 2010 Beta on a second system. In the 2007 version there was a way to repair office if things went flaqky. How do I do that in Office 2010? Michael On Tue, 8 Dec 2009 23:59:26 +0000 (UTC), Rev. Michael L. Burns <sdbpastor@charter.bet> wrote: > >I have been using Office 2007 and OneNote for some time now and have recently >set up the 2010 Beta on a second system. In the 2007 version there was a >way to repair office if things went flaqky. How do I do that in Office 2...

Seperate text into two cells
I have a large phone list excel worksheet with each person's first and last name in one cell seperated by a space. How can I quickly seperate the name into two cells (first name and last name) so I can import into a database? Otherwise, I need to cut and paste the entire list. Data>Text to Columns, choose "delimited" and check the space character as separator -- Kind Regards, Niek Otten Microsoft MVP - Excel "JeffK" <JeffK@discussions.microsoft.com> wrote in message news:C074D3E2-5EC6-4E25-970D-0F4FEAF5AB79@microsoft.com... >I have a large phon...

Is there a quick way to...
highlighting only the odd or even numbered rows? Thanks. Something like this should do: Sub banding() ActiveSheet.Range("A65536").Select Selection.End(xlUp).Select lastrow = ActiveCell.Row Range("A6:AK" & lastrow).Select Selection.Interior.ColorIndex = xlNone 'Change rownum to the the first row number where data occurs rownum = 6 For i = 1 To (lastrow - rownum) Step 2 Range("B" & rownum + i & ":AK" & rownum + i).Select With Selection.Interior .ColorIndex = 15 .Pattern = xlSolid End With ...

Viewing a Word (*.Doc) File in CHtmlView
Hi All, I have created a SDI Project using MFC AppWizard. In Step 5, I have selected Project Style: Windows Explorer. In Step 6, I have selected CHtmlView as Parent class of Application View class. In LeftView, I have made a Tree of MS Word (*.DOC) Files and when double click on a File this file is shown in RightPane. Now, problem is that EXE of my Project is running fine on my PC. But, on some other PCs it shows a Downloading Dialog Box before showing file in the Right Pane. Dialog Box asks user to Open file, Save file, and to Cancel processing. How I can hide this dialog box. Best Rega...

Global Default View
Is there a way to change the default view globaly so that the filter on all views is set to "all" instead of "active" Hi, there is no global way. You have to go to each entity and set it's default view to All. -- PLEASE do click on Yes or No button if this post was helpful or not for our feedback. uMar Khan Email for direct contact: imumar at gmail dot com "Nate" wrote: > Is there a way to change the default view globaly so that the filter on all > views is set to "all" instead of "active" ...