Streamline my Formula

Hello everyone, Im currently using a very tedious process of finding the number that I need from this sheet. It is a pasted excel sheet into a Smartsheet. There are multiple repeating "CC Codes" that are only differentiated by the "111" number in the Primary Column. I need to find the value for each of these items on another sheet. On another sheet, the formula below is being used to return the number I need: INDEX({2022B}, 71, MONTH(TODAY())) * 1000.

What I want to do is find a formula that will find the number I need WITHOUT me having to change the row number for every value I need on the other sheet. Im open to slightly reconfiguring this sheet (like with Parent /Child ) but I dont want it to be tedious. Any thoughts or suggestions?


Answers

  • Lucas Rayala
    Lucas Rayala ✭✭✭✭✭✭
    edited 01/13/22

    So in your INDEX function, your range is the group of months, correct? And you are using the MONTH function to provide a column that matches the month number?

    You can create a few helper columns to make this happen. Two helper columns on the original sheet and one helper (+ the final output column) on your second sheet where you want the final value.

    FIRST SHEET

    In the first helper column (HELP), paste the corresponding "111-x" number into it. So lines 81 through 94 would be 111-235. In the second helper column, HELP2, use the column formula:

    =HELP@row + [CC Code]@row

    SECOND SHEET

    In your other sheet, have a column that contains the "111-x" number + CC Code (it has to be formatted the same as you see in HELP2 from the previous sheet). Call that one HELP3.

    Create a new column for your final output column formula (this is the one you requested in your question) and add this column formula:

    =INDEX({2022B}, MATCH(HELPER3@row, {HELP2}, 0),MONTH(TODAY()))*1000

    Where {2022B} is your original range and {HELP2} is a cross-sheet reference to the HELP2 column created above.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!