referencing variable help

Hi Guys,

So i have a bunch of formulas which calculate a linked workbook and im wanting to have a cell in the current workbook which you can enter the filename so that it is easily updated without going into the VBA.

=COUNTIF('C:\Users\webbth\Desktop\[South Pac Load Tracker - MAR14.xlsx]SPECIALS TEAM NZ'!$C$8:$C$1000,A4)

This is the formula for example in the cell, and i want to change [South Pac Load Tracker - MAR14.xlsx] to a cell in the workbook instead.

if the cell that contains the file name is static, then try
=COUNTIF("'C:\Users\webbth\Desktop\"&B4&"SPECIALS TEAM NZ'!$C$8:$C$1000",A4)
where B4 contains [South Pac Load Tracker - MAR14.xlsx]

Good luck,
Josh

Why is it when update my links they come up with #VALUE yet the filepath and formulas are correct .... and if i manually re-enter it with the workbook open they will stay its not until i try and update with the workbook closed that they do not calculate


Thanks izandol, stupid me used ' instead of " and used like this

dim filename as string

filename = sheets(sheet1).Range("a1").value

Activecell.Formula = "=COUNTIF('C:\Users\webbth\Desktop\[' & filename & ']SPECIALS TEAM NZ'!$C$8:$C$1000,A4)"