How to get invoice amount with corresponding date range

Hi,

I have a data set that has invoice amounts with corresponding bill date range. I need to get those invoice amount auto pop up in correspond month columns.

Invoice Number Bill From/Bill To Invoice Amount Dec '13 Jan '14 Feb '14
1 01/01/2014 00:00 $100
01/02/2014 00:00

2 12/30/2013 00:00 $200
12/30/2014 00:00

3 01/01/2014 00:00 $300
01/02/2014 00:00

Can someone please help me?
Thank you so much!

Excel Question.xlsx

Thanks for the reply.

I would like to see amount auto-pop in months column (Dec '13 Jan '14 Feb '14...etc...they are just in title in row 1, but I need amount showing under each month)

record #2 should be :
2 12/30/2013 00:00 $200
12/30/2013 00:00

So it only should show amount under Dec '13 column. sorry for the typo in my previous post.

Its a bit of a long way around but have a look at the one I have attached.

Basically you need to convert the date into text in the right format then use an if function to display it.

There are other functions like lookup and vlookup which will do they same thing more efficiently but this may be too complicated. Have a look at it and see what you think.

I made certain assumptions like US currency based upon the date format, but you can adjust this in the formatting to suit your. Needs.

Thanks daffodil11. it's exactly what I want.

in your formula, it says IF(YEAR(E$1)&MONTH(E$1)=YEAR($B2)&MONTH($B2),$C2,"")...usually people use $ both before and after a cell ($ letter $ #), but here I only see one $. Do you mind explaining to me why we only need one $ here?

Thank you!

if I have a lot more rows of data coming up, can I just drag the formula down?

I see what you mean and I can clearly understand all the formulas and drag them down to more rows if I need.

Thank you very much for the quick reply!

This is my first time here. this forum is awesome!

Thank you!

that is very smart. Thank you daffodil11, I learnt a lot today!

Have a great day!!!

will do!

I just found another problem,

what if the date format came from raw data has two dates in one cell: "08/01/2013 00:0009/01/2013 00:00"

Besides using RIGHT and LEFT function to break it into two cells, is there any easier way to get the amount auto-pop?

Thanks!

try this..

Hi,

same problem, but different format of date...what should I do?

if the date format came from raw data has two dates in one cell: "08/01/2013 00:0009/01/2013 00:00"

How do I auto-pop amount under months columns?

Thank you!

Originally Posted by daffodil11

Sure. The purpose of $ is to create absolute references.

If I type = A1 in cell B1, and then copy that formula into F20, the reference will adjust itself to E20.

If I put a $ in front of the row =A$1, it will make the row as an absolute reference, but leave the column relative, so that copied to F20, it will say =E$1.
If I reverse it and copied to F20, only the row will adjust but the column becomes absolute. So =$A1 copied out to F20 will now say =$A20.


In my formula, I wrote D2 first. I like being efficient, so I write absolute references only where needed so when I copy D2 into E6, for example, I want it to still target what's relevant. It need to target the date in the header, and it needs to always target that first line, so

=IF(YEAR(D1)&MONTH(D1)=YEAR(B2)&MONTH(B2),C2,"")
becomes
=IF(YEAR(D$1)&MONTH(D$1)=YEAR(B2)&MONTH(B2),C2,"") so that wherever I copy to, it's still referring to the top line, but allowing the header column to adjust itself.

I always need the Invoice Data to stay absolute in relation to that column, but I want it to adjust as I copy down.

=IF(YEAR(D$1)&MONTH(D$1)=YEAR(B2)&MONTH(B2),C2,"")
becomes
=IF(YEAR(D$1)&MONTH(D$1)=YEAR($B2)&MONTH($B2),C2,"")


Lastly, I also want the invoice amount to change depending on the row I'm on, so we make it's column an absolute value

=IF(YEAR(D$1)&MONTH(D$1)=YEAR($B2)&MONTH($B2),C2,"")
becomes
=IF(YEAR(D$1)&MONTH(D$1)=YEAR($B2)&MONTH($B2),$C2,"")


Now when I copy D2

=IF(YEAR(D$1)&MONTH(D$1)=YEAR($B2)&MONTH($B2),$C2,"")

into E6, only the relative references adjust themselves, becoming:

=IF(YEAR(E$1)&MONTH(E$1)=YEAR($B6)&MONTH($B6),$C6,"") so that the header, invoice amount, and bil date are retargeted but without changing row or column as appropriate.


Ultimately, it comes down to programming a single cell well enough that its formula can be applied to all other cells and what its pointing to will automatically adjust. Otherwise, you're left writing a different formula for every cell.

I tried to delete the last post but couldn't.

Since my first question has been solved, I'll mark this one as solved.