+ Reply to Thread
Results 1 to 16 of 16

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

  1. #1
    Registered User
    Join Date
    09-21-2009
    Location
    mexico
    MS-Off Ver
    Excel 2007
    Posts
    20

    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
    Last edited by NBVC; 10-11-2009 at 07:42 PM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    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

    Click Add

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

    Click Format and choose Yellow from Pattern Tab

    Ok

    Ok
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    09-21-2009
    Location
    mexico
    MS-Off Ver
    Excel 2007
    Posts
    20

    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

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

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

  5. #5
    Registered User
    Join Date
    09-21-2009
    Location
    mexico
    MS-Off Ver
    Excel 2007
    Posts
    20

    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

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

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

  7. #7
    Registered User
    Join Date
    09-21-2009
    Location
    mexico
    MS-Off Ver
    Excel 2007
    Posts
    20

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

    Thank you for your reply.
    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

  8. #8
    Registered User
    Join Date
    09-21-2009
    Location
    mexico
    MS-Off Ver
    Excel 2007
    Posts
    20

    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

  9. #9
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

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

    Quote Originally Posted by joerob88 View Post
    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)

  10. #10
    Registered User
    Join Date
    09-21-2009
    Location
    mexico
    MS-Off Ver
    Excel 2007
    Posts
    20

    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

  11. #11
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

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

  12. #12
    Registered User
    Join Date
    09-21-2009
    Location
    mexico
    MS-Off Ver
    Excel 2007
    Posts
    20

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

    Thank you, adapted your formula to the following:
    =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

  13. #13
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

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

  14. #14
    Registered User
    Join Date
    09-21-2009
    Location
    mexico
    MS-Off Ver
    Excel 2007
    Posts
    20

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

  15. #15
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    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

  16. #16
    Registered User
    Join Date
    09-21-2009
    Location
    mexico
    MS-Off Ver
    Excel 2007
    Posts
    20

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1