Can formulas be ignored?
Find bottlenecks in your Excel workbooks
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Can formulas be ignored?

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Jefferson City, Missouri
    Posts
    382
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    How can I make my sorting macro ignore the hidden formulas?

    My macro sorts fine and includes everything I want but I have extra cells for adding new data that contain a formula. When my macro sorts it places all empty cells at the top of the list then the data. When I delete the hidden formulas the sort places the empty cells at the bottom of my list like I want. Is there any way to code my macro to treat all cells that contain only the formula(not the data) as empty? The only thing the formula is, is a reference to another sheet ie. ='Sheet6'!$A$6.

    Thanks,

    I appreciate the help from everyone at Mr. Excel.

    viper

  2. #2
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Viper


    Push F5 and use the Edit>Go to Special - Formulas to select your formula cells, then hide the rows. Excel will not sort hiiden rows.



  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Jefferson City, Missouri
    Posts
    382
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    test
    I appreciate the help from everyone at Mr. Excel.

    viper

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Jefferson City, Missouri
    Posts
    382
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Dave,
    Thanks, but I do want them included in my sort. They are part of a group. So can I go at it differently? Can I change my code to sort only those in Range("A6:C10,A12:C40") <> "", or will the sort still pick up the hidden formula?
    I appreciate the help from everyone at Mr. Excel.

    viper

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Louisville, KY
    Posts
    72
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    add a space like this:
    =" "&'Sheet6'!$A$6

    and the formula cells should drop to the bottom of the list

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Jefferson City, Missouri
    Posts
    382
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You the man!! Worked great. Is there anyway to prevent the empty cell from showing #value? I tried =IF(ISERROR(""&'Team Entry'!A5),"",""&'Team Entry'!A5) in each cell but that seemed to cancel out the space and put the empty cells on top again after sorting.

    But thanks for the information.


    I appreciate the help from everyone at Mr. Excel.

    viper

  7. #7
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    (ISERROR(""&'Team Entry'!A5),"",""&'Team Entry'!A5) has no spaces in it. Try:

    (ISERROR(" "&'Team Entry'!A5)," "," "&'Team Entry'!A5)

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Location
    Jefferson City, Missouri
    Posts
    382
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Nate,

    Still puts empty at top. I even did
    =" " & if(iserror(" " & 'sheet'!$A$5)," "," " & 'sheet'!$A$5) but still no effect.

    But thanks for all your help. I'll work on it somemore, I've got until 4/2/02 before I roll it out.


    I appreciate the help from everyone at Mr. Excel.

    viper

  9. #9
    Board Regular wilkisa's Avatar
    Join Date
    Apr 2002
    Location
    Decatur IL, USA
    Posts
    632
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Can formulas be ignored?

    I have the same conundrum. In cells in Column D, I have the following formula:

    =IF(ISERROR(VLOOKUP(C4,$B$43:$D$73,3,FALSE)),"",VLOOKUP(C4,$B$43:$D$73,3,FALSE))

    It works fine but when I sort the column, all the blanks sort to the top. So, I modified it to:

    =IF(ISERROR(VLOOKUP(C4,$B$43:$D$73,3,FALSE))," "&VLOOKUP(C4,$B$43:$D$73,3,FALSE),VLOOKUP(C4,$B$43:$D$73,3,FALSE))

    based on the above suggestions. I now get #N/A as a result when there is no value to pull in, which isn't a problem except that I don't want it to show as such. I went to the Page Layout tab, Page Setup, Sheets and asked that errors display as but the #N/A is still visible. I have the cells shaded using Conditional Formatting =Mod(row() so that alternating rows are white/gray. I have tried to modify the CF so that if the row has #N/A the text will go white/gray but I can't make it work either.

    Does anyone have another other suggestions?
    Thanks,
    Shirlene

  10. #10
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    15,706
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Can formulas be ignored?

      
    Perhaps your CF Criteria needs to be " #N/A" rather than "#N/A" - if we are prefixing a space to the result.

    Another idea (a bad one maybe) would be to use "ZZZ" as the value for "not found" results - so it drops to the bottom of a text-sorted list. You might then use a CF to conditionally format ZZZ as White to "blend in" with the background.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com