F2 Enter to make Formula Work

  • Thread starter Thread starter Buffyslay_co_uk
  • Start date Start date
B

Buffyslay_co_uk

Hi

anyone got any idea on this?

I have a vlookup, which is picking up data from another page in the
same workbook, I have to F2 and then Enter to make Formula Work. If i
make changes then i have to do this again.

This also happens when i copy/paste values (I am consolidating lots of
data all linked by unique id, and want to get it into a single usable
workbook - would rather use access, but am not allowed!)

Any ideas? Thought about doing the F2/Enter in vba, but that didnt
seem to work either!

P

http://www.buffyslay.co.uk
 
Buffyslay_co_uk said:
Hi

anyone got any idea on this?

I have a vlookup, which is picking up data from another page in the
same workbook, I have to F2 and then Enter to make Formula Work. If i
make changes then i have to do this again.

This also happens when i copy/paste values (I am consolidating lots of
data all linked by unique id, and want to get it into a single usable
workbook - would rather use access, but am not allowed!)

Any ideas? Thought about doing the F2/Enter in vba, but that didnt
seem to work either!
Click to expand...


It's probably because your data is getting in as text rather than numeric.
VLOOKUP will only work if both lookup value and data in which it's looking
are the same (i.e. both numeric or both text).

Imported data often appear as text. You can see the difference if you remove
all explicit alignment, as text will be left-aligned and true numbers will
be right-aligned. You could also use a formula (such as =ISTEXT(A1) ) to
test this.

If you have a text entry that consists only of numeric characters in a cell
formatted as General, when you edit it (F2 and Enter) it changes to numeric.
That's why your formula then works.
 
Imported data often appear as text. You can see the difference if you remove
all explicit alignment, as text will be left-aligned and true numbers will
be right-aligned. You could also use a formula (such as =ISTEXT(A1) ) to
test this.

If you have a text entry that consists only of numeric characters in a cell
formatted as General, when you edit it (F2 and Enter) it changes to numeric.
That's why your formula then works.
Click to expand...

still not sure why this isnt picking up - thanks for the helpful tip
on the formating, have looked and there were some differences, but its
still not picking them up...
 
Buffyslay_co_uk said:
still not sure why this isnt picking up - thanks for the helpful tip
on the formating, have looked and there were some differences, but its
still not picking them up...
Click to expand...

So, have you changed all to text, or all to numeric?

If text, you need to look out for almost invisible differences, such as a
trailing space. (For example, "qwerty" and "qwerty " are not the same, and
will not match.)

If numeric, are the values exactly the same? For example, if you have
numbers displayed to two decimal places, 1.239 will display as 1.24, but
this will not match with an exact value of 1.24 .

Alternatively, are you really sure you have converted all your data? Have
you tried a formula such as =ISTEXT(A1) on all the cells that you are trying
to match, since you 'converted' them?
 
Back
Top