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.
--
Thank you!

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),"")

--
Jacob


"Doug" wrote:

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.
--
Thank you!

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.

--
Thanks


"Jacob Skaria" wrote:

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),"")

--
Jacob


"Doug" wrote:

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.
--
Thank you!

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.

--
Thank you!


"Jacob Skaria" wrote:

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),"")

--
Jacob


"Doug" wrote:

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.
--
Thank you!