File referencing

[artfink]

Hello all,

I have a spreadsheet that uses both Vlookup and Index to pull information from other spreadsheets. I reference the file name in order to do so:

Example being

=INDEX('P:\Documents\Accounting\Financials\08-09\Claims Reports\08 August 2008\[SDAO Monthly Reports.xls]Report 1'!$I:$I,MATCH(+A7 & +B7,'P:\Documents\Accounting\Financials\08-09\Claims Reports\08 August 2008\[SDAO Monthly Reports.xls]Report 1'!$B$1:$B$200 & 'P:\Documents\Accounting\Financials\08-09\Claims Reports\08 August 2008\[SDAO Monthly Reports.xls]Report 1'!$C$1:$C$200,0))

My issue is the "08 August 2008" will need to be "09 September 2008" the following month for this spreadsheet. A way to do it is to simply find and replace as needed. But I thought maybe an easier way would be....

Assign C1 to be 'P:\Documents\Accounting\Financials\08-09\Claims Reports\
Assign D1 to be 08 August 2008
Asisgn E1 to be \[SDAO Monthly Reports.xls]Report 1'!$I:$I

Then each month I would simply change D1.

My new formula (that DOES NOT work) is
=INDEX(CONCATENATE(TEXT("'"&C1&D1&E1,0)),MATCH(+A6 & +B6,'P:\Documents\Accounting\Financials\08-09\Claims Reports\08 August 2008\[SDAO Monthly Reports.xls]Report 1'!$B$1:$B$200 & 'P:\Documents\Accounting\Financials\08-09\Claims Reports\08 August 2008\[SDAO Monthly Reports.xls]Report 1'!$C$1:$C$200,0))

or
=INDEX(CONCATENATE("'"&C1&D1&E1),MATCH(+A6 & +B6,'P:\Documents\Accounting\Financials\08-09\Claims Reports\08 August 2008\[SDAO Monthly Reports.xls]Report 1'!$B$1:$B$200 & 'P:\Documents\Accounting\Financials\08-09\Claims Reports\08 August 2008\[SDAO Monthly Reports.xls]Report 1'!$C$1:$C$200,0))

or

=INDEX(CONCATENATE(TEXT("'"&$C$1:$E$1,0)),MATCH(+A6 & +B6,'P:\Documents\Accounting\Financials\08-09\Claims Reports\08 August 2008\[SDAO Monthly Reports.xls]Report 1'!$B$1:$B$200 & 'P:\Documents\Accounting\Financials\08-09\Claims Reports\08 August 2008\[SDAO Monthly Reports.xls]Report 1'!$C$1:$C$200,0))

None of these work. Any suggestions?

[artfink]

I think I just busted my brain. But this appears to work....????

=INDEX(INDIRECT(TEXT("'"&C1&D1&E1&"'!$I:$I",0)),MATCH(+A6&+B6,(INDIRECT(TEXT("'"&C1&D1&E1&"'!$B$1:$B$200",0)))&(INDIRECT(TEXT("'"&C1&D1&E1&"'!$C$1:$C$200",0)))))

More testing tomorrow.

[artfink]

I modified the formula a little and then copied and pasted down my spreadsheet. It seems to work fine for the first 10 records, then pulls values of 0.00. Then when the variables refered to in the Match "Match(+A & +B6)" change to the next set of variables, it actually picks up the first set.


i.e. A6 & B6 read
1996 Automobile Liability

and A16 & B16 read
1996 Automobile Physical Damage

My formula pulls back the SAME data for both references which just isn't right.

Any thought? Looking further just now it is only catching the first cell of the Match....the A portion which is year. Why is it missing the B portion?

=INDEX(INDIRECT(TEXT("'"&$C$1&$D$1&$E$1&"'!$I:$I",0)),MATCH(+A6 & +B6,(INDIRECT(TEXT("'"&$C$1&$D$1&$E$1&"'!$B$1:$B$200",0)))&(INDIRECT(TEXT("'"&$C$1&$D$1&$E$1&"'!$C$1:$C$200",0)))))