how to make data validation with excel formulas like =OFFSET(Sheet1,0,0,1,1) ?

Topics: Developer Forum, Project Management Forum, User Forum
Nov 23, 2012 at 4:04 AM

I know that PHPexcel could make data validation with the code  below:





but,what i want to do is to set a formula directly to a column or to a cell  like this:




because,i want a cascade effect like below:
 1.  There is a column named "country" with dropdown list "USA,UK,CAN,CHN,ITA".

 2.  There is a column named "state".  When you have selected "US" in column "country",the "state" column has a dropdown list like "Alabama,Alaska,Arizona,Arkansas,California......". 

  When you have selected "CAN" in column "country",the "state" column has a dropdown list   like   "British Columbia,Alberta,Saskatchewan,Manitoba,Ontario......". 

  When you have selected “CHN”,you will get a dropdown list like "Beijing,Shanghai.....".

So l need the column or the cell has a formula like"=OFFSET(sheet2!$A$3,,MATCH(Sheet1!A2,sheet2!$1:$1,)-1,HLOOKUP(Sheet1!A2,sheet2!$1:$2,2,))"

and put the data of countries and states in the sheet  named "Sheet2".

Nov 23, 2012 at 6:23 AM
Edited Nov 23, 2012 at 6:23 AM

Another question,can i make some names in the names manager just like  this:
 create a name "country"  with a value of "=OFFSET(sheet2!$A$3,,MATCH(Sheet1!A2,sheet2!$1:$1,)-1,HLOOKUP(Sheet1!A2,sheet2!$1:$2,2";

and  then set the data validation like 




I can use key   ctrl + F3  to show the names manager in the Excel fiels. 

Nov 26, 2012 at 6:41 AM

Is there anybody who has encountered questions like this?

Nov 26, 2012 at 9:44 AM

No :-) I don't play with this (with PHPExcel) but, according to the documentation, you can define workbook, worksheet names, see 4.6.39 in developer documentation, but as far i can see, it's just named range, not constant or formula.
Same source (function Reference), you can read HLOOKUP : Not yet implemented.
In Excel, you can't have validation with data on another sheet else using named range, PHPExcel, at best, must have same restrictions.
So, define names, use implemented functions (PHPExcel is'nt a blind writer), test yourself. Not working ? Show your code, perhaps a mistake... No ? Add an entry in the issue tracker, and why not, a contribution.