How to split a record

[raghuprabhu]

I have a spreadsheet with two worksheets.
One is list of dates when the rate change
The second shows the calculations.

Row 3 is not correct.
Rows 7,8 and 9 are correct.
How do I split the rows. For example I input 01FEB15 in B7 and 21DEC15 in D7, I want the record to split up.

[Kaper]

Do you need it splitted automatically? or just calculated?
If you need it splitted then probably macro shall be used for that if just calculated - formulas would be enough (but will not too be easy). Let's focus on nformulas and let's assume in bolumn C in your first sheet there are Rates for given periods.

so in H3 (and copy it dow to rows 7:9) there will be formula:

Please Login or Register  to view this content.

In J3 (of course copy down too easier one:

Please Login or Register  to view this content.

L3 also easy:

Please Login or Register  to view this content.

but here we need real number. So the real one will be much more complicated and it will be an array formula*

Please Login or Register  to view this content.

as you see the first part is the same, we only inserted long sum instead of text "multiple"

Check it out in the attachment

*...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[raghuprabhu]

Thanks Kaper...I want to split it automatically when the ToDate is input.

Regards

Raghu

[Kaper]

Check attached
Cells are color-coded:
Yellow - cells for input (dates) blue (single cell formula). Darker green: formula copied down to light green.
Two styles (two workbooks)
1st with totals immediately under calculations (more sophisticated formulas)
2nd with totals in a row where input goes

[raghuprabhu]

Kaper, Thank you very much for your effort...I am trying to do it in Access 2003.

[Kaper]

As for Access I'd suggest starting thread on appropriate forum: http://www.excelforum.com/access-tables-and-databases/
If excel part is finished, would be lovely if you mark thread Solved.