Hello Zeunasc;
I am not good in to explain something. Here is an attempt. Hope this would
help you.
A2 = Start Date, here 10/20/2011
A4 = End Date, here 10/25/2011
Basically 10/20/2011 is 40836 (40836 days after 1/1/1900) &
10/25/2011 is 40841 (40841 days after 1/1/1900). If you change the cell
format to General you can see this number.
Defined a name “*CurrPeriod*” with Start & End date.
*=INDEX(Sheet1!$A:$A,Sheet1!$A$2):INDEX(Sheet1!$A:$A,Sheet1!$A$4)*
If you use INDEX before or after *:* it will give the actual cell reference,
rather than it’s value. So here it become,
INDEX(Sheet1!$A:$A,40836):INDEX(Sheet1!$A:$A,40841), which is
A40836:A40841
Note: you can also use INDIRECT here, =INDIRECT(Sheet1!$A$2&":"&Sheet1!$A$4)
INDIRECT is volatile function. This will slow up the calculation.
Take A8 formula as an example.
=LOOKUP("zzzzz",CHOOSE({1,2},A8,SUBSTITUTE(TRIM(A8),"remark
"&TEXT(LOOKUP(2,MATCH("*"&TEXT(ROW(CurrPeriod),"mm/dd/yyyy")&"*",A8,0),ROW(CurrPeriod)),"mm/dd/yyyy"),"")))
ROW(CurrPeriod) *|* This will give the array of the start & end dates,
here, ROW(A40836:A40841), which is {40836;40837;40838;40839;40840;40841}
TEXT(ROW(CurrPeriod),"mm/dd/yyyy") *|* Here TEXT function will convert
the all the values to mm/dd/yyyy format, which is
{"10/20/2011";"10/21/2011";"10/22/2011";"10/23/2011";"10/24/2011";"10/25/2011"}
"*"&TEXT(ROW(CurrPeriod),"mm/dd/yyyy")&"*" *|* * will help to look for
partial match. Array will become
{"*10/20/2011*";"*10/21/2011*";"*10/22/2011*";"*10/23/2011*";"*10/24/2011*";"*10/25/2011*"}
MATCH("*"&TEXT(ROW(CurrPeriod),"mm/dd/yyyy")&"*",A8,0) *|* If any of the
above array contains in A8, will give 1 otherwise #N/A. So here will become
{1;#N/A;#N/A;#N/A;#N/A;#N/A}. This means first value (10/20/2011) is
contains in A8 rest of them doesn’t.
LOOKUP is always looking for approximate value. Since we looking on a single
cell MATCH always will give 1 or #N/A.
LOOKUP(2,MATCH("*"&TEXT(ROW(CurrPeriod),"mm/dd/yyyy")&"*",A8,0),ROW(CurrPeriod))
| So this part will become
LOOKUP(2,{1;#N/A;#N/A;#N/A;#N/A;#N/A},{40836;40837;40838;40839;40840;40841})
| so here LOOKUP will give 40836 which is 10/20/2011
TEXT(LOOKUP(2,MATCH("*"&TEXT(ROW(CurrPeriod),"mm/dd/yyyy")&"*",A8,0),ROW(CurrPeriod)),"mm/dd/yyyy")
*|* So here this will become
TEXT(40836,”mm/dd/yyyy”) which is 10/20/2011
"remark
"&TEXT(LOOKUP(2,MATCH("*"&TEXT(ROW(CurrPeriod),"mm/dd/yyyy")&"*",A8,0),ROW(CurrPeriod)),"mm/dd/yyyy")
*|* will become here, remark 10/20/2011
TRIM(A8) *| *This will avoid all unnecessay spaces If A8 contains.
SUBSTITUTE(TRIM(A8),"remark
"&TEXT(LOOKUP(2,MATCH("*"&TEXT(ROW(CurrPeriod),"mm/dd/yyyy")&"*",A8,0),ROW(CurrPeriod)),"mm/dd/yyyy"),"")
This will become here, *SUBSTITUTE(TRIM(A8),"remark 10/20/2011","") *which
will replace remark 10/20/2011 to “”
CHOOSE({1,2},A8,SUBSTITUTE(TRIM(A8),"remark
"&TEXT(LOOKUP(2,MATCH("*"&TEXT(ROW(CurrPeriod),"mm/dd/yyyy")&"*",A8,0),ROW(CurrPeriod)),"mm/dd/yyyy"),""))
This will give array of A8 & Substituted value. If any of the number doesn’t
contain in A8 LOOKUP will give #N/A error, so will give a array of A8 & #N/A
=LOOKUP("zzzzz",CHOOSE({1,2},A8,SUBSTITUTE(TRIM(A8),"remark
"&TEXT(LOOKUP(2,MATCH("*"&TEXT(ROW(CurrPeriod),"mm/dd/yyyy")&"*",A8,0),ROW(CurrPeriod)),"mm/dd/yyyy"),"")))
This will look for the text which is in last. If #N/A is in last will show
the A8 value otherwise the substituted text.
If you are using XL2007 or later, you can use IFERROR; like
=IFERROR(SUBSTITUTE(TRIM(A8),"remark
"&TEXT(LOOKUP(2,MATCH("*"&TEXT(ROW(CurrPeriod),"mm/dd/yyyy")&"*",A8,0),ROW(CurrPeriod)),"mm/dd/yyyy"),""),"")
*LOOKUP function*;
http://office.microsoft.com/en-us/excel-help/lookup-HP005209163.aspx
*Volatile Functions*;
http://www.decisionmodels.com/calcsecretsi.htm
Hope this helps;
Haseeb.
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
