Index Question

Cristina Andersen

I have two smartsheets. One contains my budget and one contains budget vs actual. Currently, Im using an index function to find the budget for the month of each cost account BUT every month I have to go update the column index in the function so that it updates. What I'd like to do is make a reference where I can just put in, for instance, 2, and it would move to the next column. Is this even possible?

Paul Newcome

It could be very possible. Are you able to provide a screenshot that shows what you are talking about?

Cristina Andersen

Here's a sample.

Paul Newcome

Is the sheet on the right the sheet you are referencing?

What exactly are you referencing and which part needs updated?

Cristina Andersen

yes the sheet on the left is where my formula is housed. The sheet on the right is where Im referencing. Id like to put a cell with a number that will update all of the column index to 2 or 3 and so on for the year. As you can see each of these codings would need to be updated each month...manually.

Paul Newcome

If you are just wanting the current month, you could replace the hardcoded number with...

MONTH(TODAY())

Cristina Andersen

But how would it know which column to reference? When I set the range, I made the 1st column to be January's budget numbers but I dont know how (or if its possible) to set the column index to recognize a typed month. Is that possible?

Paul Newcome

For the range, select all 12 columns.

If you use MONTH(TODAY()) in the column number portion of the INDEX function, it will automatically update to pull for the current month.

Cristina Andersen

So if I name the Columns themselves, January, February, March and so on....it will auto update?

Paul Newcome

No. You would select all of the columns for the range.

MONTH(TODAY()) will generate a number that will be the same as today's month number (in this case 1, feb = 2, mar = 3, so on and so forth.

If you drop that into the 3rd portion of an index function, it will tell the formula which column to use from the 12 column range.

=INDEX({Range to pull from}, row_number, MONTH(TODAY()))

Cristina Andersen

Dude! It worked! Thank you so much!

Paul Newcome

Happy to help. 👍️