I am not sure how to use wildcards in a formula.
I edited the sheet "Norwood" and added a new sheet "Test". However,
formula gets fairly long after the IF(ISERROR(...),"",(...)) statement
in implemented. Personally I do not like it. It seems like you will
have quite a few sheets and each will have up to 500 rows of formula.
Time to complete calculation can be increased dramatically.
Good luck.
jarviscars Wrote:
Is it possible to substitute the string with wildcards???
I want to have a couple of sheets that search for stock # prefix...
here's the modified formula that i've come up with but it's not
returning the correct values...
Code:
--------------------
=IF(ISERROR(INDEX(Norwood!$A$1:$H$500,SUMPRODUCT(S MALL(ROW(Norwood!$3:$500)*(Norwood!$A$3:$A$500="D* "),COUNTA(Norwood!$A$3:$A$500)-COUNTIF(Norwood!$A$3:$A$500,"D*")+ROW()+500-COUNTA(Norwood!$A$3:$A$500)-2-2)),COLUMN())),"",(INDEX(Norwood!$A$1:$H$500,SUMPR ODUCT(SMALL(ROW(Norwood!$3:$500)*(Norwood!$A$3:$A$ 500="D*"),COUNTA(Norwood!$A$3:$A$500)-COUNTIF(Norwood!$A$3:$A$500,"D*")+ROW()+500-COUNTA(Norwood!$A$3:$A$500)-2-2)),COLUMN())))
--------------------
Essentially i've modified the formula to look on the sheet 'Norwood'
in 'Column A' for anything with containing 'D*'
Unfortunately it doesn't return all the results. It appears to count
the number of rows containing the D prefix but then just returns the
top X number of rows as counted.
Thanks
+-------------------------------------------------------------------+
|Filename: WebsiteStockPhotoReport_template2-R2.zip |
|Download:
http://www.excelforum.com/attachment.php?postid=3672 |
+-------------------------------------------------------------------+
--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile:
http://www.excelforum.com/member.php...fo&userid=7094
View this thread:
http://www.excelforum.com/showthread...hreadid=390438