Using INDIRECT function with wildcards

[Blen]

Hi All

Sorry if this has been covered in previous posts I was not sure how to phrase my query to be able to do an effective search.

I have a file which I want to do a SUMIF on each month but refer to a different data file where it will pick information up from. Each data file will be named with a Month in its title so I am referecing a field in the main file which has the month name to then go an get the neede dinformation from that respective file.

The SUMIF uses a wild card "L*" to pick up anything in a column with the letter L in it and sum the corresponding number.

What I appear to have problems with is combining the SUMIF with a "L*" and then use CONCATENATE to build a forumla to call the data.

Ideally I want to use INDIRECT to make EXCEL understand I want it to be a formula but the whole thing vcomes out with a #VALUE. If I copy and paste the resulting concatenate in to another cell the forumla works.

I suspect it is the wild card "L*" using quotes in combination with the INDIRECT function that is causing the problem.

The entire formula is as follows:-

=INDIRECT(CONCATENATE("-SUMIF('G:\Planning\[Travel Retail ",B8," 13 Work file.xlsx]Liquidation Model'!$B$6:$B$24,""L*"",'G:\Planning[Travel Retail ",B8," 13 Work file.xlsx]Liquidation Model'!$E$6:$E$12)"))

B8 is referencing the name "January" for instance.

Data in the work filw for columns B and E would typically be:-

L TYPE 1 10
L TYPE 2 20
L TYPE 3 30
M TYPE 1 10
M TYPE 2 20
M TYPE 3 30

So the above formula shoudl return in this instance a value of 60 (sum of everything beginning with a "L".

Any ideas or help would be appreciated.
Thanks

[Cheeky Charlie]

(Understandably) you've misdiagnosed the root cause of your problem.
You can't used indirect on a closed workbook.

You can:
Use morefunc.xll to give an indirect external function
Use Harlan Grove's PULL function (similar principle)
Change your formula (doesn't) look viable in this case.
Same problem here with advice:
http://www.ozgrid.com/forum/showthread.php?t=44975

[Blen]

Thanks for your reply Charlie but I I have both files open when I do this and the it comes up with #VALUE error instead of a #REF error.

The link you refer to seems to imply that the closed book return is a #REF.

I will look at some of your other recommendations to see if they may help.

[daddylonglegs]

You can't use INDIRECT round the whole formula - you need to use a regular SUMIF with two INDIRECT functions, one around each range like this:

=SUMIF(INDIRECT("'G:\Planning\[Travel Retail "&B8&" 13 Work file.xlsx]Liquidation Model'!$B$6:$B$24"),"L*",INDIRECT("'G:\Planning\[Travel Retail "&B8&" 13 Work file.xlsx]Liquidation Model'!$E$6:$E$24"))

Note: as Charlie says INDIRECT won't work with closed workbooks (and neither does SUMIF) so you need the source file to be open (in which case you probably don't need the whole path)

[Cheeky Charlie]

Thanks for your reply Charlie but I I have both files open when I do this and the it comes up with #VALUE error instead of a #REF error.

The link you refer to seems to imply that the closed book return is a #REF.

I will look at some of your other recommendations to see if they may help.

My mistake, I assumed you had open workbooks because of the full path filenames. Looks like DLL's found the real problem - which is sort of obvious now it's been pointed out...

[Blen]

You can't use INDIRECT round the whole formula - you need to use a regular SUMIF with two INDIRECT functions, one around each range like this:

=SUMIF(INDIRECT("'G:\Planning\[Travel Retail "&B8&" 13 Work file.xlsx]Liquidation Model'!$B$6:$B$24"),"L*",INDIRECT("'G:\Planning\[Travel Retail "&B8&" 13 Work file.xlsx]Liquidation Model'!$E$6:$E$24"))

Note: as Charlie says INDIRECT won't work with closed workbooks (and neither does SUMIF) so you need the source file to be open (in which case you probably don't need the whole path)

This works perfectly, thanks dll.