I can insert a formula like this into lookup and other functions and they
work fine but when trying to insert into a reference, it will allow
$B:C(row()-1) but excel does not recognise it and only thinks there is a
single column in the reference.
There must be a way to do it.
Also, related question, If the value of a variable is the same as the row
number, how can I write a formula that replaces this variable with a row
function.