How to auto populate a row of cells based on the frequency descriptor in a cell

[Tony]

Hi


I would like to know if it is possible to auto populate a spreadsheet row (right side) with the number "1" depending on the frequency descriptor on the (left cell).


I have a call cycle spreadsheet whereby:


Column A is the customers_name

Column B is the frequency of calling eg Weekly,forthnightly,monthly,quarterly, annually

Column C to AX represents Week 1 to Week 4 for each of the calender months - Jan -December.


So if I type "Weekly" in B1 then C1 to AX1 is auto populated by the number "1"


So if I type "Monthly" in B2 then C2 to AX2 is auto populated by the number "1" for 12 periods ie week 2 in each month.


I hope this is clear. If no I can send a file.


Many thanks , hope this is possible

 

[kchiba]

Hi Tony,


try this formula in Columns C onwards


=IF($B3="weekly",1,IF($B3="monthly",IF(MOD(COLUMN(C4)+2,4)-2=0,1,""),""))


kanti

 

[Tony]

Hi Kanti


Many thanks for the formula. It works very well for the weekly and monthly frequency as per my posting above.


As usual the request from the team is to add more frequency descripter types - namely "Bi Monthly" (ie six times a year), "Quarterly" ((ie 4 times a year), "4 Monthly" and "6 Monthly".


I am trying to work this out by using your base formula. I am not sure if this is too complex to put in one formula.


Once again many thanks and have a great day.


Cheers

Tony

 

[kchiba]

Hi Tony,


Which week within the period would you want to place the "1".


This is important as the period is broken up into weeks.


cheers


kanti

 

[Tony]

Hi Kanti


I have tried to modify your formula to include other descriptors as follows:-


In most cases the "1" would default to the second week.


However, in the formula below I have not been able to include "Annually" -say in week 2 of July.


'=IF($B3="weekly",1,IF($B3="monthly",IF(MOD(COLUMN(D4)+2,4)-2=0,1,""),IF($B3="Bi Monthly",IF(MOD(COLUMN(D4)+4,8)-4=0,1,""),IF($B3="Quarterly",IF(MOD(COLUMN(D4)+6,12)-6=0,1,""),IF($B3="4 Monthly",IF(MOD(COLUMN(D4)+8,16)-8=0,1,""),IF(MOD(COLUMN(D4)+12,18)-12=0,1,""))))))


Once again thanks for your help.


Cheers, Tony

 

[kchiba]

Hi Tony,


This is a bit more complex, maybe we should use a table of frequency by where it shows up.

I will get back to you when I have a solution.


cheers


kanti

 

[Tony]

Thanks Kanti

 

[kchiba]

Hi Tony,


Create a table on another sheet that look like the following:


Weekly 1

Monthly 4

Quartely 13

Bi Annual 26


the logic here is the 52/period, so weekly = 52/52, monthly = round(52/12,0) quarterly is divided by 4 and Bi annual is divided by 26


On the main sheet the assumption is that week 1 starts from column C and that the year is divided into months of 4 weeks.

The formula in C2 would be, and then copied to all cells


=IF(MOD((COLUMN()-2),INDEX(Sheet2!$B$1:$B$4,MATCH($A1,Sheet2!$A$1:$A$4,0)))=0,1,0)


Note that the 1 is at the end of each period, so it is in week 4 for monthly etc.


cheers


kanti

 

[Tony]

Hi Kanti


Many thanks. A lot of thought has gone into this.


I will do as suggested and give you some feedback later today.


Once again that for your time and talent.

 

[Tony]

Hi Kanti


The Formula worked perfectly.


Great work.


Tony