Cross-referencing content of one worksheet with another

[Colin Hayes]

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

 

[Pete_UK]

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

 

[Colin Hayes]

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

 

[Pete_UK]

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

 

[Colin Hayes]

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

 

[Pete_UK]

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

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

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.

 

[Colin Hayes]

Hi Pete

OK that's perfect now. Thanks - I'm very grateful for your help.

It works brilliantly , and is just what I was hoping for.

Although , Excel seems to be perpetually calculating now. As I'm
applying the formula to a large number of rows , it's constantly
freezing up while it calculates and recalculates. Just switching between
sheets sets it off again. I understand why it's doing it , but wondered
if there was a way to minimise this.


Best Wishes


Colin

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

 

[Pete_UK]

In my first post I stated:

"...
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.
...."

To fix the values, you should highlight the cells with the formula in
and then click <copy>, then Edit | Paste Special | Values (check) | OK
then <Enter>. Now you can copy/paste these into column D to overwrite
the values there, and you can delete column F. As there are now no
formulae in operation, there is nothing for Excel to calculate, so it
should run much more quickly.

Hope this helps.

Pete

 

[Colin Hayes]

Hi Pete

Yes , I understand the principal of course. The problem is in the
process rather than the outcome , however.

I'm pasting new figures into both sheets so the comparison can be made.
As soon as I paste into the first sheet , the calculating starts. This
will give false results of course , because I have yet to paste into the
second sheet. It's comparing old data. I can't open the second sheet
because the machine is preoccupied with calculating on the first sheet.

Once this has finished , I can open the second sheet and paste in the
figures. When the calculation has finished , I can go back to the first
sheet , and the calculating starts again...

I've got around this by switching auto-calculation off via the Options
menu , and then pasting my data in peace. Then I switch auto-calculation
back on , and it does it's job. Seems a bit long-winded , that's all.

Anyway , job done Pete , and I'm very grateful for you help and
expertise.



Best Wishes


Colin

 

[Pete_UK]

You're welcome, Colin.

Pete