John
I don't see any obvous problem with your formula. To troubleshoot those kinds
of problems in messy formulas they can be calculated in parts. A lot of people
don't know this, but you can highlight on the formula bar, only one of your
nested functions or a rangename, then hit F9 and it will calculate only the
highlighted part of your formula. For example, higlight
match(d9,employeenames, 0), then hit F9 and see if you get a result or an
error. Use that method to systematically rule out each part of your formula.
Once you find out which embedded function is causing the error the problem will
be obvious to you, maybe.
Good luck.

Ken
Norfolk, Va

Thanks Ken, I wasn't aware of the F9 function, I received a fix from Frank
but am getting the same error again on Address4i.e. #Ref!

The fix was to place the following code within the Named Range on Address 1

=OFFSET(Database!$H$2,0,0,SUMPRODUCT(MAX((Database !$H1:$H2000<"")*(ROW(Data
base!$H1:$H2000)))),1)

I substituted Column numbers for the Range Name for Address4 as below, but
the #Ref! appears despite it being the same logic as above - which I'm not
quite sure what the above actuallu does. It appears to set the range down as
far as the last populated cell + 1. Perhaps it should relate the length of
the range name area to the Employee Name, as if there is no Employee Name,
there can't be any address either, but not sure how do do that

=OFFSET(Database!$K$2,0,0,SUMPRODUCT(MAX((Database !$K1:$K2000<"")*(ROW(Data
base!$K1:$K2000)))),1)

So I'm lost


"KRCowen" wrote in message
...
John
I don't see any obvous problem with your formula. To troubleshoot those
kinds
of problems in messy formulas they can be calculated in parts. A lot of
people
don't know this, but you can highlight on the formula bar, only one of
your
nested functions or a rangename, then hit F9 and it will calculate only
the
highlighted part of your formula. For example, higlight
match(d9,employeenames, 0), then hit F9 and see if you get a result or an
error. Use that method to systematically rule out each part of your
formula.
Once you find out which embedded function is causing the error the problem
will
be obvious to you, maybe.
Good luck.

Ken
Norfolk, Va