software

I am using the same formula on several different worksheets. Some work and
some return #N/A as a result. Any suggestions? Here is my formula:

=IF(ISERROR(VLOOKUP((CONCATENATE($C42,quot;INSQUOTE-AUTOquot;)),Pronto2!$1:$2500,8,FALSE)),quot;quot;,VLOOKUP((CON CATENATE($C42,quot;INSQUOTE-AUTOquot;)),Pronto2!$1:$1940,8,FALSE))

Ok, the thing I notice is that you use 2 different ranges in your vlookups.
in the one where you are checking for an error, the range is $1:$2500 and in
the one where you are actually doing the lookup it is $1:$1940 so if you are
getting an #N/A error that would indicate that the value is in the range
1941:2500 but not in the range 1:1940. Also, if I could I would like to
point out if you are only trying to get rid of #N/A errors, you should use
the ISNA function instead. ISERROR includes all errors including #N/A, such
that you could be potentially masking an error that you should be dealing
with. And, FWIW, ISERR includes all errors except #N/A.
--
Kevin Vaughnquot;Darrel Aquot; wrote:

gt; I am using the same formula on several different worksheets. Some work and
gt; some return #N/A as a result. Any suggestions? Here is my formula:
gt;
gt; =IF(ISERROR(VLOOKUP((CONCATENATE($C42,quot;INSQUOTE-AUTOquot;)),Pronto2!$1:$2500,8,FALSE)),quot;quot;,VLOOKUP((CON CATENATE($C42,quot;INSQUOTE-AUTOquot;)),Pronto2!$1:$1940,8,FALSE))

I just noticed, you are using entire rows for your lookup ranges. Isn't that
wasteful/doesn't it end up taking too long for calculations? I don't know,
because I just now tried it on a very small range and it worked. But your
ranges are fairly large.
--
Kevin Vaughnquot;Darrel Aquot; wrote:

gt; I am using the same formula on several different worksheets. Some work and
gt; some return #N/A as a result. Any suggestions? Here is my formula:
gt;
gt; =IF(ISERROR(VLOOKUP((CONCATENATE($C42,quot;INSQUOTE-AUTOquot;)),Pronto2!$1:$2500,8,FALSE)),quot;quot;,VLOOKUP((CON CATENATE($C42,quot;INSQUOTE-AUTOquot;)),Pronto2!$1:$1940,8,FALSE))

Good eye. It was the range. Is there a way to unlimit the top range so I
don't run into this as my records grow?

quot;Kevin Vaughnquot; wrote:

gt; Ok, the thing I notice is that you use 2 different ranges in your vlookups.
gt; in the one where you are checking for an error, the range is $1:$2500 and in
gt; the one where you are actually doing the lookup it is $1:$1940 so if you are
gt; getting an #N/A error that would indicate that the value is in the range
gt; 1941:2500 but not in the range 1:1940. Also, if I could I would like to
gt; point out if you are only trying to get rid of #N/A errors, you should use
gt; the ISNA function instead. ISERROR includes all errors including #N/A, such
gt; that you could be potentially masking an error that you should be dealing
gt; with. And, FWIW, ISERR includes all errors except #N/A.
gt; --
gt; Kevin Vaughn
gt;
gt;
gt; quot;Darrel Aquot; wrote:
gt;
gt; gt; I am using the same formula on several different worksheets. Some work and
gt; gt; some return #N/A as a result. Any suggestions? Here is my formula:
gt; gt;
gt; gt; =IF(ISERROR(VLOOKUP((CONCATENATE($C42,quot;INSQUOTE-AUTOquot;)),Pronto2!$1:$2500,8,FALSE)),quot;quot;,VLOOKUP((CON CATENATE($C42,quot;INSQUOTE-AUTOquot;)),Pronto2!$1:$1940,8,FALSE))

He is using the whole sheet actually and beyond that unless he is using
excel 12, he could change that to
$1:$256 because that is what it is using excel 97 - 2003, if you type that
in the name box you'll see what I mean. If all the extra fluff is empty I
don't think it will matter much speed wise but it's not a good spreadsheet
design in general.--

Regards,

Peo Sjoblom

nwexcelsolutions.com

quot;Kevin Vaughnquot; gt; wrote in message
...
gt;I just noticed, you are using entire rows for your lookup ranges. Isn't
gt;that
gt; wasteful/doesn't it end up taking too long for calculations? I don't
gt; know,
gt; because I just now tried it on a very small range and it worked. But your
gt; ranges are fairly large.
gt; --
gt; Kevin Vaughn
gt;
gt;
gt; quot;Darrel Aquot; wrote:
gt;
gt;gt; I am using the same formula on several different worksheets. Some work
gt;gt; and
gt;gt; some return #N/A as a result. Any suggestions? Here is my formula:
gt;gt;
gt;gt; =IF(ISERROR(VLOOKUP((CONCATENATE($C42,quot;INSQUOTE-AUTOquot;)),Pronto2!$1:$2500,8,FALSE)),quot;quot;,VLOOKUP((CON CATENATE($C42,quot;INSQUOTE-AUTOquot;)),Pronto2!$1:$1940,8,FALSE))
You could use a dynamic named range. Here are some examples that I have in
one of my spreadsheets:

