Sorting Issues

[stantheman]

Hi there!

Just wondering if someone can point me in the right direction for this, as it's been racking my brain trying to figure this out.

Basically, I'm retrieving data from another table using the formula =VLOOKUP([@[Entry Number]], 'sheet3'!$B$2:$M$191,2,FALSE), which is fine working well. However, it's obviously going to give me 0's and N/A's, as the formula is referring to a COLUMN with record entries in a number auto-fill format. Basically, 1 - 300, the formula finds the cell number, then uses the data on that row to prefill, but this is causing an issue when sorting.


Is there a way to hide this n/a or 0's when sorting completely? I hide the 0's in the advance tab, they still appear blank in the sort, as the cell still contains the 0. When sorting I can use the drop-down arrow to turn OFF 'n/'a and '0', problem is, new data isn't auto-selected and won't appear in the table sorted. Any help will be appreciated!

 

[MarqyMarq]

Try the =ISNA function or the =IFERROR function. They work for me when I apply them correctly to any given situation.

Should look something like this:

=IF(ISNA(VLOOKUP([@[Entry Number]], 'sheet3'!$B$2:$M$191,2,FALSE),"", VLOOKUP([@[Entry Number]], 'sheet3'!$B$2:$M$191,2,FALSE))

Good luck!