Fixed Reference for a table like $ in normal excel

**kenny.fsw** · 05-03-2012, 09:48 AM

Hello,

I need to copy a formula that has references to a table, how do I fix the table column.

Here is my formula, I have put the $ in the standard parts, but whats the table method?

=IF(SUMIFS(Table_Above_Max_by_Branch[Above Max],Table_Above_Max_by_Branch[Depot],Levels!O$2,Table_Above_Max_by_Branch[S-Code],Levels!$D14)>$K14-SUM($M14:N14),$K14-SUM($M14:N14),SUMIFS(Table_Above_Max_by_Branch[Above Max],Table_Above_Max_by_Branch[Depot],Levels!O$2,Table_Above_Max_by_Branch[S-Code],Levels!$D14))

Any help would be greatly appreciated!

**TMS** · 05-03-2012, 09:59 AM

I would have thought it was already "fixed", in that it refers to column headings within the table. I must admit, I tend to use traditional references so I know what I'm dealing with. sign of old age, I guess.

Regards, TMS

**NBVC** · 05-03-2012, 10:04 AM

See this section: Moving, copying, and filling structured references here.

**kenny.fsw** · 05-03-2012, 10:04 AM

sorted it, thanx

**JosephP** · 05-03-2012, 10:05 AM

also isn't that just
=MIN(SUMIFS(Table_Above_Max_by_Branch[Above Max],Table_Above_Max_by_Branch[Depot],Levels!O$2,Table_Above_Max_by_Branch[S-Code],Levels!$D14),$K14-SUM($M14:N14))

**NBVC** · 05-03-2012, 10:05 AM

Please identify for the benefit of others.. how you sorted it.

**kenny.fsw** · 05-04-2012, 03:43 AM

I was dragging/autofilling the cells which made a relative referance instead of copy and paste which makes an absoloute reference.

I didnt think that would make a diference, never encountered the problem before.

And thank you Joseph - thats a much easier way, Im just addicted to if statements lol.