I have an excel file that I am currently using to create a chart every month. As it currently stands every month I have to change the cell it is referring to. On one sheet I have the multiple business units and months Example:

      Col A    Col B Col C Col D .....
Row1= Unit 1 - Jan - Feb - Mar - Etc.....
Row2= Del Exp   .02  .03   .07.....
Row3= WH Exp    .05  .07   .06.....

On the other sheet I have to manually go in and change it every month for the current month. So if the month is February I have to go change all the January numbers to look at the February column. I'm wondering if there is a function that would easily identify the data for the current month? So that when February numbers are entered it automatically populates it for the graph. Would I be able to add a MTD column at the end of my first sheet that looks to see the last data entry in that row, and then on the other sheet just reference that cell?

Any help is greatly appreciated.

Screen shot

  • You may like to show a screenshot of the other sheet too for clarity. I suspect most likely HLOOKUP function may help here. You can create a single column that maps to data coming from 12 columns, in such a way that HLOOKUP references to the column where it's current month and have your Chart refer to the single column which receives data from one of the 12. – patkim Jan 28 '17 at 1:31

So currently you have a formula looking at B2 and next month you want it to look at C2?

Instead of looking at B2, look at OFFSET(A2,0,MONTH(TODAY()))

So when it's January, MONTH(TODAY()) will return 1, and you'll be looking at one column to the right of A2. In July, MONTH(TODAY()) will return 7 and you'll be looking seven columns to the right of A2.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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