Collect Data From One Sheet Into Another

[honkin]

Hi

I need to be able to bring some data from a sheet in one file into a sheet in another file, but have no idea where to start and am hoping someone here may be able to assist. I'll try to explain.

The source data I am after resides in columns P & Z and the sheet is called VDW Place. To find the data, the date and name of need to match; one issue is that the date format in the recipient sheet is just date whereas in the source sheet, it includes the time, which is not needed in the new sheet. So source is 1/12/2018 10:30:00 and target is 1/12/2018. The date in both sheets is in column A.

The name in the source sheets is in column J and the heading for that is Form, and the target has the same heading at column E. The target sheet name is Shortlist.

So the source and target files will both have the same name already in columns J & E and the same date in column A. As indicated, the date format in the source includes the time, which may cause an issue. What I need to occur is if the name and date match in both sheets, to have the data in columns P & Z be copied into columns H & I in the target respectively.

I hope this makes sense and is something which can be done

Thanks so much in advance

 

[honkin]

A quick followup, I have changed the date format in the source sheet to the same as the target, so that is now not an issue. It now just needs to find when the dates and names match in source (dates = A & names = J) and target (dates = A & names = E), that data in columns P & Z in the source is copied to columns H & I in the target.

cheers

 

[honkin]

Hi again
I just want to try and make it even clearer as it may still be sounding a bit wishy washy, so here is what it is using cells.

If A1 (source) = A1 (Target) AND J1 (source) = E1 (target) the value will = P1 (source). It seems simple, but I am unsure of how to reference other sheets using vlookup, if that is what is required

I hope this simplifies it a little

cheers

 

[honkin]

I have been trying to resolve this myself and have worked on a formula, but Excel tells me there are errors and I can't work out where they are.

=IF(AND(A1=‘[VDW-December-2018.xlsx]VDW’!A1, J1=‘[VDW-December-2018.xlsx]VDW’!E1),‘[VDW-December-2018.xlsx]VDW’!P1, 0)

I don't necessarily want to use IF(AND but can't work out another function to use. I only want the result if TRUE and nothing to happen if FALSE. The file name and sheet references should be correct, but it is beyond me to get this right

Both things need to be TRUE for the response, else it will just return a zero.

In simple form it is saying, IF A1 (source) = A1 (target) and J1 (source) = E1 (target) return the value in P1 (source) otherwise return 0

What is wrong in the formula?

Thanks in advance