How to lock a cell in a table

[tas1337]

I am trying to copy the formula:
=SUMIF(Table3[1],TRUE,Table3[Cab])

but as i copy through other cells i need to keep "Table3[1]" the same value vs where i need my Table3[Cab] to change to the next table cell. ive looked around as to how you can lock it(like $a$1) but i haven't come across an easy way.
Can anyone please help me out?

Thanks,

 

[Brettster]

Hello tas1337,
You are correct, there is no easy way of doing this.
There are two options that I am aware of:

1. Do the coding manually (probably easiest option if you only have a small number of columns to populate).
2. Create the formula as a string (which means you can copy it across (and the criteria range will remain static, whilst the sum range will move):
i) To do this copy formula below across.

="&=SUMIF(Table3[1],TRUE," &Table1[Cab]]&"])"
ii) Copy and paste values
iii) Replace the "&" with "" (to change the formula string to a formula proper)

You could also potentially extend the table column to be longer than the actual table. This will make the range no longer a table range, but able to be locked. However, this is probably not appropriate if the table is likely to grow in size (as the range would not expand with the table (unless items were being actually inserted rather than appended to the table).

I hope this is useful.
Regards,
Brettster