Formula to copy anual invoices all in 1 sheet to monthly sheets and fix in Index/Match

Hi,

I need help in 2 questions:

- i have a anual sheet with many invoices listed from all months of year and i need a formula to copy them to the monthly sheets... and also copy the remaining aspects of the invoice like the number, value etc... In the anual sheet the invoices will not be listed in order, all the january, then february, etc... they will be mixed. It would be great if the formula in the months sheets sort the dates from day 1 to 31 but that is not mandatory

- i need help with a index and match formula (or any other solution you find better), i need the formula to compare exact values and not aproximatelly values. The question is to compare movements from my bank extract with the invoice values . A kind of bank reconciliation.

I send in attachement a example sheet that i hope you can understand and help me.

Thanks

Budget_Excel Forum.xlsx

In the attached file I put this formula in cell J3 of the All_Invoices sheet:

=IF(A3="","-",MONTH(A3)&"_"&COUNTIF(J$2:J2,MONTH(A3)&"*")+1)

and then copied this down to beyond the bottom of your data - the hyphens indicate where the formula is active (I've also coloured the cells blue, to make them more obvious). This formula identifies the month for each invoice and then adds a unique sequential number to this.

In the January sheet I put the month number (1) in cell D1, and then this formula in cell D29 (also coloured blue):

=IFERROR(MATCH($D$1&"_"&ROWS($1:1),'All Invoices'!J:J,0),"-")

When this is copied down it will return the row number where the first record, then the second record, then the third record (etc.) exists on the main sheet.

I used this formula in E29:

=IF(OR($D29="-",$D29=""),"",INDEX('All Invoices'!A:A,$D29))

and this can then be copied across to H29 to bring the appropriate data across for the first record, and when the cells E29:H29 have been given the appropriate formatting using the Format Painter from the row below, they can be copied down as far as required.

The block of formulae from D29:H38 can just be copied into the same block in the February sheet, but you will need to manually add the month number (2) to cell D1, and then you can just Ctrl-Drag the tab of one of the monthly sheets to create another copy, then change the entries in A1:D1 of each sheet.

Hope this helps.

Pete

Hi Pete,

Its awsome. i dont have much time right now to see closely and continue to the other months, but i can see its working great. Thanks for your help and patiente.


Can i just ask you 1 more thing?

In the months sheets, cell N73 and down (O73 an down, P73 and down) i need formulas that compare bank extract movements to the invoices values , a kind of bank reconciliation. I already tried a simple index-match function but i found that its comparing similar values and not exact values, i placed in red 1 error in January sheet (its comparing 17,50 to 15 ). Can you help me with these?

Thanks. This will help me a lot.