I have a source data sheet named "Import" where I paste data in from
Quickbooks. On another sheet in column A, I am searching the source data in
Column U (which is a special price column for items that are on sale this
qtr.) for any row that has a value greater than 0. If so, then it pulls in
the item number from the source data coumn C.

This is the formula in A1:
=IF((Import!U10),Import!C1+ROW()/10^10,"")
copied down to A4000

The above formula works great, but, in column B, I am trying to remove the
gaps or rows without a result from the above formula. In other words, if
there are 50 results in column A, then they will display in B1 to B50.

This is the formula in B1:
=IF(ROW()COUNT(A:A),"",INDEX(Import!C:C,MATCH(SMA LL(A:A,ROW()),A:A,0)))
copied down to B4000

The result that I am getting in B1 to B4000 is "#VALUE", and I don't know
why. This formula works when I search some of the other data. Can someone
help?

Thanks,
Chad

Hi,

Don't know exactly what your data looks like but the second formula you are
using will return a #VALUE error if any cells in the range A:A or C:C return
#VALUE.

The first formula will return a #VALUE error if C1 is not a number or a blank:

=IF((Import!U10),Import!C1+ROW()/10^10,"")

If there are formulas in column C of the Import sheet which return "" that
will also cause the 1st formula to return VALUE.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Chad F" wrote:

I have a source data sheet named "Import" where I paste data in from
Quickbooks. On another sheet in column A, I am searching the source data in
Column U (which is a special price column for items that are on sale this
qtr.) for any row that has a value greater than 0. If so, then it pulls in
the item number from the source data coumn C.

This is the formula in A1:
=IF((Import!U10),Import!C1+ROW()/10^10,"")
copied down to A4000

The above formula works great, but, in column B, I am trying to remove the
gaps or rows without a result from the above formula. In other words, if
there are 50 results in column A, then they will display in B1 to B50.

This is the formula in B1:
=IF(ROW()COUNT(A:A),"",INDEX(Import!C:C,MATCH(SMA LL(A:A,ROW()),A:A,0)))
copied down to B4000

The result that I am getting in B1 to B4000 is "#VALUE", and I don't know
why. This formula works when I search some of the other data. Can someone
help?

Thanks,
Chad

Shane,

Please look at the file at the file at the link below and tell me if you can
figure out why I am getting a "#VALUE" in column B of the "Specials"
worksheet.

http://freefilehosting.net/download/44ba5

Thanks,
Chad

"Shane Devenshire" wrote:

Hi,

Don't know exactly what your data looks like but the second formula you are
using will return a #VALUE error if any cells in the range A:A or C:C return
#VALUE.

The first formula will return a #VALUE error if C1 is not a number or a blank:

=IF((Import!U10),Import!C1+ROW()/10^10,"")

If there are formulas in column C of the Import sheet which return "" that
will also cause the 1st formula to return VALUE.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Chad F" wrote:

I have a source data sheet named "Import" where I paste data in from
Quickbooks. On another sheet in column A, I am searching the source data in
Column U (which is a special price column for items that are on sale this
qtr.) for any row that has a value greater than 0. If so, then it pulls in
the item number from the source data coumn C.

This is the formula in A1:
=IF((Import!U10),Import!C1+ROW()/10^10,"")
copied down to A4000

The above formula works great, but, in column B, I am trying to remove the
gaps or rows without a result from the above formula. In other words, if
there are 50 results in column A, then they will display in B1 to B50.

This is the formula in B1:
=IF(ROW()COUNT(A:A),"",INDEX(Import!C:C,MATCH(SMA LL(A:A,ROW()),A:A,0)))
copied down to B4000

The result that I am getting in B1 to B4000 is "#VALUE", and I don't know
why. This formula works when I search some of the other data. Can someone
help?

Thanks,
Chad

Chad,
Suggest that you apply autofilter on the upstream formulated criteria col
for quick diagnosis. Just scroll right down in the autofilter droplist, check
for error values such as #N/A, #VALUE, etc. These are the usual culprits
which break the formulas flow. And the underlying causes would usually lie
further upstream, in the source data's consistency. It may actually be mixed
text (alphas) / nums data while you had thought it's pure nums/text nums
right down the col (eg in Import's col C).

Instead of:
=IF((Import!U10),Import!C1+ROW()/10^10,"")

Try:
=IF(Import!U10,IF(ISNUMBER(Import!C1+0),Import!C1 +ROW()/10^10,""),"")

Above should give you better results
in the face of mixed source data in Import's col C
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---

Max,

Thanks! That did it, and it works great.

I am about done with this file that you have been helping me on. It is
pretty big now -- about 35MB. This is helping to shave hours off of
generating our monthly order guide that we send out to our customers. Thanks
for all of your help. I could not have done it without you.

Best Regards,
Chad

"Max" wrote:

Chad,
Suggest that you apply autofilter on the upstream formulated criteria col
for quick diagnosis. Just scroll right down in the autofilter droplist, check
for error values such as #N/A, #VALUE, etc. These are the usual culprits
which break the formulas flow. And the underlying causes would usually lie
further upstream, in the source data's consistency. It may actually be mixed
text (alphas) / nums data while you had thought it's pure nums/text nums
right down the col (eg in Import's col C).

Instead of:
=IF((Import!U10),Import!C1+ROW()/10^10,"")

Try:
=IF(Import!U10,IF(ISNUMBER(Import!C1+0),Import!C1 +ROW()/10^10,""),"")

Above should give you better results
in the face of mixed source data in Import's col C
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---

Max,

Type in "Vendor Lookup" in the search box, and see if you can help me with
that one.

Thanks,
Chad

"Max" wrote:

Chad,
Suggest that you apply autofilter on the upstream formulated criteria col
for quick diagnosis. Just scroll right down in the autofilter droplist, check
for error values such as #N/A, #VALUE, etc. These are the usual culprits
which break the formulas flow. And the underlying causes would usually lie
further upstream, in the source data's consistency. It may actually be mixed
text (alphas) / nums data while you had thought it's pure nums/text nums
right down the col (eg in Import's col C).

Instead of:
=IF((Import!U10),Import!C1+ROW()/10^10,"")

Try:
=IF(Import!U10,IF(ISNUMBER(Import!C1+0),Import!C1 +ROW()/10^10,""),"")

Above should give you better results
in the face of mixed source data in Import's col C
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---

Yes, I've responded in that thread.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---
"Chad F" wrote in message
...
Max,

Type in "Vendor Lookup" in the search box, and see if you can help me with
that one.

Thanks,
Chad

Delighted to hear, Chad.
You're welcome
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---
"Chad F" wrote in message
...
Max,

Thanks! That did it, and it works great.

I am about done with this file that you have been helping me on. It is
pretty big now -- about 35MB. This is helping to shave hours off of
generating our monthly order guide that we send out to our customers.
Thanks
for all of your help. I could not have done it without you.

Best Regards,
Chad