Problem:Data validation using if statement in two workbooks

Hi all,

I am making an invoice sheet for me and i am struck at 1 place. I need your help. I am using Excel 2007

I have 2 workbooks. First is InvoiceTemplate.xlsx ( Main Invoice file), second is partydata.xlsx.

In 1st workbook I have one sheet named Bill. I have a cell D7 in it which holds state name of the customer. In the same sheet at G40 I want a drop down list with Tax name depending on the state customer is from because of different state tax and local tax here.

In second workbook i.e. partydata.xlsx, I have 1 sheet viz. Datalist. Cells P2 to P6 holds different Tax names.

I hope you understood the structure.

Now , When Cell D7 cell in 1st workbook holds value "MH", Dropdown at G40 in same sheet should contain Taxnames from 2nd workbook cells p2 to p4.

if Cell D7 cell in 1st workbook holds value other then "MH", Dropdown at G40 in same sheet should contain Taxnames from 2nd workbook cells p3 to p6.

I have done this before but there was only 1 workbook but now there are 2 workbooks. So it's not working for me.

Below is what i used to have in 1 file only and was working nicely.
=IF(Bill!D7="MH",DataList!$E$2:$E$4,DataList!$E$3:$E$6) [Here E2 to E6 were used to hold Tax names.]
I put this formula in Data-Datavalidation-List-source


Now I tried

=IF(Bill!D7="MH",[Partydata.xlsx]DataList!$P$2:$P$4,[Partydata.xlsx]DataList!$P$3:$P$6)

But it says You can not reference different worksheets or workbooks for Data Validation criteria.

I tried named range also with P2 to P4 as Taxnm1 and P3 to P6 as Taxnm2 then used formula as
=IF(Bill!D7="MH",INDIRECT("[Partydata.xlsx]Taxnm1"),indirect("[Partydata.xlsx]Taxnm2"))

result is same error.

Please help asap. Thanks.

Regards,

Apoorva

Actually it is about populating a dropdown list with data from another file depending on Cell value.