Posts by lovejoy

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

  • Return Nth Greatest/Smallest Value

    Hi!


    I want to ask if there is a way to find the next greatest maximum without a ton of auxiliary coding....Is there a function which does that in Excel? For example from the following list it would give 45:


    1
    34
    45
    26
    78
    29
    7


    Thanks! a still more awesome function would give 34 if you give it 2 as an input parameter.

  • Highlight Formula Reference On Another Sheet

    Re: Highlight Formula Reference On Another Sheet


    Quote from Ger Plante

    Remove the underscore (_) and put all code on one line -


    Code
    If .Address = rFormulaCell.Address Then GoTo PrecedentError


    Ger



    thanks!!! it works now!! but, strangely, it uses a1 cell as its starting point instead of the current active cell...do you think you can make it start from the current active cell instead of the a1? also - do you know if it is possible to revert format changes after the macro is run?[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]

    Quote from Cheeky Charlie

    I have to admire your tenacity. You should be in business, not research.



    If you are not referring to Ger - thanks! :) I am actually in business too....

  • Highlight Formula Reference On Another Sheet

    Re: Highlight Formula Reference On Another Sheet


    thanks for the code Dave but I get these two lines highlighted in red with the syntax error:


    Code
    If .Address = rFormulaCell.Address _
            Then GoTo PrecedentError


    Can you please check if this is correct?
    Also - does this code allow to bring the cell formatting back once the cells have been colored?
    Thanks!!!!!

  • Highlight Formula Reference On Another Sheet

    Re: Highlight Formula Reference On Another Sheet


    Quote from shg

    Attached.


    thank you for making this for me...as I see the cells in the second sheet are actually not highlighted,,,,even if the popup window shows their links....looks like there is no hope in this question.....((([hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]

    Quote from Cheeky Charlie

    IMHO the best you can do would be the Goto precedents/dependents which Hatman has alluded too (press Ctrl + G whilst on the cell in question and pick your poison).


    What you're asking for is more about the design of Excel itself than how to use it - we can't really fix that here!


    Perhaps some git with 2007 will wade in to tell you how fantastic that is for exactly what you need(?)



    yep..I was just thinking that this was not such a huge problem and an average addin or a smart method would quickly overcome it....

  • Highlight Formula Reference On Another Sheet

    Re: Highlight Formula Reference On Another Sheet


    thank you for your reply...i need this formula highlighting to visually "debug" or check the code as I programm the model in excel...unfortunately I already tried the method you proposed and it didnt work...(((

  • Highlight Formula Reference On Another Sheet

    Re: Formula Highlighting On Different Sheets


    i very much appreciate your stubbornness...)) as a matter of fact I am myself a formula-only man....no vba knowledge at all!!!))) which I do not regret at all at the time being....as for my purpose for so many columns...I can only slighty remove the veail - this is a scientific project for constructing a complex adaptive model of a natural phenomen...sorry cannot go any further from that!)) anyways....if you get an idea on my topic problem - please let me know!

  • Highlight Formula Reference On Another Sheet

    Re: Formula Highlighting On Different Sheets


    thanks again..but I do not think i can make this question more general :) I just need to see formula references highlighted on different sheets (while having two different sheets of the same workbook open at the same time side by side) as they are on a single sheet..looks like I have run into a insurmountable wall here....

  • Highlight Formula Reference On Another Sheet

    Re: Formula Highlighting On Different Sheets


    thanks a lot for such a detailed reply! I tried all the approaches you suggested and none of them works for me...((( in all cases Excel takes me to the cells that are referenced by the formula while I need them to be highlighted only. I ask this because I am building calculations on one sheet based on the calculations in the other. I am using two sheets because one of them is already full of columns with the formulas...)) Is there absolutely no way to get Excel highlight formulas on different sheets???? Maybe some pluigin does this??

  • Highlight Formula Reference On Another Sheet

    Re: Formula Highlighting On Different Sheets


    Quote from Bob Phillips

    Double-click should do it.



    thanks...but the trouble is double-clicking doesn't work :( to clarify - I am working with two different worksheets of the same workbook each in its own window...arranged vertically (side by side).