• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data Tables - How to fix a column?

H

Henrique Carvalho

New Member
Hi Guys!


Is there something like F4 for data tables?


I can´t fix (lock) a certain column (witch contains the months of the data) using data tables. So, when i try to drag the formula to other cells, the reference that i wanted to be fixed moves.


Any help?


Thanks in advance!
 
Wow.. Hui.. copy paste works.. I have spent several hours scratching my head about this stupid bug with data tables. Thank you ...
 
Hi Hui!


I´ve already tried Ctrl C & Ctrl V but it dosen´t work.


See the spreadsheet below to understand why:


http://www.megaupload.com/?d=15GKYWK0


Thanks!
 
@Henrique...


In your case, you may need to use OFFSET formula, like this:


=INDEX(Table1[Month],MATCH(C6,OFFSET($C$9,0,COLUMNS($C$3:C3)-1,COUNTA(Table1[Month]),1),0))


instead of =INDEX(Table1[Month],MATCH(C6,Table1[Asset 1],0),0).


use ctrl+c, ctrl+v to paste the formula in both first / last rows.
 
I was also going to suggest that if you only had a few columns just Edit "F2" each equation and drag the boxes, But Dragging doesn't work with the data table ranges?
 
yeah.. strange, but dragging doesnt work well. Also, another quirk with data tables is that you cannot use, for eg. table1[month] as input list in data validation. I create a named range lstMonths and point it to table1[month] and then use the lstMonths as source data list in data validation...
 
@Chandoo, You can make a named range with =Table1[Month] and then use the named range in your validation.
 
Ha, and here I am desperately googling how to change the column references to a data table. Turns out, I never use the mouse drag to copy formulas - I only use ctrl+c, ctrl+v so I never saw that the references changed. Good to know!
 
Hi trying to fix Qty Delivereed & week column and below formula, E3 should change to F3, G3 and so on on dragging... can fix cell ranges using $ sign but dont know how to fix table columns, can any one help?


=SUMIFS(Table3[Qty Delivered],Table3[Customer],$B$1,Table3[Week],E3)


Thank you in advance
 
Hi Malhotra ,


I assume you wish to make the table column reference an absolute one ; check this link :


http://answers.microsoft.com/en-us/office/forum/office_2010-excel/absolute-reference-to-table-column-in-excel-2010/51c910d5-5140-4288-8824-87ceb462813c


Narayan
 
You must log in or register to reply here.
Back
Top