Hey!
So i got my accountant colleague to transfer all the data and check if everything works fine and we encountered 2 problems; 1 macro based and the other formula based... and surprisingly i could sort out the issue with macro which btw if i look back at it now wasnt really a macro issue but a formatting issue, but still cant get hold of the correct approach to sort out the formula based issue.

Actually, on a given day if there are no transactions in a given A/c the "consolidated report generates a N/A error However, i want it to show the balance after the last transaction before the report date.
So if there are no transactions on 5th,6th,7th and i'm generating a report of 7th i require it to show me the final balance of 4th. I tried different formulas to get the desired result but cudnt manage. So here i am once again asking for ur kind help...

the variations of the formulas i tried:

=LOOKUP(2,1/('Kotak 811'!B5:B1300=Report!G1),'Kotak 811'!H5:H1300)

=LOOKUP(2,1/('Kotak 811'!B5:B1300<=Report!G1),'Kotak 811'!H5:H1300)

=LOOKUP(2,1/(('Kotak 811'!B5:B1300=Report!G1)*('Kotak 811'!B5:B1300<Report!G1)),'Kotak 811'!H5:H1300)

=IF(ISNA(LOOKUP(2,1/('Kotak 811'!B5:B1300=Report!G1),'Kotak 811'!H5:H1300),LOOKUP(2,1/('Kotak 811'!B5:B1300<=Report!G1),'Kotak 811'!H5:H1300))
All these formulas to be entered in B4 in Report Sheet. Workbook sample is the same as wot Sir JBeaucaire posted earlier.

@JBeaucaire
Btw, the date format was causing the report to not be generated for first 9 days of the month because of the format of the date.. my data has the date format set as "DD-MMM-YY" so to match yours i converted into "DD-MMM" but still on first 9 days the date wud still come out as "D-MMM" which did not allow the filtering to take place.

Thanks Again

Regards

Mohit