• Visitors can check out the Forum FAQ by clicking this link. You have to register before you can post: click the REGISTER link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. View our Forum Privacy Policy.
  • Want to receive the latest contracting news and advice straight to your inbox? Sign up to the ContractorUK newsletter here. Every sign up will also be entered into a draw to WIN £100 Amazon vouchers!

Annoying Excel problem

Collapse
X
  •  
  • Filter
  • Time
  • Show
Clear All
new posts

    Annoying Excel problem

    I have a spreadsheet I'm putting together that calulates values for a row of cells based on values of other cells using a formula.

    I want to use the autofill feature in excell 2007 to fill in the values for this row. However when I try and do this is doesnt increment the numberical value in the formula only the letter.

    e.g.

    =IF(Organisation!F5="Not Applicable","N/A",(Organisation!F5)) is the formula in the first cell of the row.

    If I use the series fill feature to drag this accross the rest of the cells it does this :

    =IF(Organisation!F5="Not Applicable","N/A",(Organisation!F5))
    =IF(Organisation!G5="Not Applicable","N/A",(Organisation!G5))
    =IF(Organisation!H5="Not Applicable","N/A",(Organisation!H5))
    =IF(Organisation!I5="Not Applicable","N/A",(Organisation!I5)) etc. when what I want is:

    =IF(Organisation!F5="Not Applicable","N/A",(Organisation!F5))
    =IF(Organisation!F6="Not Applicable","N/A",(Organisation!F6))
    =IF(Organisation!F7="Not Applicable","N/A",(Organisation!F7))
    =IF(Organisation!F8="Not Applicable","N/A",(Organisation!F8)) etc.

    I've played with all the series fill options but can't get it to behave the way I want?

    Anyone know how to do it, before I resort to editing it all by hand?
    "Being nice costs nothing and sometimes gets you extra bacon" - Pondlife.

    #2
    use the dollar($) to specify an absolute reference on the column:

    =IF(Organisation!$F5="Not Applicable","N/A",(Organisation!$F5))
    Coffee's for closers

    Comment


      #3
      Originally posted by Spacecadet View Post
      use the dollar($) to specify an absolute reference on the column:

      =IF(Organisation!$F5="Not Applicable","N/A",(Organisation!$F5))
      Nope, That preserves the F5 across all the cells. Just behaves as if I had copied and pasted it.
      "Being nice costs nothing and sometimes gets you extra bacon" - Pondlife.

      Comment


        #4
        Originally posted by DaveB View Post
        Nope, That preserves the F5 across all the cells. Just behaves as if I had copied and pasted it.
        Numbers only increment if you drag down.

        Comment


          #5
          Originally posted by craig1 View Post
          Numbers only increment if you drag down.
          Arse.

          Oh well, at least I can set it up in column form and transpose it to where I actually want it.

          Thanks guys.
          "Being nice costs nothing and sometimes gets you extra bacon" - Pondlife.

          Comment


            #6
            Tried this with openoffice and it increments correctly.
            It complains about a 508 error (unmatched bracket, etc.)
            Have you tried replacing the formula with something simpler?

            Comment

            Working...
            X