Hi

I have the following problem.

I am using hlookup with the row function to circumvent hlookup failing when I insert new rows into the range. It does not appear to always work though - it does on some tables and not on others, even after triple checking all my formulae

For example if I want to return the value in row 6 even if I insert a new row in at e5, so that row 6 becomes row 7, am I right in thinking the following should always acheive this?

=HLOOKUP(F$1,Sheet1!$1:$1048576,(ROW(Sheet1!E6)))

I cannot use dynamic named ranges as this causes conflicts and errors with other worksheets and versions of excel

Many thanks