# Specific cells populate with specific numbers when a value within a range is entered.

1. ## Specific cells populate with specific numbers when a value within a range is entered.

Here is what i am trying to achieve. If the date 2/20/2010 is located at F53 & the cell next to it at H53 is populated with a number between 1 & 16, then i want the cell at J11 (42 rows further up) to auto populate with the number 1. When this occurs the cells beneath this, from J12 to J52 should also auto populate with the with consecutive numbers from 2 to 42. Would also like to see the cells with numbers 1 to 28, automatically format to orange & the cells containing numbers 29 to 42 automatically format to yellow. I plan to have this condition repeat several times later in the year, at dates that are to be decided. When these dates are decided i want to be able to enter a number from 1 to 16 & next to the date & all of the above automatically occurs.
Any assistance would be appreciated.
Thank you
JR  Register To Reply

2. ## Re: Specific cells populate with specific numbers when a value within a range is ente

in J11,

=IF(AND(F53=DATE(2010,2,20),H53>=1,H53<=16),1,"")

in J12,

=(IF(J11="","",J11+1) copied down..

Then Select J11:53 and go to Format|Conditional Formatting.

Select Cell Value Is >> Less Than or Equal to >> =28

click Format and choose Orange from Pattern tab..

Click Ok

Cell Value Is >> Less than or Equal to>> =42

Click Format and choose Yellow from Pattern Tab

Ok

Ok  Register To Reply

3. ## Re: Specific cells populate with specific numbers when a value within a range is ente

That helps a lot! However when i tried to re-use the formula on different dates i had to change the date. Is there anyway to make the formula more flexible by saying that if H53 contains a number between 1 & 16 then the cell 42 rows above it & two columns to the right (in this case J11) should auto populate with the number 1?

Thank you!
JR  Register To Reply

4. ## Re: Specific cells populate with specific numbers when a value within a range is ente

Do you mean, in J11

=IF(AND(F53=DATE(2010,2,20),OFFSET(J11,42,-2,1,1)>=1,OFFSET(J11,42,-2,1,1)<=16),1,"")

This will look down 42 rows and left 2 columns to see if the cell contains a number between 1 and 16...  Register To Reply

5. ## Re: Specific cells populate with specific numbers when a value within a range is ente

Thank you, but not exactly what i need. It would be more flexible if the formula could say - if the any cell in column H, contains a value between 1 & 16, then auto populate the cell 42 rows above it & 2columns to the right with the number 1. Can this be done?

Thank you again for you assistance!

JR  Register To Reply

6. ## Re: Specific cells populate with specific numbers when a value within a range is ente

Unless you fill up a bunch of cells that are 42 rows above and 2 columns to the right of each corresponding H cell with my above formula, I think you will need the assistance of a VBA assistant.....  Register To Reply

7. ## Re: Specific cells populate with specific numbers when a value within a range is ente

What is a VBA assistant & what do they do?
Also how would i get one of these VBA assisatants to do this for me?
Thank you,

JR  Register To Reply

8. ## Re: Specific cells populate with specific numbers when a value within a range is ente

Was looking at my problem & your formula below:
=IF(AND(F53=DATE(2010,2,20),OFFSET(J11,42,-2,1,1)>=1,OFFSET(J11,42,-2,1,1)<=16),1,"")
What if we looked at this differently. Can we say look down to the cell 42 rows down & 2 columns to the left, if this cell contains a value between 1 & 16, then populate the cell containing your formula with a 1. Can this be done?

Thank you,

JR  Register To Reply

9. ## Re: Specific cells populate with specific numbers when a value within a range is ente Originally Posted by joerob88 Was looking at my problem & your formula below:
=IF(AND(F53=DATE(2010,2,20),OFFSET(J11,42,-2,1,1)>=1,OFFSET(J11,42,-2,1,1)<=16),1,"")
What if we looked at this differently. Can we say look down to the cell 42 rows down & 2 columns to the left, if this cell contains a value between 1 & 16, then populate the cell containing your formula with a 1. Can this be done?

Thank you,

JR
That is exactly what my formula does. If you put it in J11, it will look down 42 columns and 2 columns left to see if your criteria is met there. Then it will put a 1 or leave it blank.

By the way, by VBA assistant I meant a person in the forum who can program in Visual Basic for Applications (i.e create a macro for you)  Register To Reply

10. ## Re: Specific cells populate with specific numbers when a value within a range is ente

Thank you.
Can we remove the need to look at the date in F53 & instead look for a numerical value between 1 & 16,in h53.

Thank you,

JR  Register To Reply

11. ## Re: Specific cells populate with specific numbers when a value within a range is ente

Yes, try this

=IF(AND(OFFSET(J11,42,-2,1,1)>=1,OFFSET(J11,42,-2,1,1)<=16),1,"")  Register To Reply

12. ## Re: Specific cells populate with specific numbers when a value within a range is ente

=IF(AND(H53>=1,OFFSET(J11,42,-2,1,1)>=1,OFFSET(J11,42,-2,1,1)<=16),1,"")
This seems to solve the problem. The second part, is that when the number one appears in any cell in the J column, i need to have the numbers 1 to 42 to automatically populate the in the cell to the right & below. In this case K11 to K53?

Thank you,

JR  Register To Reply

13. ## Re: Specific cells populate with specific numbers when a value within a range is ente

I don't have access to Excel at the moment, so can't test...

Try

=IF(ISNUMBER(J10),J10+1,IF(AND(H53>=1,OFFSET(J11,42,-2,1,1)>=1,OFFSET(J11,42,-2,1,1)<=16),1,""))  Register To Reply

14. ## Re: Specific cells populate with specific numbers when a value within a range is ente

Thank you, that almost worked. Looking at this again i think what i need to say is the following. Any time the number 1 appears in the J column, automatically populate the cell to the right of the cell containing the number 1 with a 1 & populate the cells below it with the the numbers 2 to 42.  Register To Reply

15. ## Re: Specific cells populate with specific numbers when a value within a range is ente

I can't open your workbook right now..

but I think this should work.

If in J11 you go back to original formula:

=IF(AND(H53>=1,OFFSET(J11,42,-2,1,1)>=1,OFFSET(J11,42,-2,1,1)<=16),1,"")

Then in K11

=IF(J11=1,1,IF(and(Isnumber(K10),K10<42),K10+1,""))

these formulas can be copied up and down the columns  Register To Reply

16. ## Re: Specific cells populate with specific numbers when a value within a range is ente

Bingo!!!! Thats great i think you have just automated my spread sheet for me.

Thank you, very much!!!!

JR  Register To Reply