Pre-fixing Numbered Rows With Cell Information

    I have a title row containing the name of a test that I am running. Each row under that will be numbered for different test senarios. I would like to have each row contain the test name and then a consecutive number such as:


    Test name: Ball


    Ball - 1
    Ball - 2
    Ball - 3


    Is there a way to have excel pull the name of the test from a cell and put a consecutive number with it down the rows as I go automatically?


    Thanks,
    Brian

    Re: Pre-fixing Numbered Rows With Cell Information


    Hi Brian,


    Hopefully the following formula should do the trick (it assumes the heading is in cell A1, simply change if this is not the case):


    =$A$1&" - "&ROW()


    Cheers,


    Robert
    : D

    Re: Pre-fixing Numbered Rows With Cell Information


    Thanks,
    I know I used the word row, but what if I just want it to start at number one and then add them consecutively. So I would have


    =$A$1" - " but instead of &ROW() i just want it to start at one and continue to add a number each row I move down to.


    Brian

    Re: Pre-fixing Numbered Rows With Cell Information


    Hi Brian,


    Here's a couple of possible solutions:


    1 - Subtract the number of rows above where the formula starts i.e. if the formula starts at row 2 use $A$1&" - "&ROW()-1, and copy this down as many times as required, or
    2 - Enter this formula =$A$1&" - "&1 into the first desired cell (I used A2), and then this formula =$A$1&" - "&MID(A2,8,5)+1 into the immediate cell beneath (A3 in my case) and copy it down as many times as needed.


    Hope this helps.


    Cheers,


    Robert
    : D

    Re: Pre-fixing Numbered Rows With Cell Information


    brianNC


    Looks like you are doing a science fair project, hope you do well, take First Place, finish high in regionals, and get a scholarship! For the record, more PSI will make the ball bounce higher.


    I'm probaly wasting everyone's time as the suggestions above are viable, and more elegant than my techinque...


    I would use a hidden column, or a column on another sheet. Put numbers in the column, and ref to there. this will get the number. It doesn't even have to be a hidden sheet as you can set the print area before you print your data tables


    if your hidden column is "X" you could put this formula in row 2 and drag it down.


    And instead of typing the X2 in the formula bar you could just click on it.


    =$A$1&" "&X2

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login