Enter correct values in non contiguous cells depending on frequency.

**christopherp** · 06-04-2011, 03:11 PM

Budget.xlsx

Hi There

I am preparing a worksheet to track several months of budget numbers June 11 - Dec 11...Pls see attached.

As you can see from the attachment income and or expenses can occur weekly, fortnightly or monthly. The weekly and monthly numbers are easy to enter and have worked out formulas to get the values in the appropriate cells by searching for the day of the week (mon, tue, wed...etc) or the day of the month (1, 2 , 3 etc....)

The part I am having trouble with is working out how to get an income / expense value which occurs fortnightly to appear automatically in the correct cells of the corresponding row depending on first occurrence (i.e. first occurence is 01/06/11 and then every 14 days after that until 31/12/11).

I would like the formula to reference the frequency chosen in the frequency column (i.e weekly frequency would cause the value to appear every seven days, forthnightly would appear every 14 days and monthly would appear on same day of every month i.e 25th of the month

Any help you guys can offer would be greatly appreciated.

Cheers

Chris

**MarvinP** · 06-04-2011, 03:53 PM

Hi Chris,

This would be an easy probem if you data was in a Table. see http://office.microsoft.com/en-us/ex...010048546.aspx
And you used Pivot Tables Groups with number of days equal to 14. See http://www.contextures.com/xlPivot07.html but make the number of days 14 instead of 7.

Ever thought of aranging your data differently?

**christopherp** · 06-08-2011, 01:24 AM

Thanks for the response Marvin

I have had a good look at both links and don't know if a table would allow me to set things up the way I had hoped.

I am just hoping for a formula that will place the value in every fourteenth cell / column form the first date.... there must be one?

Cheers

**christopherp** · 06-09-2011, 11:07 AM

Originally Posted by christopherp

Attachment 111192

Hi There

I am preparing a worksheet to track several months of budget numbers June 11 - Dec 11...Pls see attached.

As you can see from the attachment income and or expenses can occur weekly, fortnightly or monthly. The weekly and monthly numbers are easy to enter and have worked out formulas to get the values in the appropriate cells by searching for the day of the week (mon, tue, wed...etc) or the day of the month (1, 2 , 3 etc....)

The part I am having trouble with is working out how to get an income / expense value which occurs fortnightly to appear automatically in the correct cells of the corresponding row depending on first occurrence (i.e. first occurence is 01/06/11 and then every 14 days after that until 31/12/11).

I would like the formula to reference the frequency chosen in the frequency column (i.e weekly frequency would cause the value to appear every seven days, forthnightly would appear every 14 days and monthly would appear on same day of every month i.e 25th of the month

Any help you guys can offer would be greatly appreciated.

Cheers

Chris

Hi Guys

I just added a test spreadsheet to play with..... as always any help anyone can offer would be greatly appreciated.

Cheers

Chris