displyaing the referenced cells ?

Hi everyone !
Working on an excel file full of references, it would be very useful
to be able to make the display to jump directly to the cells
referenced by the active cells I currently see.
So if my cell TU789 reference the cell JI890, I don't want to search
for JI 890, but would like excel to automatically show me this cell.
Do you know if it is possible ?
Is there a shortcut for that ?
Thanks,
JR.
0
3/1/2009 7:10:13 PM
excel 39879 articles. 2 followers. Follow

7 Replies
418 Views

Similar Articles

[PageSpeed] 37

It would be difficult to do that generically, as the cell could
contain a formula that references several other cells - which one
should be chosen in this case?

You might think about using a hyperlink formula (say in the top row of
your sheet with Freeze Panes set) and then in the cell next to it you
could enter the cell ref and then just click on the hyperlink to go
there.

Hope this helps.

Pete

On Mar 1, 7:10=A0pm, Romain Jouin <romain.jo...@gmail.com> wrote:
> Hi everyone !
> Working on an excel file full of references, it would be very useful
> to be able to make the display to jump directly to the cells
> referenced by the active cells I currently see.
> So if my cell TU789 reference the cell JI890, I don't want to search
> for JI 890, but would like excel to automatically show me this cell.
> Do you know if it is possible ?
> Is there a shortcut for that ?
> Thanks,
> JR.

0
pashurst (2576)
3/1/2009 7:42:01 PM
Romain Jouin or JR  -

Maybe the formula auditing tools will help:

In Excel 2003 and earlier, choose Tools | Formula Auditing | Trace 
Precedents.

In Excel 2007, choose Formulas | (Formula Auditing) Trace Precedents.

-  Mike

http://www.MikeMiddleton.com



"Romain Jouin" <romain.jouin@gmail.com> wrote in message 
news:6254422f-12ef-4069-88e3-14dde9962846@o36g2000yqh.googlegroups.com...
> Hi everyone !
> Working on an excel file full of references, it would be very useful
> to be able to make the display to jump directly to the cells
> referenced by the active cells I currently see.
> So if my cell TU789 reference the cell JI890, I don't want to search
> for JI 890, but would like excel to automatically show me this cell.
> Do you know if it is possible ?
> Is there a shortcut for that ?
> Thanks,
> JR. 


0
mike5208 (300)
3/1/2009 8:33:15 PM
On 1 mar, 22:42, Pete_UK <pashu...@auditel.net> wrote:
> It would be difficult to do that generically, as the cell could
> contain a formula that references several other cells - which one
> should be chosen in this case?
>
> You might think about using a hyperlink formula (say in the top row of
> your sheet with Freeze Panes set) and then in the cell next to it you
> could enter the cell ref and then just click on the hyperlink to go
> there.
>
> Hope this helps.
>
> Pete
>
> On Mar 1, 7:10=A0pm, Romain Jouin <romain.jo...@gmail.com> wrote:
>
> > Hi everyone !
> > Working on an excel file full of references, it would be very useful
> > to be able to make the display to jump directly to the cells
> > referenced by the active cells I currently see.
> > So if my cell TU789 reference the cell JI890, I don't want to search
> > for JI 890, but would like excel to automatically show me this cell.
> > Do you know if it is possible ?
> > Is there a shortcut for that ?
> > Thanks,
> > JR.

Pete,
Thanks for your answer,
But I am not sure I got all the idea : are you suggesting to make a
dynamical hyperlink cell ? I never saw that, which vb function would
allow that ?
If you may develop, it is interesting.
Thanks again.
BR,
JR.
0
3/1/2009 10:25:00 PM
Mike,
Thanks for your idea.
The formula trace tool is since a long time a mystery for me as I
never could activate the arrow displayed on the screen to jump to the
pointed cell. I do see the arrow, but it is not reacting to any click.
Is there a trick to make it work ?
Thanks again,
JR.



On 1 mar, 23:33, "Mike Middleton" <m...@mikemiddleton.com> wrote:
> Romain Jouin or JR =A0-
>
> Maybe the formula auditing tools will help:
>
> In Excel 2003 and earlier, choose Tools | Formula Auditing | Trace
> Precedents.
>
> In Excel 2007, choose Formulas | (Formula Auditing) Trace Precedents.
>
> - =A0Mike
>
> http://www.MikeMiddleton.com
>
> "Romain Jouin" <romain.jo...@gmail.com> wrote in message
>
> news:6254422f-12ef-4069-88e3-14dde9962846@o36g2000yqh.googlegroups.com...
>
> > Hi everyone !
> > Working on an excel file full of references, it would be very useful
> > to be able to make the display to jump directly to the cells
> > referenced by the active cells I currently see.
> > So if my cell TU789 reference the cell JI890, I don't want to search
> > for JI 890, but would like excel to automatically show me this cell.
> > Do you know if it is possible ?
> > Is there a shortcut for that ?
> > Thanks,
> > JR.

