software

The formula below was working when D188 was D14 because I had entries from D9
to D14, but when I copied down to row 188 where there is no data yet, it
created a quot;#N/Aquot; for result. I need to have those cell without data to be
included in the formula.
Can someone help with this problem?
Many thanks in advance.

Serge

=IF(OR(A32=quot;quot;,B32=quot;quot;),quot;quot;,SUMPRODUCT(('Bolt List'!$D$9:$D$188=A32)*('Bolt
List'!$J$9:$J$188=B32)*('Bolt List'!$E$9:$E$188=quot;SAE Gr.5 Min. Cad.
Platedquot;),'Bolt List'!$C$9:$C$188))

why not change your formula so there is no na by using the same if idea
shown.

--
Don Guillett
SalesAid Software

quot;Sergequot; gt; wrote in message
...
gt; The formula below was working when D188 was D14 because I had entries from
gt; D9
gt; to D14, but when I copied down to row 188 where there is no data yet, it
gt; created a quot;#N/Aquot; for result. I need to have those cell without data to be
gt; included in the formula.
gt; Can someone help with this problem?
gt; Many thanks in advance.
gt;
gt; Serge
gt;
gt; =IF(OR(A32=quot;quot;,B32=quot;quot;),quot;quot;,SUMPRODUCT(('Bolt List'!$D$9:$D$188=A32)*('Bolt
gt; List'!$J$9:$J$188=B32)*('Bolt List'!$E$9:$E$188=quot;SAE Gr.5 Min. Cad.
gt; Platedquot;),'Bolt List'!$C$9:$C$188))
Hello Don,
Could you show me an example please!
Serge

quot;Don Guillettquot; wrote:

gt; why not change your formula so there is no na by using the same if idea
gt; shown.
gt;
gt; --
gt; Don Guillett
gt; SalesAid Software
gt;
gt; quot;Sergequot; gt; wrote in message
gt; ...
gt; gt; The formula below was working when D188 was D14 because I had entries from
gt; gt; D9
gt; gt; to D14, but when I copied down to row 188 where there is no data yet, it
gt; gt; created a quot;#N/Aquot; for result. I need to have those cell without data to be
gt; gt; included in the formula.
gt; gt; Can someone help with this problem?
gt; gt; Many thanks in advance.
gt; gt;
gt; gt; Serge
gt; gt;
gt; gt; =IF(OR(A32=quot;quot;,B32=quot;quot;),quot;quot;,SUMPRODUCT(('Bolt List'!$D$9:$D$188=A32)*('Bolt
gt; gt; List'!$J$9:$J$188=B32)*('Bolt List'!$E$9:$E$188=quot;SAE Gr.5 Min. Cad.
gt; gt; Platedquot;),'Bolt List'!$C$9:$C$188))
gt;
gt;
gt;

The formula is fine. You must have a #N/A in the data between 15 and 188.
Find it and correct it.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Sergequot; gt; wrote in message
...
gt; The formula below was working when D188 was D14 because I had entries from
D9
gt; to D14, but when I copied down to row 188 where there is no data yet, it
gt; created a quot;#N/Aquot; for result. I need to have those cell without data to be
gt; included in the formula.
gt; Can someone help with this problem?
gt; Many thanks in advance.
gt;
gt; Serge
gt;
gt; =IF(OR(A32=quot;quot;,B32=quot;quot;),quot;quot;,SUMPRODUCT(('Bolt List'!$D$9:$D$188=A32)*('Bolt
gt; List'!$J$9:$J$188=B32)*('Bolt List'!$E$9:$E$188=quot;SAE Gr.5 Min. Cad.
gt; Platedquot;),'Bolt List'!$C$9:$C$188))
Hello Bob,
Yes I have in the quot;Bolt Listquot; sheet for all the rows that do not have
entries yet with this formula:
=VLOOKUP(I40, INDIRECT(VLOOKUP(D40, $R$1:$S$9,2)),2,TRUE)
Now maybe the VLOOKUP formula needs modification?
Can the this formula give me an empty cell if there is no entries in the
corresponding cells?
Thanks a million
Serge

quot;Bob Phillipsquot; wrote:

gt; The formula is fine. You must have a #N/A in the data between 15 and 188.
gt; Find it and correct it.
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;Sergequot; gt; wrote in message
gt; ...
gt; gt; The formula below was working when D188 was D14 because I had entries from
gt; D9
gt; gt; to D14, but when I copied down to row 188 where there is no data yet, it
gt; gt; created a quot;#N/Aquot; for result. I need to have those cell without data to be
gt; gt; included in the formula.
gt; gt; Can someone help with this problem?
gt; gt; Many thanks in advance.
gt; gt;
gt; gt; Serge
gt; gt;
gt; gt; =IF(OR(A32=quot;quot;,B32=quot;quot;),quot;quot;,SUMPRODUCT(('Bolt List'!$D$9:$D$188=A32)*('Bolt
gt; gt; List'!$J$9:$J$188=B32)*('Bolt List'!$E$9:$E$188=quot;SAE Gr.5 Min. Cad.
gt; gt; Platedquot;),'Bolt List'!$C$9:$C$188))
gt;
gt;
gt;

Yes, use

