Formula to match a data point in 2 wrkshts

[tobnull]

I need to match a data point in a new report with the previous repor
and then pull in a note for that matching data point.

Each month I get a report have look at the data then make a note:
Column A:work number Column D:note made by me

The data changes some of the data in A: drops off which then would mak
note in column D: unimportant for the current report.

I played around with various versions of:
=if(A1<>””,A1,””) - this was no where near complex enough to grab dat
from another worksheet with data points that drop off the list.

then tried this one:
*=IF(('May 2012 Report'!A3:A226='June 2012 Report'!A3),'May 201
Report'!D3: D226<>"",'May 2012 Report'!$D$2:$D$225)* (put space betwee
: and D got a grin)

This one gives me a True/False statement in the first to cells then i
keeps referring to empty cells.

Does not account for the change in my data in Column A: so records dro
everything in D: even if A: in worksheet 1 does not correspond with dat
in worksheet 2.

Any suggestions to fix thi

 

[Spencer101]

I need to match a data point in a new report with the previous repor
and then pull in a note for that matching data point.

Each month I get a report have look at the data then make a note:
Column A:work number Column D:note made by me

The data changes some of the data in A: drops off which then would mak
note in column D: unimportant for the current report.

I played around with various versions of:
=if(A1<>””,A1,””) - this was no where nea
complex enough to grab data from another worksheet with data points tha
drop off the list.

then tried this one:
*=IF(('May 2012 Report'!A3:A226='June 2012 Report'!A3),'May 201
Report'!D3: D226<>"",'May 2012 Report'!$D$2:$D$225)* (put space betwee
: and D got a grin)

This one gives me a True/False statement in the first to cells then i
keeps referring to empty cells.

Does not account for the change in my data in Column A: so records dro
everything in D: even if A: in worksheet 1 does not correspond with dat
in worksheet 2.

Any suggestions to fix this

Hi,
I can see why your formula is not working, but a dummy version of th
workbook will be needed in order to write the correct formula for you.

If you could post one i'll be happy to help.

Feel free to PM me for an email address if your data cannot be poste
here.

Spencer

 

[tobnull]

Hi,
I can see why your formula is not working, but a dummy version of th
workbook will be needed in order to write the correct formula for you.

If you could post one i'll be happy to help.

Feel free to PM me for an email address if your data cannot be poste
here.

Spencer.

see a dummy work up attache

+-------------------------------------------------------------------
|Filename: Dummy DC3030.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=406
+-------------------------------------------------------------------

 

[Spencer101]

see a dummy work up attached

Try the below formula in cell D2 on the June 2012 Report tab and cop
down.
(Adjust the 21 near the end of the formula to the relevant last row o
the real workbook)

=VLOOKUP($A2,'MAY 2012 REPORT'!$A$2:$D$-21-,4,FALSE

It will feed in the "Note" from the previous report, if applicable, giv
#N/A if the reference in column A does not appear on the previous repor
and give a zero if the reference number appears but there was no not
recorded.

Does that do what you're after?

Let me know either way and I will either adjust the formula if it's no
what you meant, or sit back and enjoy being of help if it is

S

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[tobnull]

Try the below formula in cell D2 on the June 2012 Report tab and cop
down.
(Adjust the 21 near the end of the formula to the relevant last row o
the real workbook)

=VLOOKUP($A2,'MAY 2012 REPORT'!$A$2:$D$-21-,4,FALSE

It will feed in the "Note" from the previous report, if applicable, giv
#N/A if the reference in column A does not appear on the previous repor
and give a zero if the reference number appears but there was no not
recorded.

Does that do what you're after?

Let me know either way and I will either adjust the formula if it's no
what you meant, or sit back and enjoy being of help if it is

S.

That one works gives me what I am looking for but one thing:
1. several cells have #N/A

Tried <>"" at the end but that just screwed up the formula giving me
false answer. Is there away to get a cell that does not match to b
either blank or have a 0

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Spencer101]

That one works gives me what I am looking for but one thing:
1. several cells have #N/A

Tried <>"" at the end but that just screwed up the formula giving me
false answer. Is there away to get a cell that does not match to b
either blank or have a 0?

On the proviso you're using Excel 2007 or later, you can use...


*=IFERROR(VLOOKUP($A2,'May 2012 Report'!$A$2:$D$21,4,FALSE),"")

+-------------------------------------------------------------------
+-------------------------------------------------------------------