Glad to hear that, Colin.

This amendment will add 1 to the value being brought from Sheet2, but
will leave the value from Sheet1 unchanged:

=IF(ISNA(MATCH(E1,Sheet2!B:B,0)),D1,INDEX(Sheet2!A :A,MATCH(E1,Sheet2!
B:B,0)*)+1)

Happy New Year to you.

Pete

On Dec 31 2008, 8:07*pm, Colin Hayes wrote:
Hi Pete

OK I had another go , and you're quite right. It's working fine now - I
must have input an error to cause it to go wrong.

Thanks again - I'm grateful for your kind help and expertise.

BTW - as an extension to this , could the formula be modified to add 1
to the number being imported to Sheet1?

Best Wishes

Colin

In article
,
Pete_UK writes



Hi Colin,

I set this up with the test data you provided, put my formula in F1 of
Sheet1 and copied it down, and got exactly the results you said you
were hoping for.

You must have typed the formula with errors in it, particularly as you
say that Excel is not recognising the sheet names - make sure you use
the names that you have in your workbook, which are not necessarily
Sheet1 and Sheet2. If you have spaces in these names, you will have to
put apostrophes around the complete name, like 'Sheet 2'.

Hope this helps.

Pete

On Dec 31, 3:01*am, Colin Hayes wrote:
In article , Pete_UK
writes

Put this formula in a helper column (e.g. in F1) of Sheet1:

=IF(ISNA(MATCH(E1,Sheet2!B:B,0)),D1,INDEX(Sheet2! A:A,MATCH(E1,Sheet2!
B:
B,0)))

Then you can copy this formula down to cover all the data you have in
Sheet1.

When you are happy that it does what you want, you can fix the values in
column F and then copy/paste them over the values in column D, and then
delete column F.

Hope this helps.

Pete

Hi Pete

OK thanks for getting back.

I tried it out , but found no joy I'm afraid. It's very close though , I
feel.

Curiously , when I drag down the formula in F , it opens an entirely
separate popup requesting update values. I think it's not recognising
Sheet2 as a worksheet and is looking for the values in an imported file.

It just needs to bring over the values in Sheet2 to Sheet1 where the
Reference Numbers match , and replace the values in the appropriate
cells. I feel a little insecure in the clarity of my description , so
I've put some sample figures below.

So Sheet1 , before change:

D * * * * * E

2.65 * *75945bx185
5.75 * *12116bx86
6.75 * *12183bx86
3.75 * *51087bx126
4.75 * *12835bx88
7.75 * *12117bx86
6.75 * *12185bx86
7.75 * *2302bx29
3.75 * *69918bxV12
7.75 * *12115bx86

Sheet2

A * * * * *B

6.15 * *75945bx185
2.36 * *12116bx86
9.75 * *12183bx86
3.75 * *51087bx126
4.75 * *12835bx88
8.25 * *12117bx86
6.45 * *12185bx86
7.75 * *2302bx29
4.75 * *23278bx30
3.75 * *14395bx97
3.75 * *43751bx177

Sheet1 , after update becomes :

D * * * * *E

6.15 * *75945bx185
2.36 * *12116bx86
9.75 * *12183bx86
3.75 * *51087bx126
4.75 * *12835bx88
8.25 * *12117bx86
6.45 * *12185bx86
7.75 * *2302bx29
3.75 * *69918bxV12
7.75 * *12115bx86

I did try tweaking , but I'm not expert enough to do this really , so if
you can see how to get it going , I'd be grateful for your advice.

Best Wishes

Colin

"Colin Hayes" wrote in message
...

Hi All

I have a little puzzle.

I have a workbook with two worksheets.

In Sheet 1 , column D contains a number to 2 decimal places , and column E
contains a reference number.

In Sheet 2 , column A contains equally a number to 2 decimal places , and
column B contains a Reference Number.

I need a formula or a macro which will check the Reference Numbers in
sheet 1 with sheet 2 and , where there is a a match , bring the number
from column A to overwrite the number in column D. Effectively , updating
the numbers to 2 decimal places in Sheet 1 with the ones from Sheet 2. The
Reference Numbers would be unchanged.

It should look for a match anywhere between the 2 columns , and continue
down until it has got to the final row , each time bringing the number
from ws2 to ws1

Can anyone help with this?

Grateful for any help.

Best Wishes- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -