Index Collect Based on Another Cell

Options

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.

Tags:

Answers

  • trevrobwhite
    Options

    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"

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!