VBA using cell values to build a sum product formula.. maybe don’t need VBA??

[Powerplaya]

What’s up my power peeps,

I need help on building a sum product formula in excel using a combo of different cell values and hard coded text.

Example.

Changing the date in a6, I extract what year it is in a1, what month in a2, and I hard code the rest of the path name to find my sheet on my server to pull the sumprodct formula

Example.

=iferror(sumproduct(- -(‘c:\home\a1 & “hard coded filename”\a2 & a1 &”hard coded filename”\[a6&”.xlsx”]tab’!$b$6),””)

So my formula is looking into my server files for the combined hard coded and cell values for the path, and if the file isn’t made yet, I used the iferror to show a blank.

Does this need a VBA? I tried almost everything I can think of.

Thanks guys!

 

[BSALV]

and ? You check if a file exists on your server, it's not even open at this moment ?
you can use a "dir" command to check of your file exists, something like this, approx.

 Filename = Dir("c:\home\" & a1 & "hard coded filename\" & a2 & a1 & "hard coded filename\" & a6 & ".xlsx")  
   if  Filename <> "" then

caution : use the correct " not the ”