Tags: absolute, across, calculated, column, columns, continues, copy, create, excel, formula, microsoft, msdn, reference, references, software, subsequent, table

Absolute references in Calculated Table columns

On Microsoft » Microsoft Excel

3,989 words with 5 Comments; publish: Thu, 22 May 2008 06:16:00 GMT; (30660.55, « »)

I need to create an absolute reference in a calculated column so that when I

copy the formula across to subsequent columns the formula continues to refer

to the equivalent of $D4. How is this done using table columns? When I copy

now to the next column it applies a relative reference.

All Comments

Leave a comment...

  • 5 Comments
    • Can you be more specific? $D4 will always refer to Column D, but the

      row will remain relative. $D$4 will always refer to D4.

      #1; Thu, 22 May 2008 06:17:00 GMT
    • $D4 is a combination of an absolute column address ($D) and a relative row

      address(4). If you want to reference D4 always, use $D$4.

      Tyro

      "Humphrey" <Humphrey.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      news:84BD54FB-2335-4FC2-BBC7-C235ABDC2A06.excel.todaysummary.com.microsoft.com...

      >I need to create an absolute reference in a calculated column so that when

      >I

      > copy the formula across to subsequent columns the formula continues to

      > refer

      > to the equivalent of $D4. How is this done using table columns? When I

      > copy

      > now to the next column it applies a relative reference.

      #2; Thu, 22 May 2008 06:18:00 GMT
    • What I was after was an absolute reference using the table nomenclature ie,

      Table_CBCC_Data[[#This Row],[dateBudget]]. Is there a table formula

      equivalent in 2007 to $D4, something like Table_CBCC_Data[[#This

      Row],$[dateBudget]].

      "Humphrey" wrote:

      > I need to create an absolute reference in a calculated column so that when I

      > copy the formula across to subsequent columns the formula continues to refer

      > to the equivalent of $D4. How is this done using table columns? When I copy

      > now to the next column it applies a relative reference.

      #3; Thu, 22 May 2008 06:19:00 GMT
    • On 14 jan, 22:19, Humphrey <Humph....excel.todaysummary.com.discussions.microsoft.com> wrote:

      > What I was after was an absolute reference using the table nomenclature ie=,

      > Table_CBCC_Data[[#This Row],[dateBudget]]. =A0Is there a table formula

      > equivalent in 2007 to $D4, something like Table_CBCC_Data[[#This

      > Row],$[dateBudget]]. =A0

      >

      > "Humphrey" wrote:

      > > I need to create an absolute reference in a calculated column so that wh=en I

      > > copy the formula across to subsequent columns the formula continues to r=efer

      > > to the equivalent of $D4. =A0How is this done using table columns? =A0Wh=en I copy

      > > now to the next column it applies a relative reference.- Tekst uit oorsp=ronkelijk bericht niet weergeven -

      > - Tekst uit oorspronkelijk bericht weergeven -

      Humprey,

      I've got the same problem and no direct answers. The simple $ trick

      still works but is not what we want.

      Im using 12 colomns with for the monthly turnover calculations

      (sumproducts) and some colomns with input for this calculations. The

      input colomns need an absolute reference in the sumproduct. Using the

      new tableformat i can't copy one month over all twelve. I have to

      manualy correct the formula or loose the new tabel functionality and

      use the old $ trick.

      For now I've a small issue (12 corrections is overseeable) but i have

      to do the same exercise over 52 weeks and next all working days later

      on....

      I hope adding the same problem in different words will help.

      #4; Thu, 22 May 2008 06:20:00 GMT
    • Humphrey,

      Just got the answer myself in a newer post. Use indirect to make it

      absolute

      so i.e Table_CBCC_Data[[#This Row],[dateBudget]] becomes

      INDIRECT("Table_CBCC_Data[[#This Row],[dateBudget]]")

      this will be absolute

      see also http://www.cpearson.com/excel/indirect.htm

      On 14 jan, 22:00, Humphrey <Humph....excel.todaysummary.com.discussions.microsoft.com> wrote:

      > I need to create an absolute reference in a calculated column so that when I

      > copy the formula across to subsequent columns the formula continues to refer

      > to the equivalent of $D4. How is this done using table columns? When I copy

      > now to the next column it applies a relative reference.

      #5; Thu, 22 May 2008 06:21:00 GMT