Excel messes with my formula when copied, how can

问题:

So I'm working with an old database system that is being updated, previously it ran across many different files, one with emails, one with numbers, you get the idea.

Anyway, in trying to compile in to one file, I've hit a snag, some people don't have a number or an email etc, this means I can't just copy the old data over (PersonID 5's phone would be in PersonID 4's space)

So what I did was use a Vlookup Formula, had some troubles with it and this morning I realised what the problem was.

=VLOOKUP(A2,'PhoneType - Email'!B1:V29190,3,FALSE)

I'll break this up quick, A is the column with the PersonID in, email is the worksheet the emails are in, B1:V29190 should be selecting the whole table and 3 is the column that PersonID is in in that worksheet.

Basically, I need A to change (A2, A3, A4) in line with the row it's in BUT I need B1:V29190 to stay constant as currently Excel tries to be clever and progresses it with each copy paste.

How can I do this?

Many thanks,

icey

回答1:

The names are Absolute and Relative cell references. The $ turns either a row or column absolute so they do not change when filled or copied to a new location. You were filling down rows so you only needed to 'lock' the row numbers as absolute. If you put the cursor into the cell address and hit F4, you will see the first of the combinations. Keep tapping F4 to see other combinations.

=VLOOKUP(A2,'PhoneType - Email'!B$1:V$29190,3,FALSE)