Sorting Formulas with "Empty" Results

A

Access n00b

I have seen several similar questions posted and none of the resolutions seem
to be working for me. I hope Microsoft addresses this issue in the upcoming
Office release.

I have a worksheet that uses VLOOKUP to match a name to names in other
sheets in order to calcluate a year-to-date total. There are sheets for each
month in the workbook. The list on each sheet is 100 rows long, but not
necessarily completely full. This is necessary because there will be
multiple people using this sheet as a template and each will have a different
number of names to add to the list. So someone may have 5 names while
someone else has 50.

The list needs to be sortable both ascending and descending, but whether I
make the formula "IF(A1="","",....)" or "IF(A1="",0,...)" and hide zero
values, nothing seems to make a difference. When I set the formula to "" for
blank rows, the blank rows shop up first when sorted in descending order.
When I set the formul to 0 for blank rows, the blank rows show up first when
sorted in ascending order.

The gist of the formula is: If the name is blank, then insert a blank (or
zero), else lookup the name on each monthly sheet and return the sum for this
column.

Thanks in advance!

Here is the exact formula I am using:
=IF(A6="",0,SUM(VLOOKUP($A6,Jan_Table_NoHeaders,2,FALSE),VLOOKUP($A6,Feb_Table_NoHeaders,2,FALSE),VLOOKUP($A6,Mar_Table_NoHeaders,2,FALSE),VLOOKUP($A6,Apr_Table_NoHeaders,2,FALSE),VLOOKUP($A6,May_Table_NoHeaders,2,FALSE),VLOOKUP($A6,Jun_Table_NoHeaders,2,FALSE),VLOOKUP($A6,Jul_Table_NoHeaders,2,FALSE),VLOOKUP($A6,Aug_Table_NoHeaders,2,FALSE),VLOOKUP($A6,Sep_Table_NoHeaders,2,FALSE),VLOOKUP($A6,Oct_Table_NoHeaders,2,FALSE),VLOOKUP($A6,Nov_Table_NoHeaders,2,FALSE),VLOOKUP($A6,Dec_Table_NoHeaders,2,FALSE)))
 
A

Access n00b

OK, nevermind. I modified my sort script to select only rows with valid
data. Since the first column of each sheet contains a name that is not
entered by a formula, I used the following code for the macro so that it will
only select the rows with valid entries:

Sheets("YTD").Select
Range("A6").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Sort Key1:=Range("B6"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("B6").Select
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top