Thanks Jim. Any other ideas as to what might be causing this? I've
replicated this several times, and just again now. Copying and pasting to
the left or the right it does not update the reference, whether in manual or
automatic calculation. The worksheet is unprotected. Still seems like it
must be a bug unless I have some funky setting set . . .

I'm working off someone else's code and am gradually replacing the VLookups
for efficiency, as you mention ... I think the Index and Match idea is a
good one for this sheet, thank you. (For some others I've been using Match
and Offset). As we use this same spreadsheet every year with different
amounts of data, referencing the exact # of rows where data is present has
caused problems.

Deb
"Jim Rech" wrote:

I entered your formula in Excel 2007 and did a regular copy and paste one
column to the right. The E$3 became F$3 as it should.

Incidentally your formula could be written to compute more efficiently,
particularly if you are going to copy it many times. The problem is that
your formula creates a dependency on 47 columns when all it really depends
on need is 2 (column E and column AM, the 35th):

=INDEX($AM$3:$AM$65536,MATCH(E$42,IData!$E$3:$E655 36,FALSE))

If you do not have data going to row 65536 you might pick up efficiency by
referencing the rows that will actually have data.

--
Jim
"Deb Plosk77" wrote in message
...
Hi,

In copying this formula:

=VLOOKUP(E$42,IData!$E$3:$AY$65536,35,FALSE)

from one column to another, regular copy and paste, it treats e$42 as if
it
is $e$42. If I copy and paste special, formulas and number formats, it
will
update the column reference correctly.

Does anyone know if this is a known bug? Do you have this problem as
well?
Not sure where to post possible problems so thought I'd try here as well
as
mailing MS directly.

Thank you,
Deb