JobTitle=OFFSET(Applicants!$P$3,,,COUNTA(Applicants!$P:$P)-1)
sort_area=OFFSET(Applicants!$A$2,,,COUNTA(Applicants!$A:$A) ,COUNTA(Applicants!$2:$2))
Store=OFFSET(Applicants!$L$3,,,COUNTA(Applicants!$L:$L)-1)
UpdDate=OFFSET(Applicants!$A$3,,,COUNTA(Applicants!$A:$A)-1)

That way, as the range grows, the named area that the named range refers to,
expands. Note, this would assume no interspersed blank cells.
--
Kevin Vaughnquot;Darrel Aquot; wrote:

gt; Good eye. It was the range. Is there a way to unlimit the top range so I
gt; don't run into this as my records grow?
gt;
gt; quot;Kevin Vaughnquot; wrote:
gt;
gt; gt; Ok, the thing I notice is that you use 2 different ranges in your vlookups.
gt; gt; in the one where you are checking for an error, the range is $1:$2500 and in
gt; gt; the one where you are actually doing the lookup it is $1:$1940 so if you are
gt; gt; getting an #N/A error that would indicate that the value is in the range
gt; gt; 1941:2500 but not in the range 1:1940. Also, if I could I would like to
gt; gt; point out if you are only trying to get rid of #N/A errors, you should use
gt; gt; the ISNA function instead. ISERROR includes all errors including #N/A, such
gt; gt; that you could be potentially masking an error that you should be dealing
gt; gt; with. And, FWIW, ISERR includes all errors except #N/A.
gt; gt; --
gt; gt; Kevin Vaughn
gt; gt;
gt; gt;
gt; gt; quot;Darrel Aquot; wrote:
gt; gt;
gt; gt; gt; I am using the same formula on several different worksheets. Some work and
gt; gt; gt; some return #N/A as a result. Any suggestions? Here is my formula:
gt; gt; gt;
gt; gt; gt; =IF(ISERROR(VLOOKUP((CONCATENATE($C42,quot;INSQUOTE-AUTOquot;)),Pronto2!$1:$2500,8,FALSE)),quot;quot;,VLOOKUP((CON CATENATE($C42,quot;INSQUOTE-AUTOquot;)),Pronto2!$1:$1940,8,FALSE))

I see what you are saying when I enter that into the name box. However, I
used a much smaller range when I was testing:
=VLOOKUP(H1,$4:$29, 6, FALSE)
and it appeared to be using entire rows from row 4 through row 29. Let me
check again (by hitting F2 and seeing what Excel quot;colorsquot; as the range being
used.
Yes, rows 4 - 29 columns A through IV is the highlighted range.
--
Kevin Vaughnquot;Peo Sjoblomquot; wrote:

gt; He is using the whole sheet actually and beyond that unless he is using
gt; excel 12, he could change that to
gt; $1:$256 because that is what it is using excel 97 - 2003, if you type that
gt; in the name box you'll see what I mean. If all the extra fluff is empty I
gt; don't think it will matter much speed wise but it's not a good spreadsheet
gt; design in general.
gt;
gt;
gt; --
gt;
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt; nwexcelsolutions.com
gt;
gt;
gt;
gt;
gt; quot;Kevin Vaughnquot; gt; wrote in message
gt; ...
gt; gt;I just noticed, you are using entire rows for your lookup ranges. Isn't
gt; gt;that
gt; gt; wasteful/doesn't it end up taking too long for calculations? I don't
gt; gt; know,
gt; gt; because I just now tried it on a very small range and it worked. But your
gt; gt; ranges are fairly large.
gt; gt; --
gt; gt; Kevin Vaughn
gt; gt;
gt; gt;
gt; gt; quot;Darrel Aquot; wrote:
gt; gt;
gt; gt;gt; I am using the same formula on several different worksheets. Some work
gt; gt;gt; and
gt; gt;gt; some return #N/A as a result. Any suggestions? Here is my formula:
gt; gt;gt;
gt; gt;gt; =IF(ISERROR(VLOOKUP((CONCATENATE($C42,quot;INSQUOTE-AUTOquot;)),Pronto2!$1:$2500,8,FALSE)),quot;quot;,VLOOKUP((CON CATENATE($C42,quot;INSQUOTE-AUTOquot;)),Pronto2!$1:$1940,8,FALSE))
gt;
gt;
gt;