insert a variable into a formula

[gpeterman]

=COUNTIF('C:\Users\Public\Documents\ASAB-LSA\CDR Reports\ASAB 0 Call Report\[ASAB 0 Calls 04-21-14.xlsm]0 Calls'!$E:$E,A2)
Note the Countif formula above. It references another worksheet located elsewhere on my C Drive.
In the woksheet name is a date "04-21-14". My question is this, I want to make only the date portion a variable which retrieves it's information from a cell on the sheet where the formula resides. IE: Cell B2 of this sheet contains "04-21-14".

 

[SkipVought]

Hi,

In an empty cell concatenate
[tt]
="C:\Users\Public\Documents\ASAB-LSA\CDR Reports\ASAB 0 Call Report\[ASAB 0 Calls "&B2&".xlsm]0 Calls'!$E:$E,A2"
[/tt]

Then use the INDIRECT() function to convert that string to a reference. So if your cell that contains the concatenation were ZZ1, then
[tt]
=Countif(Indirect(ZZ1))
[/tt]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]