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.

Look in HELP for the INDIRECT() function.
Second question: ROW()

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"HenryB" wrote in message ...
|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.
|