0
3/1/2009 10:27:52 PM
Hi,

With your cursor on the cell with the formula press Ctrl+[

When you are on the cell to which the formula refers you can get back by 
pressing Ctrl+]

there are the keyboard equivalents to Trace Prescedents and Dependents

-- 
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Romain Jouin" wrote:

> Mike,
> Thanks for your idea.
> The formula trace tool is since a long time a mystery for me as I
> never could activate the arrow displayed on the screen to jump to the
> pointed cell. I do see the arrow, but it is not reacting to any click.
> Is there a trick to make it work ?
> Thanks again,
> JR.
> 
> 
> 
> On 1 mar, 23:33, "Mike Middleton" <m...@mikemiddleton.com> wrote:
> > Romain Jouin or JR  -
> >
> > Maybe the formula auditing tools will help:
> >
> > In Excel 2003 and earlier, choose Tools | Formula Auditing | Trace
> > Precedents.
> >
> > In Excel 2007, choose Formulas | (Formula Auditing) Trace Precedents.
> >
> > -  Mike
> >
> > http://www.MikeMiddleton.com
> >
> > "Romain Jouin" <romain.jo...@gmail.com> wrote in message
> >
> > news:6254422f-12ef-4069-88e3-14dde9962846@o36g2000yqh.googlegroups.com...
> >
> > > Hi everyone !
> > > Working on an excel file full of references, it would be very useful
> > > to be able to make the display to jump directly to the cells
> > > referenced by the active cells I currently see.
> > > So if my cell TU789 reference the cell JI890, I don't want to search
> > > for JI 890, but would like excel to automatically show me this cell.
> > > Do you know if it is possible ?
> > > Is there a shortcut for that ?
> > > Thanks,
> > > JR.
> 
> 
0
3/1/2009 11:09:01 PM
On 2 mar, 02:09, Shane Devenshire <shanedevensh...@sbcglobal.net>
wrote:
> Hi,
>
> With your cursor on the cell with the formula press Ctrl+[
>
> When you are on the cell to which the formula refers you can get back by
> pressing Ctrl+]
>
> there are the keyboard equivalents to Trace Prescedents and Dependents
>
> --
> If this helps, please click the Yes button.
>
> Cheers,
> Shane Devenshire
>
> "Romain Jouin" wrote:
> > Mike,
> > Thanks for your idea.
> > The formula trace tool is since a long time a mystery for me as I
> > never could activate the arrow displayed on the screen to jump to the
> > pointed cell. I do see the arrow, but it is not reacting to any click.
> > Is there a trick to make it work ?
> > Thanks again,
> > JR.
>
> > On 1 mar, 23:33, "Mike Middleton" <m...@mikemiddleton.com> wrote:
> > > Romain Jouin or JR =A0-
>
> > > Maybe the formula auditing tools will help:
>
> > > In Excel 2003 and earlier, choose Tools | Formula Auditing | Trace
> > > Precedents.
>
> > > In Excel 2007, choose Formulas | (Formula Auditing) Trace Precedents.
>
> > > - =A0Mike
>
> > >http://www.MikeMiddleton.com
>
> > > "Romain Jouin" <romain.jo...@gmail.com> wrote in message
>
> > >news:6254422f-12ef-4069-88e3-14dde9962846@o36g2000yqh.googlegroups.com=
....
>
> > > > Hi everyone !
> > > > Working on an excel file full of references, it would be very usefu=
l
> > > > to be able to make the display to jump directly to the cells
> > > > referenced by the active cells I currently see.
> > > > So if my cell TU789 reference the cell JI890, I don't want to searc=
h
> > > > for JI 890, but would like excel to automatically show me this cell=
..
> > > > Do you know if it is possible ?
> > > > Is there a shortcut for that ?
> > > > Thanks,
> > > > JR.

strange : ctrl + [    work well, but ctrl + ]  write down the current
date on the cell :s
0
3/1/2009 11:29:38 PM
I found a way :
1)   ctrl + c               =>      to copy the content of the cell
2)   ctrl + g               =>      go to
3)   ctrl + v               =>       paste the ref

