I have my spreadsheet set up as follows: first 10 columns full of descriptive data followed by 24 columns of month data (2 columns per month).

The last 24 columns are static and the values are updated manually. I may add columns between the first 10 columns as needed.

So one of the columns in the first 10 is called Current Price and each month section also has one column called Price.

What I want to do is have the Current Price column updated automatically depending on the current date.

I created a named range and have this formula =COLUMN(VendorsByMonth)+(MONTH(NOW())-1)*2+1 to get the index of the Price column of the current month.

But I'm not sure how to apply the INDEX or OFFSET functions to get and set the cell value.


1 Answer 1


The answer is this:


For example:

INDEX(B2:K4,1,5) the range has three rows (From 2 to 4). The 1 is row 1 within the range which is row 2 of the whole spreadsheet. The 5 is column 5 within the range which corresponds to Column F (the 6th column of the spreadsheet).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.