General Betting
There is currently 1 person viewing this thread.
Im rearranging a spreadsheet.
Is there a way to find out which cells rely on another cell?
What I'd like to do is click on a cell, and have all other cells that use it highlighted. An alternative would be a "move" command that allows me to cut/paste a cell to a new location without confusing formulae that relied on it.
Thanks for any help.
Is there a way to find out which cells rely on another cell?
What I'd like to do is click on a cell, and have all other cells that use it highlighted. An alternative would be a "move" command that allows me to cut/paste a cell to a new location without confusing formulae that relied on it.
Thanks for any help.
sort by:
Show
per page
Replies: 16
|
By:
If you double click on a cell with some formula in that relies on other cells it should highlight the other cells involved in that calculation
|
|
By:
Cheers Joe, that's backwards to what I need though.
Example off the top of my head In B2 I'd have the amount of time left in a soccer match I'd have several other cells that relied on this in their formulae (match odds, over under 2.5, etc) and would like to see which ones they were without clicking on every other cell. |
|
By:
If you use cut, instead of dragging the cell, once you then paste the cell elsewhere it should retain any of the original references
|
|
By:
Awesome, I'll test that out. Thanks.
|
|
By:
K, I see what your trying to get at , can't think of any simple command to do it. You might have to do some VB code to search the sheet for any reference to that cell reference then highlight or list them
|
|
By:
You can do what you wnated just using find Lori.
Control+F just make sure in the options you specify to look in formulas, you could then just step thru all cell using that reference by clicking find next |
|
By:
haha, excellent. Sometimes the obvious isn't obvious.
That will do just fine, thanks again :) |
|
By:
or use the formulas/auditing toolbar and Trace dependents.
(depends on version of excel). auditing toolbar might be an add-in in earlier excel versions |
|
By:
That's also awesome too billy.
Excuse my apparent hyperbole in this thread, I'm genuinely really happy to have got some decent advice and I've run out of different words to use :) |
|
By:
lori had to go for a smoke after this
|
|
By:
The trace dependents function thing is actually so amazing I may have to get Danny Morrison to describe it for me. All the methods in this thread helped me a lot , but the trace arrows are the nuts.
|
|
By:
maybe coney could write a rap about them :)
|
|
By:
Lori not sure if you have realized ~
Trace dependents when clicked once shows arrows to direct dependents when clicked more than once shows dependents on the dependents Click till it beeps and will show all dependencies. Same with precedents. cheers |
|
By:
Hadn't spotted that either CM, although it's not what I need for this one, I'm going to have good fun with that I can tell :) Cheers.
|
|
By:
Lori maybe this is what you want a simple macro. This one does Precedents to do
Dependents substitute Precedents in line 5 to Dependents. This, when run will put all cells referenced by selected cell in column 10 starting at row 1 ($J$1) Sub PrecedentCells() Dim I As Long Dim cell As Range I = 1 Selection.Precedents.Select For Each cell In Selection Cells(I, 10) = cell.Address I = I + 1 Next cell End Sub |
|
By:
Ok, this would be a good time for me to start using macros. I've never done it before but always known I would need to in the end so I'll look closer on Monday (when the sport dies down a bit) and go through it stage by stage with google.
Thanks again. |