On 1 mar, 22:10, Romain Jouin <romain.jo...@gmail.com> wrote:
> Hi everyone !
> Working on an excel file full of references, it would be very useful
> to be able to make the display to jump directly to the cells
> referenced by the active cells I currently see.
> So if my cell TU789 reference the cell JI890, I don't want to search
> for JI 890, but would like excel to automatically show me this cell.
> Do you know if it is possible ?
> Is there a shortcut for that ?
> Thanks,
> JR.

0
3/1/2009 11:31:09 PM
Reply:

Similar Artilces:

Format Cells #14
I'm trying to figure out a way (if there's one) to enter a number into a blank cell and have it automatically multiply that cell by 25.4 (essentially converting a number from in. to mm.), keeping the new number in that same cell: enter 2 into A2 and it converts it to 50.8 and A2 now shows 50.8. Is this possible? Thanks. Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False With Target If .Column = 1 Then .Value = .Value * 2.54 End If End With ws_exit: Application.EnableEvents ...

Flipping order of contents of a cell based on a coma
I have the following information in a column: VIGNEAULT, NATALIE A RYAN, BRANDON D HALLEY, ANDREA S SCOTT, BRIAN L SEBHATU, LUWAM GOMES, MARIA L NABIGON, JOHN E COLE, CHARLOTTE A I need to flip the contents around so that VIGNEAULT, NATALIE A would become NATALIE A VIGNEAULT (without the coma). Any ideas would be greatly appreciated! Thanks, Chris. Try this: =MID(A1&" "&A1,FIND(",",A1)+2,LEN(A1)-1) Copy down as needed. Biff <chunt@vvsii.com> wrote in message news:1168975213.542721.138600@v45g2000cwv.googlegroups.com... >I have the following in...

Help : Cell Flags
In Excel 2003, some of the cells in my spreadsheet have a small triangle indicator in the top left corner. What does this indicator signify, and how can I remove it from my spreadsheet? Thanks for any help. Tools|Options|Error checking You can turn them all off -- or even use the warning to correct something. Tom_0127 wrote: > > In Excel 2003, some of the cells in my spreadsheet have a small triangle > indicator in the top left corner. What does this indicator signify, and how > can I remove it from my spreadsheet? Thanks for any help. -- Dave Peterson Thanks Dave....

how to color code a cell in excel if amount reaches a minus
what is the formula that is used if any to color code a cell or many cells that the amount reaches minus within a spreadsheet, such as total is -$5.69 in a roww and you may have others that may go in the minus How can you color code that cell and others if the amount reaches a minus? I would really appriciate any help because I am working on a expenditure report Thank you Pat Mineard The feature to do this is called Conditional Formatting Highlight the cells you wish to format, then do Format > ConditionalFormatting > CellValueIs > LessThan > 0 > Format > PatternsTab...

how to find data and list all of them in a cell.
I have one database table like this. ============================== Col_A Col_B Col_C name room cancel person1 building1 no person2 building1 yes person3 building2 no person4 building2 no person5 building1 no ============================== what I need is to list the name who don't cancel in building1 and fill in cell("D1") the result would like to be : person1 , person5 pls. help me to solve this problem. thank you so much The simplest way would be to select the ...

micro for multi colors in a cell
hi i am very new to this and need some help with setting up a micro I have a spreadsheet with a cell that i will have 4 options "A" "B" "C" "D" and i would like for each of them to have a different color when pu into that cell. Please i am new to this so you will have to walk me through the step . check <OMFuxWoQEHA.620@TK2MSFTNGP10.phx.gbl> check <K4BjMWoQEHA.3980@cpmsftngxa06.phx.gbl> check <#Ca3EWoQEHA.2784@TK2MSFTNGP10.phx.gbl> takethis <mn.cb247d45f8b39c24.12473@NOSPAMhotmail.com> Subject: Re: seguridad xp From:...

Can I add to a cell value without manual calculation?
I have a value in a cell eg 100 and want to add another value to it eg 10. Can this be done by entering the additional value and the sum completed automatically? Brian, You can do it like this. Put 10 in a cell and copy it. Select the cell with 100 in it and right click - paste special - select 'ADD' - OK Delete the 10 from the cell -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Brian" wrote: > I have a value in a cell eg ...

Replace null string with blank cell
I have a large table which was created via lookups from other tables. Many of the entries are (or should be) blank. After the table was created, I converted everything to values (Paste Special/Values) in order to save memory. However, the table now contains a null string or other unprintable character in each of the "blank" cells. Thus, if I use END-DN or END-UP to find the next value in the table, the cursor goes to the end of the table, as it sees something in each cell. If I edit a "Blank" cell, I see no characters, and if I select the formula bar & hit ENTER, ...

