Streamline my Formula

Cristina Andersen

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?

Lucas Rayala

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.