Index Collect Based on Another Cell

trevrobwhite

I have a drill down sheet in which I need to collect the plan values from the master.

Master

Drill

The Drill sheet contains SRC ID, Month & Year this can pinpoint a single value in the Master sheet.

If I create a range containing only the Jan column it works:

=INDEX(COLLECT({Master Range Jan Column}, {Master Range SRC ID}, [SRC ID]@row, {Master Range Plan Column}, "Plan"), 1)

However, this requires me to change the formula for each month, I tried creating a range which included all month columns in the range, at first hardcoding to look and index 10 for October but I couldn't get it to work, it wouldn't even work with Index 1:

=INDEX(COLLECT({{Master Range Months}}, {Master Range SRC ID}, [SRC ID]@row, {Master Range Plan Column}, "Plan"), 1,1)

Error is #Incorrect Argument Set

What am I doing wrong? Ideally, I would want to use a column formula and smartsheet workout the Month column automatically (the year is the easier one).

Thanks in advance.

trevrobwhite

This was the best I could come up with, if anyone could improve it that would be great.

 =IF(Month@row = "Jan",

    INDEX(COLLECT({MasterRange Jan Column}, {MasterRange SRC ID}, [SRC ID]@row, {MasterRange Plan Column}, "Plan", {MasterRange Year Column}, Year@row), 1),

        IF(Month@row = "Feb",

            INDEX(COLLECT({MasterRange Feb Column}, {MasterRange SRC ID}, [SRC ID]@row, {MasterRange Plan Column}, "Plan", {MasterRange Year Column}, Year@row), 1),

        IF(Month@row = "Mar",

            INDEX(COLLECT({MasterRange Mar Column}, {MasterRange SRC ID}, [SRC ID]@row, {MasterRange Plan Column}, "Plan", {MasterRange Year Column}, Year@row), 1),

        IF(Month@row = "Apr",

            INDEX(COLLECT({MasterRange Apr Column}, {MasterRange SRC ID}, [SRC ID]@row, {MasterRange Plan Column}, "Plan", {MasterRange Year Column}, Year@row), 1),

        IF(Month@row = "May",

            INDEX(COLLECT({MasterRange May Column}, {MasterRange SRC ID}, [SRC ID]@row, {MasterRange Plan Column}, "Plan", {MasterRange Year Column}, Year@row), 1),

        IF(Month@row = "Jun",

            INDEX(COLLECT({MasterRange Jun Column}, {MasterRange SRC ID}, [SRC ID]@row, {MasterRange Plan Column}, "Plan", {MasterRange Year Column}, Year@row), 1),

        IF(Month@row = "Jul",

            INDEX(COLLECT({MasterRange Jul Column}, {MasterRange SRC ID}, [SRC ID]@row, {MasterRange Plan Column}, "Plan", {MasterRange Year Column}, Year@row), 1),

        IF(Month@row = "Aug",

            INDEX(COLLECT({MasterRange Aug Column}, {MasterRange SRC ID}, [SRC ID]@row, {MasterRange Plan Column}, "Plan", {MasterRange Year Column}, Year@row), 1),

        IF(Month@row = "Sep",

            INDEX(COLLECT({MasterRange Sep Column}, {MasterRange SRC ID}, [SRC ID]@row, {MasterRange Plan Column}, "Plan", {MasterRange Year Column}, Year@row), 1),

        IF(Month@row = "Oct",

            INDEX(COLLECT({MasterRange Oct Column}, {MasterRange SRC ID}, [SRC ID]@row, {MasterRange Plan Column}, "Plan", {MasterRange Year Column}, Year@row), 1),

        IF(Month@row = "Nov",

            INDEX(COLLECT({MasterRange Nov Column}, {MasterRange SRC ID}, [SRC ID]@row, {MasterRange Plan Column}, "Plan", {MasterRange Year Column}, Year@row), 1),

        IF(Month@row = "Dec",

            INDEX(COLLECT({MasterRange Dec Column}, {MasterRange SRC ID}, [SRC ID]@row, {MasterRange Plan Column}, "Plan", {MasterRange Year Column}, Year@row), 1),

        "Invalid Month"

))))))))))))