Searching and copying to separate sheet

[Yippy]

Hi all! I'm trying to build a macro that can do the following to the below table. The reference data is on table 1. As in the example, looks for the date in the table and offsets to the next column to fill in the figure. It does it for every single fruit. There won't be more than 3 fruits. If it does not find it, such as a table for USD, it will not find any Apple and will not return a figure. The reference data will always be in table 1.

Also where table 2 resides, there are separate tables on the sheet for GBP and different currencies so I can always select the range first before executing the search for the date specified in table 1's date column. The date is the previous working day so it will not have a multitude of dates however in table 2, the dates from previous days will be present. Trying to be as through as possible. Can someone assist? It would make my life easier and won't have to manually key it in every day. Thanks!

Table 2 (GBP)

Date	Apple	Orange	Pear
4/6/2013	15	18	22
4/7/2013	8	5	9
4/8/2013	19.65	285	123.30

<tbody>
</tbody>

Table 1

Fruit	Date	Currency	Amount	Formatted Amount
Apple	4/8/2013	CAD	.60DB	.60
Apple	4/8/2013	GBP	19.65DB	19.65
Apple	4/8/2013	JPY	147DB	147
Orange	4/8/2013	AUD	2.70DB	2.70
Orange	4/8/2013	CAD	123.30DB	123.30
Orange	4/8/2013	EUR	233.35DB	233.35
Orange	4/8/2013	GBP	285DB	285
Pear	4/8/2013	JPY	3.67DB	3.67
Pear	4/8/2013	AUD	2.90DB	2.90
Pear	4/8/2013	USD	287.85DB	287.85
Pear	4/8/2013	CAD	269DB	269
Pear	4/8/2013	GBP	123.30DB	123.30
Pear	4/8/2013	EUR	2.55DB	2.55

<tbody>
</tbody>

 

[s hal]

I don't see this as a macro, it seems you have a currency table that you want to place those values into another spreadsheet. I would suggest defining a named range that you adjust every day and a simple vlookup() or sumifs() from there. You could do a macro to adjust the ranges for you, but I think non macro solutions for simple things are generally best. Please let me know if I'm missing something as I.had difficulty following your example the first few times I read it...

 

[Yippy]

I don't see this as a macro, it seems you have a currency table that you want to place those values into another spreadsheet. I would suggest defining a named range that you adjust every day and a simple vlookup() or sumifs() from there. You could do a macro to adjust the ranges for you, but I think non macro solutions for simple things are generally best. Please let me know if I'm missing something as I.had difficulty following your example the first few times I read it...

Ok that would also work. What would the formula be since there are 3 conditions? Date, product and currency.