Referencing Sheet name via string variable in formula

  • Good Morning,

    My team and I track the amount of water hauled from one location to another, complete with water type, trucking company, volume hauled etc. Each ws is specific to the offload location. In the following code, I am trying to insert a SUMIFS formula into the appropriate cell that dynamically links to the correct table and column for a "Dashboard" master type worksheet. A separate Module contains code that creates a new ws and inserts a named table on new ws with the same sheet name passed from a userform. I am attempting to reference the string variable to designate which table to pull from and insert that value in front of the column name in the formula. The column names for the referenced sheet are standard and the user can simply change the date (referenced in R[-1],C[8] to see the different sums on the dashboard, which are then copied and pasted into a daily email. After much trial and error, I keep receiving a run-time error 1004. Any help with the following syntax or suggestions is greatly appreciated! ...or is my approach all wrong?

  • Re: Referencing Sheet name via string variable in formula

    If you query the derived string used for the formula before it is added to the worksheet (and I just used 'Sheet2' as a literal for the example), you get:

    =SUMIFS( "Sheet2""[Volume Hauled (bbls)]" & , "Sheet2""[Date]" & ,R[-1]C[8], & "Sheet2""[Water Type]" & ,"Production")

    That don't look right...

    Too many issues to know exactly what the formula is meant to be. Upload a sample workbook


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!