Formula to automatically pull total into correct date column

I actually asked this question last year and was given an answer and it worked but has since stopped working and I cannot recreate it.

I have 6 tabs in one workbook and I have them all set up for the basic entries

PO/PR, Vendor, date, total and beside them I have months listed across the columns for our fiscal year October - September. I want the amount in column D to automatically go into correct month according to the date in column C.

I want to copy this for all 6 tabs. I have no idea why the original formula isn't working any longer.

I have attached an example

Originally Posted by dflak

I'm using a helper row that you can hide. If you don't want the helper row then you can develop a formula for each column based on the month number.

Thank you. I am unfamiliar with a helper row? How does it work?

Originally Posted by krisryan

Thank you. I am unfamiliar with a helper row? How does it work?

I can't seem to copy and paste your formulas into my worksheet even after adding the helper bar. Is there a trick to it?

@dflak I tried putting your formula in E4 but it doesn't fill in the amount.

Okay here is the book with that formula in E4

Oh I didn't know it needed the helper row sorry. I'm just trying to learn.

My sincere apologies it works very well.

I was trying to do something similar but have different years for each worksheet. Is this a possibility ?

Reference post: "Allocate Amount for Month"

I didn't know I was hacking I will not post and mark that as closed. In no way was I trying to cause any harm.

I can't tell you how much that helped. Thank you so much!

Originally Posted by dflak

Let's look at the formula: =IFERROR(IF(AND(MONTH($C4)=E$1,$C4<>""),$D4,""),"")

We'll pick it apart from the "inside out" which is the way excel executes things: AND(MONTH($C4)=E$1,$C4<>"") - first of all, I am using partial absolute referencing for the cells - $C4 means always use column C, but when you copy the formula down, the row can change. E$1 means always used row 1 but when you copy the formula across, the column changes. Something like $A$1 means always use cell A1 no matter where the formula is copied to.

So I did not want to type =IFERROR(IF(AND(MONTH($C4)=10,$C4<>""),$D4,""),"") in column E, =IFERROR(IF(AND(MONTH($C4)=11,$C4<>""),$D4,""),"") in column F, and so on. I put the 10, 11, 12, etc. on row 1 and arranged to have the formula look at that row. So with the dates ($C) and amounts ($D) locked by columns, and the month number ($1) locked by row, I only had to work out the formula in one cell. Then I could copy it to wherever I needed it. the unlocked parts of the row or columns in the formula are relative and change depending on where the formula is copied to.

Thank you. I definitely appreciate it. I will be using the helper row now that I understand it.

Originally Posted by ron2k_1

Here is an unecessarily long formula that does not require a helper row:

Formula:

Please Login or Register  to view this content.

Just ensure that the month labels in row 1 do not have a space, like in the case of May, June and July in your example have a space after the word. Please delete the space after the month label.

I am on my phone at the moment and am unable to give you rep but I will add it in the morning. Thanks again.

Originally Posted by krisryan

I can't tell you how much that helped. Thank you so much!