How To Fill Series Or Formula To A Specific Row Without Dragging In Excel? - Cash Flow

[jupster]

Hi all, I am hoping to seek expert advice on how I could create an excel spreadsheet to show projection on cash flow per month.
I have here a spreadsheet, and I wonder if there's any formula I could do

1) to have the cell (I5, P6 etc) to automatically fill up based on the start date.
currently I have done it manually, but if my task start date is changed, I have to manually change the horizontal rows.

2) divide the remaining fund for the rest of the task period. Example G1 cell is the 10% of E4, and I would like to have cell H4 automatically fill up based on the remaining funding up divided equally till the end month of that particular task, in this case H4 up to M4

Thank you very much in advance for your kind advice/attention
Best wishes
Jup

[alansidman]

You profile indicates XL2003 yet your file indicates you are using a later version. Please update your profile as it will make a difference in the solution offered.

[DJunqueira]

Solution offered:

Please Login or Register  to view this content.

[kvsrinivasamurthy]

Since you are manually inputting data in the range G3:R6 formula will not help. It needs worksheet event.
Code for worksheet_Change event is below. Range Is taken as G3:R6, if needed it can be changed to required value say G3:R25
Currency format is your default set value.

Please Login or Register  to view this content.

Worksheetevent

How to use the code
Right click on Sheet tab --> view code
Visual Basic (VB) window opens.
Paste the code
Close the VB window.
Save the file

[jupster]

O WOW!!!! Thank you DJunqueira & kvsrinivasamurthy!
I didnt expect to get a response so quickly - both resolutions work great~~

*bows*

Thank you for your very kind advice

Best
Jup

[kvsrinivasamurthy]

Just suggestion. This appears better because entry of initial payment can be done in one column. Searching for correct column is avoided. Formula takes care of all entries in H3:S6. No VBA code is required.
Pl see file. Extra column G is inserted. Formula in H3 then copied to full range.

=IF(MONTH($C3)=MONTH(H$2&1),$G3,IF(AND(MONTH($C3)< MONTH(H$2&1),MONTH($D3)>=MONTH(H$2&1)),($E3-$G3)/$F3,""))

[jupster]

Hi DJunqueira,
I was trying to understand your solutions, but I realised it does not work when the start and end spans across 2 years - example row 8.
I think it's something to do with the MONTH formula, but I am not sure how I could tweak this to also look at the YEAR?

Thank you in advance for your advice

Best wishes,
Jup

[JeteMc]

My suggestion is to convert the month names in row 3 into dates then apply custom formatting (mmm)
The formula could then read: =IF(EOMONTH($C4,-1)+1=G$3,$E4*0.1,IF(AND(G$3>=EOMONTH($C4,0),G$3<=$D4),($E4-$E4*0.1)/$F4,""))
Let us know if you have any questions.

[jupster]

wow all of you guys are absolute amazing!
it works like a charm now and I've learnt new formula from you all.

very much appreciated!!!

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.