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

homerunhitr

New Member
Joined
Jan 6, 2017
Messages
1
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
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Watch MrExcel Video

Forum statistics

Threads
1,123,519
Messages
5,602,141
Members
414,505
Latest member
quoctrungvu99

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