Formula to pick value from 1 cell and fill in other cells upto the value in first cell

[rajkenya1]

Hi Guys


Am trying to find a formulae whereby in the attached spread sheet I have say client 1 in Column B who enrols on a program for 12 months (hard code number in cell c4) and starts in Month 1 (Hard Code in Cell d4) then in cell e 4 the number 1 appears upto the next 12 months. Similarly if the client 2 starts a program for 6 months but starts in month 2 than in cell F5 the number 1 appears and continues for 6 months. The formula should be flexible such that if I change the number in say cell c5 from 6 to 5 and change the month from say Month 2 to Month 3 than the resulting numbers in column e to R change accordingly


Please see attached spreadsheet as an example of what I am looking for.


Thanks for your help in advance.

 

[Nebu]

Hi:

Please find the attached, formulas in the colored cells.

Thanks

 

[rajkenya1]

Dear Nebu

You absolute Genius. Thank you very much.

Very much appreciated. Thanks again

 

[rajkenya1]

Hi Nebu

I notice there is an error in the formula. It works fine say up to 99 months in the attached spreadsheet but as soon as i put 100 than it takes it as month 1. What could be the issue?

Similarly take a look at client 1 who is in for a period of 18 months and starts month 1 but then is you look at cell DB18 he comes up again there.

Thanks for your help in advance.



Kind regard

 

[Nebu]

Hi;

Find the attached. the file you had attached in your OP had month in the column values as a prefix. In your new file there is no prefix for the months, hence it was not giving the desired results. I have made the formula dynamic to pick the months which are more than 99. Let me know with questions if any.

Thanks

 

[rajkenya1]

Thank you once again Genius

Well done.

Much appreciated

 

[rajkenya1]

Sorry to Trouble you Nebu but one last question. What formula can i use in cell E18 onwards to tell me which year the client starts in. Have manually inserted as an example.

Is it a combination of vlookup and if statement

Thanks in advance

 

[Nebu]

Hi:

Please find the attached.

Thanks

 

[rajkenya1]

Thanks once again Nebu

 

[rajkenya1]

Hi Nebu

Sorry mate, just need your assistance again. In the attached spreadsheet in cell E39 i wanted a formula which will look at the range from G18 to DC35 and return how many clients are there in Year 1 and so forth

Have attached a sample.

Are you able to assist please

 

[Nebu]

Hi:

Use the following formula

=COUNTIF($E$18:$E$35,F$38)

Thanks

 

[rajkenya1]

Thanks Genuis