=IF(ISNA(VLOOKUP(I40, INDIRECT(VLOOKUP(D40,
$R$1:$S$9,2)),2,TRUE)),quot;quot;,VLOOKUP(I40, INDIRECT(VLOOKUP(D40,
$R$1:$S$9,2)),2,TRUE)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Sergequot; gt; wrote in message
...
gt; Hello Bob,
gt; Yes I have in the quot;Bolt Listquot; sheet for all the rows that do not have
gt; entries yet with this formula:
gt; =VLOOKUP(I40, INDIRECT(VLOOKUP(D40, $R$1:$S$9,2)),2,TRUE)
gt; Now maybe the VLOOKUP formula needs modification?
gt; Can the this formula give me an empty cell if there is no entries in the
gt; corresponding cells?
gt; Thanks a million
gt; Serge
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt; The formula is fine. You must have a #N/A in the data between 15 and
188.
gt; gt; Find it and correct it.
gt; gt;
gt; gt; --
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove nothere from email address if mailing direct)
gt; gt;
gt; gt; quot;Sergequot; gt; wrote in message
gt; gt; ...
gt; gt; gt; The formula below was working when D188 was D14 because I had entries
from
gt; gt; D9
gt; gt; gt; to D14, but when I copied down to row 188 where there is no data yet,
it
gt; gt; gt; created a quot;#N/Aquot; for result. I need to have those cell without data to
be
gt; gt; gt; included in the formula.
gt; gt; gt; Can someone help with this problem?
gt; gt; gt; Many thanks in advance.
gt; gt; gt;
gt; gt; gt; Serge
gt; gt; gt;
gt; gt; gt; =IF(OR(A32=quot;quot;,B32=quot;quot;),quot;quot;,SUMPRODUCT(('Bolt
List'!$D$9:$D$188=A32)*('Bolt
gt; gt; gt; List'!$J$9:$J$188=B32)*('Bolt List'!$E$9:$E$188=quot;SAE Gr.5 Min. Cad.
gt; gt; gt; Platedquot;),'Bolt List'!$C$9:$C$188))
gt; gt;
gt; gt;
gt; gt;
I have learned a lot.
Thank so much Bob and to everyone else that had an input in my request.

quot;Bob Phillipsquot; wrote:

gt; Yes, use
gt;
gt; =IF(ISNA(VLOOKUP(I40, INDIRECT(VLOOKUP(D40,
gt; $R$1:$S$9,2)),2,TRUE)),quot;quot;,VLOOKUP(I40, INDIRECT(VLOOKUP(D40,
gt; $R$1:$S$9,2)),2,TRUE)
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;Sergequot; gt; wrote in message
gt; ...
gt; gt; Hello Bob,
gt; gt; Yes I have in the quot;Bolt Listquot; sheet for all the rows that do not have
gt; gt; entries yet with this formula:
gt; gt; =VLOOKUP(I40, INDIRECT(VLOOKUP(D40, $R$1:$S$9,2)),2,TRUE)
gt; gt; Now maybe the VLOOKUP formula needs modification?
gt; gt; Can the this formula give me an empty cell if there is no entries in the
gt; gt; corresponding cells?
gt; gt; Thanks a million
gt; gt; Serge
gt; gt;
gt; gt; quot;Bob Phillipsquot; wrote:
gt; gt;
gt; gt; gt; The formula is fine. You must have a #N/A in the data between 15 and
gt; 188.
gt; gt; gt; Find it and correct it.
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; HTH
gt; gt; gt;
gt; gt; gt; Bob Phillips
gt; gt; gt;
gt; gt; gt; (remove nothere from email address if mailing direct)
gt; gt; gt;
gt; gt; gt; quot;Sergequot; gt; wrote in message
gt; gt; gt; ...
gt; gt; gt; gt; The formula below was working when D188 was D14 because I had entries
gt; from
gt; gt; gt; D9
gt; gt; gt; gt; to D14, but when I copied down to row 188 where there is no data yet,
gt; it
gt; gt; gt; gt; created a quot;#N/Aquot; for result. I need to have those cell without data to
gt; be
gt; gt; gt; gt; included in the formula.
gt; gt; gt; gt; Can someone help with this problem?
gt; gt; gt; gt; Many thanks in advance.
gt; gt; gt; gt;
gt; gt; gt; gt; Serge
gt; gt; gt; gt;
gt; gt; gt; gt; =IF(OR(A32=quot;quot;,B32=quot;quot;),quot;quot;,SUMPRODUCT(('Bolt
gt; List'!$D$9:$D$188=A32)*('Bolt
gt; gt; gt; gt; List'!$J$9:$J$188=B32)*('Bolt List'!$E$9:$E$188=quot;SAE Gr.5 Min. Cad.
gt; gt; gt; gt; Platedquot;),'Bolt List'!$C$9:$C$188))
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt;
gt;
gt;

Glad we could help Serge.

Regards

Bob

quot;Sergequot; gt; wrote in message
...
gt; I have learned a lot.
gt; Thank so much Bob and to everyone else that had an input in my request.
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt; Yes, use
gt; gt;
gt; gt; =IF(ISNA(VLOOKUP(I40, INDIRECT(VLOOKUP(D40,
gt; gt; $R$1:$S$9,2)),2,TRUE)),quot;quot;,VLOOKUP(I40, INDIRECT(VLOOKUP(D40,
gt; gt; $R$1:$S$9,2)),2,TRUE)
gt; gt;
gt; gt; --
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove nothere from email address if mailing direct)