Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 10 of 10

Thread: Highlighting ARRAY formula cells

  1. #1
    Join Date
    19th January 2006
    Posts
    77

    Highlighting ARRAY formula cells

    Dear Forum,

    I thought I would be able to do this but its prooving a whole lot more difficult than I first thought. Please can you help?

    Essentially I have inherited a spreadsheet which is crammed full of complex array formulas.

    No problem in itself as I understand array formulas pretty well (or so I think!). My problem is that when i want to ammend some of these, i cannot easily see what range I need to highlight in order to edit them i.e. I obviously cant do a single cell as its part of an array. I hope I have made myself clear?

    So my question is this.

    When the cursor is sat in a cell that is part of an array formula, how can I easily find out ( ideally by the array formula range being highlighted in (say) red ) what cells are included in the array formula?

    Would conditional formating do the job?

    I would be grateful for any help given.

    Regards,

    Pete

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    19,809

    Re: Highlighting ARRAY formula cells

    Aaron Blood has a free Formula Auditing Tool. I avoud array formulas as much as possible, so i don't know if it works with them.
    Hope that Helps

    Roy

    New users should read the Forum Rules before posting

    For free Excel tools & articles visit my web site

    If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

    RoyUK's Web Site

    royUK's Database Form

    Where to paste code from the Forum

    About me.

  3. #3
    Join Date
    19th January 2006
    Posts
    77

    Re: Highlighting ARRAY formula cells

    Roy,

    Thanks - an excellent utility in any event but unfortunately it doesn't solve my query highlighting array formulas (not that I could see anyway).

    Thanks again,

    pete

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    19th January 2006
    Posts
    77

    Re: Highlighting ARRAY formula cells

    Hi All,

    I have been told two ways to do it - pretty simple really - either press "Ctrl /" or from the menu, "Edit | Goto | Special | current Array".

    Regards,

    Pete

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,798

    Re: Highlighting ARRAY formula cells

    F5 - Special - Current Array wont select all array formulas on a Worksheet unless they are all part of the same array the active cell is.
    Last edited by Dave Hawley; August 9th, 2006 at 17:25.

  6. #6
    Join Date
    10th February 2003
    Location
    Palm Beach, Florida
    Posts
    1,556

    Re: Highlighting ARRAY formula cells

    Hmmm... wonder how I managed to miss this one?

    VB:
    Sub Highlight_Arrays() 
        Dim cell As Range 
        For Each cell In Selection.SpecialCells(xlFormulas) 
            If cell.HasArray Then cell.Interior.ColorIndex = 6 'yellow
        Next cell 
    End Sub 
    
    
    In which case it would be perfectly acceptable to select the entire sheet and analyze all formulas.

    Ah well, better late than never...
    Last edited by Aaron Blood; November 17th, 2006 at 03:40.
    Sub All_Macros(Optional control As Variant)

  7. #7
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,798

    Re: Highlighting ARRAY formula cells

    This would be a nice one for Conditional Formatting with a Custom Function.

    VB:
    Function Has_Array(Formula_cell As Range) As Boolean 
        Has_Array = Formula_cell.HasArray 
    End Function 
    
    

  8. #8
    Join Date
    10th February 2003
    Location
    Palm Beach, Florida
    Posts
    1,556

    Re: Highlighting ARRAY formula cells

    Quote Originally Posted by Dave Hawley
    This would be a nice one for Conditional Formatting with a Custom Function.
    That's a neat idea...
    Last edited by Aaron Blood; November 17th, 2006 at 23:19.
    Sub All_Macros(Optional control As Variant)

  9. #9
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,678

    Re: Highlighting ARRAY formula cells

    Using Excel's Macro functions

    Define HasArray
    refers to : =GET.CELL(49,INDIRECT("rc",FALSE))

    In CF,

    Formula Is: =HasArray

    HTH

  10. #10
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,798

    Re: Highlighting ARRAY formula cells

    Cool, always more ways that one to skin a cat

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Use Cells For Array Formula Constant
    By Michael Avidan in forum Excel General
    Replies: 7
    Last Post: March 2nd, 2008, 01:40
  2. Array Formula With Text & Numeric Cells
    By wedge in forum Excel General
    Replies: 5
    Last Post: November 20th, 2007, 09:19
  3. Identifying Cells From The Results Of An Array Formula
    By davecurtis in forum Excel General
    Replies: 3
    Last Post: October 13th, 2006, 18:37
  4. Exclude cells from Array formula
    By J Windebank in forum Excel General
    Replies: 6
    Last Post: September 22nd, 2006, 15:27

Bookmarks

Posting Permissions

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