Can formulas be ignored?

[viper]

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,

[Dave Hawley]

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.

[viper]

test

[viper]

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?

[Don C]

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

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

[viper]

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.

[NateO]

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

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

[viper]

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.

[wilkisa]

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?

[xenou]

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.