Results 1 to 3 of 3
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    A looping formula! (Excel 2000 >)

    Hi all...finally back after a long absence!

    While away I discovered a possibly useful function that simulates a macro loop procedure. Granted the fact that it is not as flexible as VBA, and you can only effectively use it in conditional formatting or validation...it has its use!

    The formula is : =INDIRECT("RC",False) - This creates a reference to each cell in the selection, just like a loop. You would assign a value etc to the expression by typing =??? after the function.

    Nothing major...but my <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15> for the day!

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: A looping formula! (Excel 2000 >)

    I don't understand what you mean by looping here.
    Instead of using Formula Is =INDIRECT("RC";FALSE)=37 in conditional formatting, you might as well use Cell Value Is 37.
    Could you give an example of what you had in mind?

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: A looping formula! (Excel 2000 >)

    Yes, the Indirect function can be used as an argument for something else.

    What I originally used it for was to colour code all formula cells by nesting the indirect function into the Get.Cell function.
    Something like : =Get.Cell(48,Indirect("RC",False)) which will find all formulas in the selection and colour code it.
    Regards,
    Rudi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •