Highlight Formula Reference On Another Sheet

Re: Formula Highlighting On Different Sheets

Double-click should do it.

ADDED BY ADMIN

It can't be done as far as I know.

Color Cell Formula Precedents

Here is some VBA code that will color the precedent cells of any single cell formula. Color Cell Formula Precedents

Re: Formula Highlighting On Different Sheets

When you are on a worksheet you can quickly view what cells are being used in a formula with out needing to double click the formula bar. Use these key shortcuts.

Find what cells are being used:
Click on the cell with the formula and press " Ctrl + [ "
-This will select all the cells that this formula is using.

Conversely, Find what cells are using that cell as a reference:
Click on the cell and press " Ctrl + ] "
-If you get a message that tells you "No cells were found" then you know that the cell you selected is not contributing to any formulas and thus can be edited safely.

Here’s the problem. Excel doesn't let these key commands work across worksheets. Just like excel won’t show cell references across worksheet as highlighted colored boxes.

The only thing that I know of that might be of some help to you is excel's "formula auditing" tool bar. Click View->tool bars->formula auditing. Use the "Trace Precedents" button. Its the second from the left on the toolbar. Click this button and you will now see arrows pointing from the cells that are being used to the cell where the formula is. A grey dotted line will show that there are cells in another work sheet. If you double click this grey line, the "go to" window will open. It will list the other cells being used. if you click on one of the cells in the list and then click OK, you will be taken to that cell and it will have been selected. Use this to find and edit the cells being used.

Hopefully you can find some use out of this.

Yet the real question is: Why are you looking to highlight these cells?

Maybe someone knows a code to actually apply a color code to a cells dependents.

Cheers,
Dave

Re: Formula Highlighting On Different Sheets

Lovejoy,

Use the forum rules to your advantage. Assume you know nothing. I know you know something, because it sounds like you are doing pretty well so far with formulas and such, BUT, try posting up a new forum post with just your problem. You can tell people what you have tried, but you really should just tell people what you are trying to do.

From your last post it sounds like you are trying to use the results in one cell as a reference in another...mixed with something else.

Make a new post and just ask everyone what you want to have as a final product with the data you have. Someone may give you a new idea on how to accomplish your goal. Trust in the forum. It worked for my question. It will work for yours.

Don't forget to finish your new post with a link to this post. And it might be helpful to reply to this post with a link to your new post.

Good luck, and remember. the more general the better. that way it will help others too!
Dave

Re: Formula Highlighting On Different Sheets

I'm stubborn and by George I want to help. So would it be possible for you to type out an example of what your doing?

Because I know very little about VB I have been forced to do a lot of things with formulas. I have gotten quite good at using only formulas to do different things. I have used formulas to reference formulas to make locations for other formulas to reference. Really... If its possible to do with formulas in excel I think I have done it.

SO! Type out a little example of what you are trying to do and I might just be able to give you a solution to do it all on one worksheet with out needing to make so many formulas.

<|:-)
Dave

Re: Formula Highlighting On Different Sheets

Well,

I totally understand where you coming from with the not being able to list an example. But if you think of a way you could but it in to a different format with out the actual data then someone might be able to suggest something.

Then again...if the nature of your formulas gives away chaos theory or something....then you might not be able to.

Good luck!
Dave

Re: Formula Highlighting On Different Sheets

It can't be done as far as I know.

Re: Highlight Formula Reference On Another Sheet

Without knowing "why" you want the cells highlighted in the 2nd book, would this be useful to you?

select your cell containing the formula and goto menu: Tools\Formula Auditing\Trace Precedents, now blue lines will connect to your cells in the active workbook and any references to your other book will have dashed black line with a small sheet icon. If you double click the dashed line it will bring a popup box with all of the links and you can choose one and it will goto the 2nd book and highlight that cell.

Re: Highlight Formula Reference On Another Sheet

Quote from Sicarii

... if you double-click the dashed line...

Quote from lovejoy

I already tried the method you proposed and it didnt work

You should try again -- click the line, not the icon.

Re: Highlight Formula Reference On Another Sheet

Put it this way, it works for everyone but you. So the common factor is ......

Re: Highlight Formula Reference On Another Sheet

No, I said what you wanted cannot be done. What Sicarii suggested CAN be done.