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

Powerplaya

New Member
Joined
Jan 9, 2022
Messages
1
Office Version
  1. 365
  2. 2021
  3. 2019
  4. 2016
Platform
  1. Windows
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!
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

BSALV

Well-known Member
Joined
Oct 31, 2010
Messages
1,450
Office Version
  1. 365
  2. 2013
  3. 2007
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.
VBA Code:
 Filename = Dir("c:\home\" & a1 & "hard coded filename\" & a2 & a1 & "hard coded filename\" & a6 & ".xlsx")  
   if  Filename <> "" then
caution : use the correct " not the
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,493
Messages
5,832,015
Members
430,104
Latest member
briannnnleong

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top