can tthis countdown be done
Results 1 to 10 of 10

Thread: can tthis countdown be done
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Sep 2009
    Posts
    249
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default can tthis countdown be done

    hello,
    trying to count down from a number in a cell, C3 for exmaple, this will contain a random number.
    i want excel to see this number then populate the rest of the cells c4 c5 c6 etc with the number decreasing.
    so c3 will be 25 c4 will be 24 c5 will be 23 until it gets to 1
    can this be done ?

    regards

    acehole

  2. #2
    Board Regular dispelthemyth's Avatar
    Join Date
    Mar 2006
    Location
    England
    Posts
    635
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: can tthis countdown be done

    Sheet1

     ABCD
    1    
    2    
    3  25 
    4  24 
    5  23 
    6  22 
    7  21 
    8  20 
    9  19 
    10  18 
    11  17 
    12  16 
    13  15 
    14  14 
    15  13 
    16  12 
    17  11 
    18  10 
    19  9 
    20  8 
    21  7 
    22  6 
    23  5 
    24  4 
    25  3 
    26  2 
    27  1 
    28    

    Spreadsheet Formulas
    CellFormula
    C4=IF(OR(C3=1,C3=""),"",C3-1)
    C5=IF(OR(C4=1,C4=""),"",C4-1)
    C6=IF(OR(C5=1,C5=""),"",C5-1)
    C7=IF(OR(C6=1,C6=""),"",C6-1)
    C8=IF(OR(C7=1,C7=""),"",C7-1)
    C9=IF(OR(C8=1,C8=""),"",C8-1)
    C10=IF(OR(C9=1,C9=""),"",C9-1)
    C11=IF(OR(C10=1,C10=""),"",C10-1)
    C12=IF(OR(C11=1,C11=""),"",C11-1)
    C13=IF(OR(C12=1,C12=""),"",C12-1)
    C14=IF(OR(C13=1,C13=""),"",C13-1)
    C15=IF(OR(C14=1,C14=""),"",C14-1)
    C16=IF(OR(C15=1,C15=""),"",C15-1)
    C17=IF(OR(C16=1,C16=""),"",C16-1)
    C18=IF(OR(C17=1,C17=""),"",C17-1)
    C19=IF(OR(C18=1,C18=""),"",C18-1)
    C20=IF(OR(C19=1,C19=""),"",C19-1)
    C21=IF(OR(C20=1,C20=""),"",C20-1)
    C22=IF(OR(C21=1,C21=""),"",C21-1)
    C23=IF(OR(C22=1,C22=""),"",C22-1)
    C24=IF(OR(C23=1,C23=""),"",C23-1)
    C25=IF(OR(C24=1,C24=""),"",C24-1)
    C26=IF(OR(C25=1,C25=""),"",C25-1)
    C27=IF(OR(C26=1,C26=""),"",C26-1)
    C28=IF(OR(C27=1,C27=""),"",C27-1)


    Excel tables to the web >> Excel Jeanie HTML 4
    Financial Modelling Consultant
    Excel 2016 32bit and 2016 64bit + Windows 10

    I've always listening so i'm always learning
    Before posting your question, reread it to make sure you understand it.
    If possible post example data to make it easier to help you

  3. #3
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    14,811
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    11 Thread(s)

    Default Re: can tthis countdown be done

    One way:-
    Code:
    Sub MG20Mar52
    Dim Tem As Integer
    With Range("C3")
        Tem = .value
        .value = 1
        .AutoFill Destination:=Range("C3").Resize(Tem), Type:=xlFillSeries
        .Resize(Tem).Sort Range("C3"), xlDescending
    End With
    End Sub
    Regards Mick

  4. #4
    Board Regular
    Join Date
    Sep 2009
    Posts
    249
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: can tthis countdown be done

    Wow,
    that works a treat as I suspected the forum would come up with the goods,
    final question if possible
    i have the numbers now counting down in cell c3 onwards
    the cells a3 and b3 have information I would like to copy down to the last number in cell c
    so it would look like this
    a3. B3. C3


    1234. 5. 25
    1233. 5. 24
    1234. 5. 23


    cells a and b will not increase or decrease in value just need to copy down


    thanks for the help
    Acehole

  5. #5
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    14,811
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    11 Thread(s)

    Default Re: can tthis countdown be done

    If you're referring to my code then try this:-

    Code:
    Sub MG20Mar43
    Dim Tem As Integer
    With Range("C3")
        Tem = .value
        .value = 1
        .AutoFill Destination:=Range("C3").Resize(Tem), Type:=xlFillSeries
        .Resize(Tem).Sort Range("C3"), xlDescending
        Range("A3:B3").Resize(Tem).value = .Offset(, -2).Resize(, 2).value
    End With
    End Sub
    Regards Mick

  6. #6
    Board Regular
    Join Date
    Sep 2009
    Posts
    249
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: can tthis countdown be done

    Hi mick thank you for the replies
    i am trying your formula now but can't get it to work, being a novice I guess I am putting the code in the wrong place?
    any suggestions
    thanks
    acehole

  7. #7
    Board Regular
    Join Date
    Sep 2009
    Posts
    249
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: can tthis countdown be done

    hi Mick,
    when your code runs i get this error message come up on this line

    .AutoFill Destination:=Range("C3").Resize(Tem), Type:=xlFillSeries

    looks like it fills the numbers down but not the columns a and b and their infomation

    regards
    acehole

  8. #8
    Board Regular dispelthemyth's Avatar
    Join Date
    Mar 2006
    Location
    England
    Posts
    635
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: can tthis countdown be done

    put your values in Row 3 and use the below


    Sheet1

     ABC
    2   
    31234525
    41234524
    51234523
    61234522
    71234521
    81234520
    91234519
    101234518

    Spreadsheet Formulas
    CellFormula
    A4=IF(OR(B4="",A$3=""), "", A$3)
    B4=IF(OR(C4="",B$3=""), "", B$3)
    C4=IF(OR(C3=1,C3=""),"",C3-1)
    A5=IF(OR(B5="",A$3=""), "", A$3)
    B5=IF(OR(C5="",B$3=""), "", B$3)
    C5=IF(OR(C4=1,C4=""),"",C4-1)
    A6=IF(OR(B6="",A$3=""), "", A$3)
    B6=IF(OR(C6="",B$3=""), "", B$3)
    C6=IF(OR(C5=1,C5=""),"",C5-1)
    A7=IF(OR(B7="",A$3=""), "", A$3)
    B7=IF(OR(C7="",B$3=""), "", B$3)
    C7=IF(OR(C6=1,C6=""),"",C6-1)
    A8=IF(OR(B8="",A$3=""), "", A$3)
    B8=IF(OR(C8="",B$3=""), "", B$3)
    C8=IF(OR(C7=1,C7=""),"",C7-1)
    A9=IF(OR(B9="",A$3=""), "", A$3)
    B9=IF(OR(C9="",B$3=""), "", B$3)
    C9=IF(OR(C8=1,C8=""),"",C8-1)
    A10=IF(OR(B10="",A$3=""), "", A$3)
    B10=IF(OR(C10="",B$3=""), "", B$3)
    C10=IF(OR(C9=1,C9=""),"",C9-1)


    Excel tables to the web >> Excel Jeanie HTML 4



    Then drag down as far as you want
    Financial Modelling Consultant
    Excel 2016 32bit and 2016 64bit + Windows 10

    I've always listening so i'm always learning
    Before posting your question, reread it to make sure you understand it.
    If possible post example data to make it easier to help you

  9. #9
    Board Regular
    Join Date
    Sep 2009
    Posts
    249
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: can tthis countdown be done

    perfect, thank you very much for the help
    regards
    acehole

  10. #10
    Board Regular dispelthemyth's Avatar
    Join Date
    Mar 2006
    Location
    England
    Posts
    635
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: can tthis countdown be done

    Apologies, use the below, its a slight amendment, i didnt lock the check if cell is blank to column C


    Sheet1

     ABC
    1   
    2   
    31234525
    41234524
    51234523
    61234522
    71234521
    81234520

    Spreadsheet Formulas
    CellFormula
    A4=IF(OR($C4="",A$3=""), "", A$3)
    B4=IF(OR($C4="",B$3=""), "", B$3)
    C4=IF(OR(C3=1,C3=""),"",C3-1)
    A5=IF(OR($C5="",A$3=""), "", A$3)
    B5=IF(OR($C5="",B$3=""), "", B$3)
    C5=IF(OR(C4=1,C4=""),"",C4-1)
    A6=IF(OR($C6="",A$3=""), "", A$3)
    B6=IF(OR($C6="",B$3=""), "", B$3)
    C6=IF(OR(C5=1,C5=""),"",C5-1)
    A7=IF(OR($C7="",A$3=""), "", A$3)
    B7=IF(OR($C7="",B$3=""), "", B$3)
    C7=IF(OR(C6=1,C6=""),"",C6-1)
    A8=IF(OR($C8="",A$3=""), "", A$3)
    B8=IF(OR($C8="",B$3=""), "", B$3)
    C8=IF(OR(C7=1,C7=""),"",C7-1)


    Excel tables to the web >> Excel Jeanie HTML 4
    Financial Modelling Consultant
    Excel 2016 32bit and 2016 64bit + Windows 10

    I've always listening so i'm always learning
    Before posting your question, reread it to make sure you understand it.
    If possible post example data to make it easier to help you

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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