Dynamic Array Formula Help

Darian Klare

GOAL:

Create the most efficient formula that can drag across from Column "1" Row "2"and down as project budgets are activated (by form submission adding rows to the bottom of the sheet) and subsequently drawn down. Target Sheet has 60 project budget columns to the right of Project Kickoff, most blank, to allow additional future project budgets.

CELL TYPES Shown on Target:

Blank Cells - no data; project budget draw down hasn't started in that column

Grey/Yellow Fill Cells - VARIABLE Starting Project Budget Amounts need to be pulled from either A. locally from a similar project budget column from the left OR B. JOB # (not date) is referenced from the external sheet Source Project Data (Index/Match or Collect).

No Fill with $$$ Values - Budget draw down cells that fill down $EXPLICITLY$ starting from the cell above; the variable Starting Project Budget amount and split evenly into 6 amounts

$0.00 Cells - cells that remain after the budget has been fully spent as the projects to the right may still be actively drawing down their respective budget. It would be best if each column could end with the $0.00 (fully paid) and then the celles below the $0.00 would be blank again, similar to the cells above the Starting Project Budget

Current Formula to start project budget Column 1 Row 2:

=IF([Job #]@row = "", "", INDEX({Source Master Projects DATASET Budget}, MATCH($[Job #]@row , {Dynamically Add New Formula DATASET Job#}, 1)))

Current Formula to draw down started project budget Column 1 Row 3:

=$[1]2 - (($[1]$2 / 6))

Notice the need to hardcode the reference to the column 1 row 2 starting project budget (variable amount and sheet location). How can I include this variable value and drag across the sheet too?

Hope this makes sense and thanks in advance for the eyes and assistance! @Paul Newcome

SOURCE Master Projects DATASET

TARGET Dynamically Fill Formula Across Array

ccarriger

So this formula works if you put in a helper row that you make be the parent of all rows below it.

1. Make the first row your parent row by indenting all rows below it
2. Under the 1, 2, 3, etc. columns add the corresponding 1, 2, 3, etc. value
3. Put the below formula in each column—you can drag the formula within a single column, but dragging it to a new column breaks it. So just paste it in the 1, 2, 3, etc. columns in Row 2 and drag it down for each. It looks to match the parent row value with the job # and then grabs that budget amount and populates it.

=IF(PARENT() = Job@row , [Project Budget]@row , "")

I forgot—you're doing cross-sheet referencing. You should be able to use the above but just switching to your other sheet's budget range.