rows in a table?

  • Thread starter Thread starter Doug
  • Start date Start date
D

Doug

I keep a total of 1000 rows in a table full of vlookups linked to another
sheet of data.
The reason I keep 1000 rows is because the approx 800 rows of data
fluctuates and the rest of the rows in the table are filled with N/A's. These
N/A's become quite a problem and interrupt filtering data etc.

There are only two answers to the problem that I can think of, but don't
know how to do it.

1. I could figure out a way for the table to automatically change the number
of rows to fit the 800 +/- 50 rows of data from the other sheet it looks up.

2. I have tried this and even asked for help in earlier sessions and could
not figure it out, but to enter the formula in all cells to exclude the n/a
values. I also have an occasional problem with other error values. I tried
isna expression and iferrror, but can never get them to work.

Any suggestions? I would rather just not have the extra rows, but if the
table won't add more lines automatically when I import the query into the
other sheet that it is linked to, I will have missing data in my table.
 
Could you please post your formulas with ISNA or IFERROR().. IFERROR will
work only in 2007

=IF(ISNA(vlookupformula),"",vlookupformula)

=IFERROR(VLOOKUP(A1,B:D,2,0),"")
 
This formula only returns blank cells in the column:
=IFERROR((VLOOKUP($Q3,Import!$Q:$CA,4,FALSE)/(VLOOKUP($Q3,'Old
Import'!$Q:$CA,4,FALSE)))-1,"")
This is the original formula that works accept it allows error values such
as N/A .
=(VLOOKUP($Q3,Import!$Q:$CA,4,FALSE)/(VLOOKUP($Q3,'Old
Import'!$Q:$CA,4,FALSE)))-1

To answer your question, I am using 2007.

If you can give me a working formula that will omit these error values, so
that my table will not be influenced by them it sure will make my day.
 
This formula only returns blank cells in the column:
=IFERROR((VLOOKUP($Q3,Import!$Q:$CA,4,FALSE)/(VLOOKUP($Q3,'Old
Import'!$Q:$CA,4,FALSE)))-1,"")
This is the original formula that works accept it allows error values such
as N/A .
=(VLOOKUP($Q3,Import!$Q:$CA,4,FALSE)/(VLOOKUP($Q3,'Old
Import'!$Q:$CA,4,FALSE)))-1

To answer your question, I am using 2007.

If you can give me a working formula that will omit these error values, so
that my table will not be influenced by them it sure will make my day.
 
Back
Top