Transferring Data from many sheets to a single sheet

Hey!
Firstly thanks for the reply!

I'll work on the sample and upload a more accurate worksheet... i just thought a crude sample might be enough to get things started, my bad for taking things for granted... Its 1:20 am right now, and i gotta work early tomorrow, so i'll try and upload it as soon as i can tomorrow morning...

As this is my bank book, all the indivdual data sheets are basically bank statements of particular accounts.. we have 11-13 accounts in all, sheet names would be ICICI1476, ICICI2685, Citi6018.... i thought i cud adapt and manage the additional sheets once i get the way out conceptually....

Data formats for all these individual sheets would be exactly the same as sheet1...(all are bank statements)

And for results now that I realise it, I should have copy pasted the results in the format I want it to appear... So sorry about it! I really messed up! (Considering that was what I was looking forward to get help on!)

Thanks Again for replying!

Regards

Mohit

Ok so i've updated my workbook to include the exact no. of worksheets i'll be working on with their exact names. However, havent really added any data after 2 sheets...

Also i realised that there are actually 17 accounts that we have to track! So thats quite alot of no. of sheets... however, the records that would match a particular day could range from 0 entries to 20 entries on a good active day... (but i wud not want a limit on that, cuz its really uncertain)

I'm hoping this updated worksheet cud provide a good base to get started. If theres anything that i missed out please let me know to fill it in...

Thanks Again for all the time and help

Regards

Mohit

Sir,
That is beautiful! Thanks alot for ur time and effort!
I'll take this with my accountant colleague and let him check if everything is going right for him in this workbook... and get back if there r any changes that we might have....

Hey can you suggest how do i start learning basic vb language?

Thanks again!

Regards

Mohit

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:

Please Login or Register  to view this content.

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

Sir!
I dont any idea wotsoever of wots going on in that formula!
I'd taken about 30-40 minutes to setup the formulas (finding and replacing sheet names in a separate word doc) for my data workbook and other basic formatting and stuff to start working on it and now i'd really feel like a lousy idiot if i dont use ur formula just for the effort u took to even write that down!

Note to self: Do pilot runs and check and recheck wot could possibly go wrong before implementing something new i dont really have a clue about on full scale..

I feel really ashamed to ask this but could just gimme the part where i can just get the lookup thing sorted out so to not to have everything all over again... hope ur not offended or nething by this.

Thanks for everything!

Regards

Mohit

Yes! Sorry for the vague rants in my previous post, i was really tired and it was late in the evening and i didnt even recheck if i'd written complete sentences or not...

I meant if u cud just isolate wots wrong with my lookup formula and intended to ask u help me edit it....or editing your formula without using the indirect function to setup my sheet and make the lookup work perfectly?

I tried breaking down your code back then but i got lost when the indirect function came in last evening.. :S
But once i got some sleep and got my senses back this morning i managed to break it up and realised i just had to add another lookup criteria of the result being greater than 0 and once i reach office i'll give a crack at it...

Thanks Again for ur wonderful help!

Regards
Mohit

PS: I think this thread would now be finally Officially SOLVED! :P