In Excel, how do I get an unknown number of values from cells using a list from another cell?

user_1688501 0
Viewed 146

I have a spreadsheet (Gantt chart) with dates in a column. Refer to the table below. The "Row" column is the row number in Excel, not a real column.

Row Depends on Row(s) (Col F) Start (Col G) End (Col H) Notes
9 7/24/21 7/26/21
10 9 7/27/21 7/30/21 Starts 1 day after row 9 ends.
11 7/25/21 7/27/21
12 9,11 7/28/21 7/29/21 Starts 1 day after MAX(row 9 end, row 11 end).

How do I automatically set cells "Start10" and "Start12" to read from cell "DependsOnRows" in their row to get the max of any numbers in the "DependsOnRows" column using a formula or other method?

Currently, I'm using this formula in cells "Start10" and "Start12", which includes a manually typed "max" function:

Start10: =WORKDAY(MAX(H9), 1, Holidays!A$2:A$99)

Start12: =WORKDAY(MAX(H9,H11), 1, Holidays!A$2:A$99)

I want to automate the reading of the row numbers inside the max function so they are read from the "DependsOnRows" column.

I can use any format in the "DependsOnRows" column. I can use braces, brackets, commas, spaces, whatever. The list just ideally needs to be in 1 cell, not multiple.

excel-2010 excel-formula excel
1 Answers

You can use the FILTERXML function to change the string of row numbers into a dynamic array. From there, you can INDEX the position of each row along with a fixed column number (in this case 8, or column H) to get test values for the MAX function.

Something like the below works for me:

=WORKDAY(MAX(
INDEX($A$1:$I$13,
FILTERXML("<t><s>" &SUBSTITUTE($F13, ",", "</s><s>")&"</s></t>", "//s"),8)),
1, Holidays!A$2:A$99)

enter image description here

user_109627890
Related