show first few letters of a column in another cell
I know how to use left to show the first character of a particular column in another column. What I need to do is show the first seven characters. For Instance: Column A has a value of "McDonald" I want Column B to show "McDonal" Thanks. =LEFT(A1,7) if A1 is your reference cell. -- HansM "Glenn" <nospam@yahoo.com> wrote in message news:O1l1Z7TOFHA.1040@TK2MSFTNGP12.phx.gbl... >I know how to use left to show the first character of a particular column >in > another column. > > What I need to do is show the first seven characters. >...

Remove Cell Auto-Format (General) on Paste / Replace
Good morning all, Just curious if this is able to be done. I have a series o spreadsheets, all of them do different things, but the common factor i that I paste claim numbers into them. The problem is, Excel seems t think our claim numbers are dates. (Example. 2146/04 or 04/262 (different formats for different sections). Now, I have formatted AL cells on these spreadsheets as text. (Ctrl-A, Format-Cells-Text However, when pasting, Excel overrides whatever you've got, and choose it's own way to paste data. The other problem is with Find-Replace or Find-Replace All. A lot o the time, ...

Office 2007 + formatting cells
I have a cell with 6 numbers in for example 098737. Excel deletes the zeros off from the cell, when i right click format cells and go to custom i change general to 000000 but it changes it to chinese? Can anyone help please? Neil, The quickest way to keep the formatting is to put an apostrophe in front of your number. Like this: '098737 When you hit enter, Excel formats the cell as text and keeps your leading zero. HTH. "Neil Holden" wrote: > I have a cell with 6 numbers in for example 098737. > > Excel deletes the zeros off from the cel...

in excel can I write = c2 as a formula such as =cell(2,3)
Sheet1 is a price list if a person enters 5 in cell a5 (quantity) I want that 5 to be place in the first blank line on sheet two. To do so I need to write that cell as a formula. PS I don't know visual basic and would prefer not to use it. Hi not really sure what you're trying to do. What else in on sheet 2? -- Regards Frank Kabel Frankfurt, Germany segray wrote: > Sheet1 is a price list > if a person enters 5 in cell a5 (quantity) I want that 5 to be place > in the first blank > line on sheet two. To do so I need to write that cell as a formula. > PS I don't...

Find date in row and move to cell
I will try and explain this as best as I can but please forgive me if I fail. I have a date range in row 2 starting in column e and runing through to cell IR2. I then have a list of tasks in column a staring in row 4. There is a command button linked to a macro that will insert a new task in the middle of the existing list of tasks and creates some subtotals etc but what I would like it also to do is remember the row the active cell is in, look up todays date in the range and move to that location ready for input. Any ideas please? Kind regards, Mav Try the below Sub ...

Select every Nth cell in column? Help please
3 columns A2:C100 contains data. In E2:E14, I want a formula or function that will get every 3rd cell from Column A, F2:F14 every 3rd cell from Col B, G2:G14 every 3rdcell from Col C E.g.: A B C D E F G Days Week Mon RDays RWeek RMon 1 2 3 7 8 9 4 5 6 4 5 6 7 8 9 9 8 7 10 110 12 3 3 3 1 2 3 4 5 6 7 8 9 12 11 10 9 8 7 6 5 4 3 2 1 3 3 3 I'm guessing Vlookup somehow, but I sure cant figure out how to do it. Appreciate any help you can provide. Basically, I'm just trying to get a smaller set of data to work with in Cols E:G versus using the data in Col A:C which currently i...

Preventing Duplicate Cells
We have a list of about 8600 different numbers which are our accounts. Each is 16 digits long. We have recently had the problem that when we copy over account lines, sometimes we create duplicate cells. We need to prevent this duplication. Is there any way in Excel to prevent the addition of cells that have the same content? Check out the 'Advanced Filter' option... DATA / FILTER / ADVANCED FILTER You have the ability to copy unique records only to a different location. HTH, -- Gary Brown gary.DeleteThis2SendMeAnEmail.Brown@kinneson.com "BenBlair" wrote: > We h...

Inserting Quotation Marks in all Cells
Hi! I have a co-worker who needs to insert quotation marks in the entire worksheet. It contains both numbers and text. The quotation marks need to be inserted around each individual cells data. Can someone help? Enter this small macro: Sub quoteit() Dim r As Range For Each r In Selection If IsEmpty(r.Value) Then Else r.Value = Chr(34) & r.Value & Chr(34) End If Next End Sub Select all or some portion of the worksheet and run the macro. It will put quote marks before and after the contents of all selected non-empty cells -- Gary's Student "confused" wrote: ...

why doesnt my formula cell update automatically ?
=SUM(G5:G11) if I change any cell in the range, the sum is not performed. I have to select the sum cell, F2 then Enter in order to get the sum to work Check and make sure that calculation is set to automatic. Tools>Options>Calculation Biff "Wozza" <Wozza@discussions.microsoft.com> wrote in message news:D54D1FA7-F8C3-4333-B243-34E516DE8A5D@microsoft.com... > =SUM(G5:G11) if I change any cell in the range, the sum is not performed. > I > have to select the sum cell, F2 then Enter in order to get the sum to work Thanks Biff...not sure how it got set to ma...

Extract numeric value from single cell #2
Thanks, thats grea -- Landyma ----------------------------------------------------------------------- Landyman's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=807 View this thread: http://www.excelforum.com/showthread.php?threadid=26148 ...

distributing each characters on a cell
how can i distribute the letter in a cell to another cells? example: A1=HELP then get each character B1=H C1=E D1=L E1=P Here's one way : B1=MID($A25,COLUMN()-1,1) and copy across. NickHK "jjuan" <jp4the_clan@yahoo.com> wrote in message news:eSXqv%23$9GHA.1220@TK2MSFTNGP05.phx.gbl... > how can i distribute the letter in a cell to another cells? > example: > > A1=HELP > > then get each character > > B1=H > C1=E > D1=L > E1=P > > thanks a lot!!!! "NickHK" <TungCheWah@Invalid.com> wrote in message news:u4GAgBA...

formatting cells #8
I am trying to formatt the cell to show a L##.## (eg K34.9) Have gone into format, cells, then custom or special. Don't know how to change the special type and can not figure out how to enter formula in custom. Can this actually be done? Hi if you just want to use a fixed character in front of your values you may enter the following in the custom format: "L"#.00 just type this within the inputbox for this category. Not sure what you mean with 'enter formula' -- Regards Frank Kabel Frankfurt, Germany tm wrote: > I am trying to formatt the cell to show a L##.##...

city, state, zip in same cell
I ha ve been given a large data base with city, state and zip in same cell. How can I seperate the city, state and zip without doing it manually? Good afternoon, u may want to try this. First insert a few columns right after the column that contains the city state, etc. info. Then click on the column letter to highlight the column that contains the city, state, etc. data u would like to split into cells, then goto DATA menu toward the top of your screen and click on TEXT TO COLUMNS. A box will pop up, make sure DELIMITED button is on and click NEXT, Then put a check in boxes by COMMA a...

VB6 - ADO
First I must to say Sorry, because my English is not the best. I have some values that I write into an Excel XP sheet from VB6 using ADODB connection. The problem is that in Excel XP sheet the numbers in the cells are formatted as text. (All the cells In Excel have a green error indicator in the upper left corner). To write the values in Excel sheet I use INSERT INTO � or AddNew and Update methods of the RecordSet. I would like to now how can I copy the values (or one column) of the MSFlexGrid to Clipboard. It is to Past to another application, like Excel Sheet or OriginPro 7. Thanks any He...

pasting in merged cells #2
I have created a form in excel 2003 where I have a column for names and there are six rows merged in each block that contains the name due to space for data in other parts of the form. I am wanting to paste a list of names in the name column containing the merged cells so that there will be one name per merged cell in the column. How can that be done? I think merged cells give more problems than they are worth. When a cell is merged there is only 1 cell. In your case I would not merge the cells but format them with a borde around them and cell colour to look like they are merged -- M...

Finding the cell in which an object lies
Hi again I'm trying to make a page break after a chart. But I can't do this i seems, the page break can only be inserted into a cell/row. Any idea how to determine the cell that the corner of an object is in I want to take my object, find what call it is above, and break base on that cell. Cheers -- F*S ----------------------------------------------------------------------- F*SH's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3635 View this thread: http://www.excelforum.com/showthread.php?threadid=56251 the chartobject has a .topleftcell and a .bo...

How do I change the color the cell becomes when doing a FIND
When I do a FIND or FIND / REPLACE, when Excel finds the value I am looking for, it highlights the cell, but the highlighted cell is in white, which is the standard sheet background. How can I change a setting so anytime I try to do a find, when the cell is found, it shows this cell in a totally different color say purple or red or yellow etc.. I find sometimes trying to identify which cell it has selected in a full screen of data can be tough. I jst want to be able to locate the selected cell easier. Thanks for your help Two known and common options to search ALL WB sheets....