Using Excel 2010
First and most importantly, I need to auto populate a spreadsheet representing a full year / 365 days from a data set.
Secondly, if a specific data item changes, whether the amount changes or the payment day changes, I need it to automatically update the spreadsheet through the entire year.
Does the following require a massive 'If' formula that needs to be applied to every destinaton cell?
Data Set:
Days of Month Payment Schedule......Vendor A.....Vendor B.....Vendor C
..................1................................ $100........... $50........... $10
..................2................................ $150........... $75........... $20
..................3.................................$ 0..............$20............$16
..................4.................................$ 0..............$ 0.............$ 0 (No payments on the 4th)
..................5.................................$ 30............$60.............$10
.................etc
The calendar below will show all 365 days while the payment schedule above may only have 15 days where there are required payment entries, so there is no need to list all 31 days in the data set, (you could eliminate the 4th in the example above), unless thats the best way to set it up.
Result Needed:
Need to auto populate the following spreadsheet from the data set above (to populate entire year), i.e., if the day is the 1st, the auto entry for Vendor A is $100, Vendor B $50 and for Vendor C $10 for every month, per the data set above.
365 Day Calendar of Scheduled Payments
Date........Vendor A......Vendor B......Vendor C
1/1/14
1/2/14
1/3/14
etc
2/1/14
2/2/14
2/3/14
ect
3/1/14
3/2/14
3/3/14
etc
If you could provide an appropriate formula, I would appreciate it. Thank you.
First and most importantly, I need to auto populate a spreadsheet representing a full year / 365 days from a data set.
Secondly, if a specific data item changes, whether the amount changes or the payment day changes, I need it to automatically update the spreadsheet through the entire year.
Does the following require a massive 'If' formula that needs to be applied to every destinaton cell?
Data Set:
Days of Month Payment Schedule......Vendor A.....Vendor B.....Vendor C
..................1................................ $100........... $50........... $10
..................2................................ $150........... $75........... $20
..................3.................................$ 0..............$20............$16
..................4.................................$ 0..............$ 0.............$ 0 (No payments on the 4th)
..................5.................................$ 30............$60.............$10
.................etc
The calendar below will show all 365 days while the payment schedule above may only have 15 days where there are required payment entries, so there is no need to list all 31 days in the data set, (you could eliminate the 4th in the example above), unless thats the best way to set it up.
Result Needed:
Need to auto populate the following spreadsheet from the data set above (to populate entire year), i.e., if the day is the 1st, the auto entry for Vendor A is $100, Vendor B $50 and for Vendor C $10 for every month, per the data set above.
365 Day Calendar of Scheduled Payments
Date........Vendor A......Vendor B......Vendor C
1/1/14
1/2/14
1/3/14
etc
2/1/14
2/2/14
2/3/14
ect
3/1/14
3/2/14
3/3/14
etc
If you could provide an appropriate formula, I would appreciate it. Thank you.
