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

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

Click Add

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

Click Format and choose Yellow from Pattern Tab

Ok

Ok

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

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

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)

Yes, try this

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

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,""))

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