Urgently need solution, too tricky for me

[colinheslop1984]

Sorry the first post was messed up...

I have used match function to compare cell A:A to cell E:E, if there's a match cell D displays either true or false. Where there is a match I want the name of that product from cell A to appear on another page.

So

A........ B.....C..........D.................E............F...... G
Product....Qty...£.......Match T/F.......Product....Qty.... £
Tee..........2.....30........T............... Sweat........4..... 100
Shirt........1.....15....... .F................Pants........0..... .0
Sweat......6....140...... .F................ Shirt........7..... 180

So the function I am looking for will search column D for a match (false) and return the product name from cell A so I can then capture and compare the data on another page in the format below

A......... .B..............C...........D......E
Product.....Sales TY....Sales LY.. +-%..+-£

 

[colinheslop1984]

Ive got no idea what Im looking for on the evaluate formula function. The value error has gone but why might the cell not show a result and just appear blank?

 

[AliGW]

Have you done what I suggested? Evaluated the formula? It suggests that this part of the formula is evaluating as TRUE:

IF(COUNTIF('Product Data Import'!$D$3:$D$500,"False")<('Product Data Import'!$E$3:E$500)

 

[colinheslop1984]

Yeh I've looked but not really understanding what I'm seeing. When I check error checking function it says the formula is referring to cells that are currently empty, but they're not (atleast not all of them, my data range is approx 200 rows long but my formula is capturing data of up to 500 rows, as this may change weekly)

 

[AliGW]

Sorry, but without seeing the file, it's not at all easy to advise. When you say the cells aren't empty, what do you mean? Have you checked them for invisible trailing or leading spaces that might cause a mismatch?

 

[AhoyNC]

Try:
Add the ROWS function after the less than symbol and remove the quotes from around the FALSE

[IF(COUNTIF('Product Data Import'!$D$3:$D$500,[COLOR=#ff0000]FALSE[/COLOR]) < [COLOR=#ff0000]ROWS('Product Data Import'!$E$10:$E10),""[/COLOR],INDEX('Product Data Import'!$A$3:$A$500,SMALL(IF('Product Data Import'!$D$3:$D$500=[COLOR=#ff0000]FALSE[/COLOR],ROW('Product Data Import'!$D$3:$D$500)-ROW('Product Data Import'!$D$3)+1),ROWS('Product Data Import'!$E$10:E10))))/CODE]


[B]Product Data Import[/B]

[TABLE]
 <colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 72px;"><col style="width: 72px;"><col style="width: 72px;"><col style="width: 72px;"><col style="width: 111px;"><col style="width: 72px;"><col style="width: 72px;"></colgroup><tbody>[TR="bgcolor: #CACACA"]
[TD]*[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]2[/TD]
[TD="bgcolor: #C0C0C0"]Product[/TD]
[TD="bgcolor: #C0C0C0, align: center"]Qty[/TD]
[TD="bgcolor: #C0C0C0, align: center"]£[/TD]
[TD="bgcolor: #C0C0C0"]Match T/F[/TD]
[TD="bgcolor: #C0C0C0, align: center"]Product name[/TD]
[TD="bgcolor: #C0C0C0, align: center"]Units[/TD]
[TD="bgcolor: #C0C0C0, align: center"]£[/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]3[/TD]
[TD]Tee[/TD]
[TD="align: center"]2[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]TRUE[/TD]
[TD="bgcolor: #FFFF00, align: center"]Shirt[/TD]
[TD]*[/TD]
[TD]*[/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]4[/TD]
[TD]Shirt[/TD]
[TD="align: center"]1[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]FALSE[/TD]
[TD="bgcolor: #FFFF00, align: center"]Sweat[/TD]
[TD]*[/TD]
[TD]*[/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]5[/TD]
[TD]Sweat[/TD]
[TD="align: center"]6[/TD]
[TD="align: right"]140[/TD]
[TD="align: right"]FALSE[/TD]
[TD="bgcolor: #FFFF00"]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE]
<tbody>[TR]
[TD][B]Spreadsheet Formulas[/B][/TD]
[/TR]
[TR]
[TD][TABLE]
<tbody>[TR="bgcolor: #CACACA"]
[TD]Cell[/TD]
[TD]Formula[/TD]
[/TR]
[TR]
[TD]E3[/TD]
[TD]{=IF(COUNTIF('Product Data Import'!$D$3:$D$500,FALSE) < <rows<span>('Product Data Import'!$E$10:$E10),"",INDEX('Product Data Import'!$A$3:$A$500,SMALL[COLOR=#0000FF](IF[COLOR=#FF0000]('Product Data Import'!$D$3:$D$500=FALSE,ROW[COLOR=#804000]('Product Data Import'!$D$3:$D$500)[/COLOR]-ROW[COLOR=#804000]('Product Data Import'!$D$3)[/COLOR]+1)[/COLOR],ROWS[COLOR=#FF0000]('Product Data Import'!$E$10:E10)[/COLOR])[/COLOR]))}</rows<span>[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][FONT=Arial][B]Formula Array:[/B][/FONT][FONT=Arial]
Produce enclosing [/FONT][FONT=Arial][B]{ }[/B][/FONT][FONT=Arial] by entering
formula with CTRL+SHIFT+ENTER![/FONT][/TD]
[/TR]
</tbody>[/TABLE]
 

[COLOR=#000000][FONT=Arial][B]Excel tables to the web >> [/B][/FONT][/COLOR][URL="http://www.mrexcel.com/forum/<a href="]Excel Jeanie Html[/URL]" target="_blank"> Excel Jeanie HTML 4