Excel 2003 function with variables

**braddach** · 08-09-2009, 07:53 AM

I’m in over my head !!

On drive “C” I have folder sales
In sales are yearly folders 2007, 2008, 2009
In the yearly folders are the monthly workbooks sales 01, sales 02
In the workbook the sheets are 1, 2, 3, for the day of the month
i.e. c:\sales\2009\sales 08.xls

In my sales recap workbook I wish at access daily item mean sales in the other
Workbooks based on the Current date. I think something like this

=”c”:\sales\YEAR(A1)’[sales MONTH(A1).xls]DAY(A1)’!$BL$33

**Andy Pope** · 08-09-2009, 08:24 AM

Use concatenate to build the string and indirect to do the referencing.
Note that Indirect will only work if the workbook is open

=INDIRECT( CONCATENATE("c:\sales\YEAR",A1,"’[sales ",MONTH(A1),".xls]",DAY(A1),"’!$BL$33"))

To have a live link you will need code to covert the text into a real formula.

**braddach** · 08-10-2009, 03:49 AM

Thanks Andy!

If the workbook needs to be open the ("c:\sales\YEAR",A1,"’[sales ",MONTH(A1),".xls]", is a moot point but the code =(CONCATENATE("[Logs 0",MONTH(A7),".xls]",DAY(A7),"’!$BM$33"))
Gives the perfect [sales 08.xls]9’!$BM$33 I’m looking for, now just a little help with converting the text into a real formula.

**braddach** · 08-10-2009, 07:47 AM

Must be an ez'er way to get yesterday's avg.'s in a recap I was just try'en to use the date -1 as a refeance.

**braddach** · 08-11-2009, 05:33 AM

I got it to work
Thatnk for getting me on the right track

=INDIRECT(CONCATENATE("'c:\SALES\",YEAR(A1),"\[SALES 0",MONTH(A1),".xls]",DAY(A1),"'!","$BM$33"))