Copying Formulas that use the Table Construct

[kchiba]

When copying Fomulas across columns that refer to the Table construct is it possible to make cetain columns absolute.


Here is an example


The original formula is : '=-SUMIFS(T_OpexBay[Nov],T_OpexBay[LOCAL_COST_ELEM_CODE],$A2,T_OpexBay[FASTER_BASE_LINE_ITEM],$AA$1)


When I copy it to the next column i get


=-SUMIFS(T_OpexBay[Dec],T_OpexBay[LOCAL_COST_ELEM_DESCR],$A2,T_OpexBay[OPEX RULE],$AA$1)


The only change I wanted was the Nov to Dec and this is correct, but the other references have also changed by one column.


I have copied the formula across, then selected all the columns, and then Edited the first formula with Ctrl Enter, this keeps everything the same, then I have to go and change Nov to Dec.


Any suggestions

 

[keymaster]

Kanti... good question.


I am not aware of any way to keep some parts of table refs. absolute and some relative. But here is what I usually do in situations like these.


write the month names (or table column names) in a row above (say between A1:F1)


Now, write =-SUMIFS(indirect("T_OpexBay[&A$1&]"),T_OpexBay[LOCAL_COST_ELEM_CODE],$A2,T_OpexBay[FASTER_BASE_LINE_ITEM],$AA$1)


then copy the formula by pressing CTRL+C

Now paste it in next columns. This keeps all other table refs intact.


Let us know if you come across a better technique.

 

[kchiba]

Hi Chandoo,


Thanks for the reply, I did eventually end up doing exactly that.


But I was wondering if this could be logged with Microsoft as a potential flaw for future improvement, as it seems to go against the very first functionality in spreadsheets, which was the absolute and relative referencing.

 

[NARAYANK991]

Hi ,

Even though this is a very old thread , I thought , in the interest of making this a complete forum , why not include the recommended solution here ? So , check this link :

http://www.excelcampus.com/tips/absolute-formula-references-excel-structured-table/

Narayan