Formula to get from sheet by indirect()?

[cav~firez22]

ok, i have 2 spreadsheets, both are updated separately.

One sheet has new sheets added weekly, while the other is 1 sheet,

I need the workbook with 1 sheet to pull information from the one that has new sheets added weekly

Each new sheet is named accoring to week. (eg.. 10 01 06)
so the next sheet that is added will be 10 08 06
and then 10 15 06

and soforth.
Back on the single sheet work book, colums are labed accordingly.

L m
3 10/01/06 10/8/6 and sofort

4

5

i need L5 to equil the sum of c7 +E7 from the other workbook on the sheet that matches the date in L3


If this is too confusing, then let me know, however i feel it is because my brain is now smokeing.

ANY help would be more than greatly appreciated..

 

[cav~firez22]

='[Collections KPI.xls]10 01 06'!$E$10+'[Collections KPI.xls]10 01 06'!$H$10

That is the equation i have.

What i would love to have it to have the 10 01 06 be the indirect Value of L2

Codes would be helpful too...

and be able to just cary that on.. is that possible???

 

[jimrward]

try something like the following

=indirect("'[Collections KPI.xls]" & L2 & "'!$E$10")+ .... and repeat for second part, note it is not 3 pips in a row, it is double quote followed by single to start with and repeated in the middle and finish with double quote

 

[cav~firez22]

EXCELLENT. THANK YOU SOOOO MUCH!!!!!!

 

[cav~firez22]

OOPS..

one more, if Collections KPI.xls is located in a diferent location, say....
O:\Shared Services\Continuous Improvement\Manual Data\KPI's\Collections KPI.xls

How could that be implemented into?? Am havin a rough timne with tthis...