Formula assistance, please

Loann McGee

Good afternoon,

I'm not even sure this can be done, but I'm giving it a shot. The picture below is a snip of the sheet I'm working on. I'm wanting to figure out if there's a formula that will put the data from a single cell on the Sep row to the "Current Month" row - but the catch is that is needs to look for the last numerical data in the column so that when I input the number for Oct it will update the Current Month row to the new number.



I hope that makes sense. This is so I can have my dashboard automatically update instead of me having to go in each month an update what row the dashboard is pulling the data from.

 

 

Paul Newcome

I would suggest adding 2 columns that can later be hidden (immediately to the left of the first column is where I personally would put them). We will name them "DataHelper" and "MonthHelper" (no spaces) for this example. In the "DataHelper" column you would put the following (change column names to fit what you have):

 

=INDEX([Month Column Name]@row:[Name of Final Column to Pull Data From]@row, 1, COUNTIFS([Month Column Name]@row:[Name of Final Column to Pull Data From]@row, NOT(ISBLANK(@cell))))

 

Drag fill that down, and it will give you the data from the last populated cell in that row. If there are blanks in any of the middle cells it will throw it off. This formula assumes that column 2 will only be populated after column 1 has data in it and so forth.

 

In the MonthHelper column, just put the month number next to the appropriate month (1 next to Jan, 2 next to Feb, etc)

 

Then in the cell to the right of "Current Month: " put:

 

=INDEX(DataHelper1:DataHelper12, MATCH(MONTH(TODAY()), MonthHelper1:MonthHelper12, 0))

 

This will look at the month for whatever TODAY is and then pull the data from the corresponding row of the DataHelper column (which holds the data of the cell furthest to the right that is not blank).

 

Because of using the TODAY function, your sheet will have to be opened to update to the current month which will happen every time data is entered.