Trying to Create a Formula that Aggregates Data from a Dynamic Range into a Single Column

[homerunhitr]

I am pretty decent at excel, but have hit a roadblock in what I am trying to accomplish. This forum has been super helpful to me in the past and I thought I would post my issue here. Any help would be super appreciated! On the file linked below, I am looking to do the following:

My formula is currently: =SUMPRODUCT('TC 1'!P$27:P32,N(OFFSET('Dev Plan'!D$6:D11,ROWS('Dev Plan'!D$6:D11)-ROW('Dev Plan'!D$6:D11)+ROW('Dev Plan'!D$6)-1,0)))

I need this formula to calculate the way it currently works, but I need it to be dynamic and pull data from multiple columns, instead of just one. Additionally, I need the "TC 1" reference to be something along the lines of an indirect function instead of being hardcoded into the formula. Additional details below:

- Column D has the example formula that I need help to be more automated / flexible
- I would like to have column D on the Williston tab automatically pickup the project area (eg. Williston) in the "Dev Plan" tab and respective "TC" columns (row 4)
- Currently, this is manual, as I have to pick up each "TC" column individually
- Conceptually, I need the ability to reference an infinite amount of "TC" columns and have that data funnel through to the Williston tab
- I would prefer formulas, if doable (arrays?). I will have individuals who are basic Excel users using this model, and I will need them to be able to relatively easily be able to trace the information, so I'm not sure VBA would be the best approach.
- Ultimately, the input name for the TC cells needs to link to ultimately match the input in row 4 in the "TC Control" tab, so the actual name will need to be flexible
- The last column in each "Project Area" in the "Dev Plan" tab will be a sum of the columns to the left of it. Theoretically, I need the ability to add an infinite amount of "TC" columns which will roll-up into the "Asset" tabs
- Under the "Asset" tabs, I will need this formula to be flexible so that it pulls the respective headers under the "Project" tabs. Eg, right now I am pulling "Net Oil Prod" for all the "Project" tabs based on the input in "Dev Plan". I will need this to also carry over so that it pulls data for "Net Gas Prod", "Net NGL Prod", "LOE", "GTP", "Total Drilling Capex", "Total Completion Capex", and "Total Acq. and Other Capex"


File for Download

Gofile - Free Unlimited File Sharing and Storage

Gofile is a free, secure file sharing and storage platform. With unlimited bandwidth and storage, you can easily store and share files of any type without any limits. Our advanced features, such as CDN support and password protection, make Gofile the ideal choice for individuals and businesses...

gofile.io