• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

R

rajkenya1

Member
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.
 

Attachments

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
 

Attachments

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
 

Attachments

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
 

Attachments

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 :)
 

Attachments

Last edited:
You must log in or register to reply here.
Back
Top