determining which of 12 columns to draw data from based upon a single variable

[bfletch01]

Hello,

First time doing this; hope this is the proper forum.

I am working on a budget reporting package. The input page for managers has twelve columns (one for each month). At the start of the fiscal year, all columns are empty (zero). As the year progresses, the manager moves from left to right entering data for January in column E, then February in column F, then March in column G, etc.

I created a counter that increases by one any time a manager inputs revenue data in months moving left to right (value in revenue cell >0 means the counter number increases by one). Using an if/then statement with a Date(2000,[counter cell],1) implement, I can account for the month to report based upon the counter number when that cell is custom formatted 'mmmm'. There are probably easier ways to do this but this is what I found worked.

Ongoing issue:
Now I need a single report page (to be re-used every month) to draw data from the column corresponding to the proper month. For example, if the counter value = 1, then I need this report to pull data from the 'January' column on the input tab. If the counter value = 2, pull from 'February' column, and so on.

I can do one month by simply inputting =if((S5 [this is the cell the counter is estabished] = 1, then E6 [this corrosponds to the first revenue input cell in the 'January' column. However, i do not know how to 'stack' this type of calculation so that it functions as such:

IF the [counter] number = 1, pull the data from the 'January' column OR IF the [counter] number = 2 then pull from the 'February' column OR IF the [counter] number =3, pull from the 'March' column and so on, up through the 12th possible counter number, corresponding to the 'December' column.

It occurs to me that there might be a function somewhere where it could assess the counter number and direct the data extraction from the appropriately numbered (or named) column, but my excel expertise ends there.

Can anyone help?

Ben

[FDibbins]

Hi and welcome to the forum

Sounds to me like this is a job for...superman!!! oops I mean you can use INDIRECT() for this, but to be more specific, I will need to see a sample of what you are working with?
Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic