Autofill on specific cell based on search criteria

**donmach** · 06-08-2012, 02:50 PM

Hi,

I'm new to this forum and am looking for a solution for my problem. Hope that someone can help.

Here is what I am trying to do:

I have 2 sheets in a workbook, namely "Funds History" and "Funds Data".

On the "Funds Data" sheet, I have created a "Connection" which get's external data from the web. The information downloaded looks like this in the "Funds Data" sheet.

Index	Date	Fund	Fund Abbr	NAV	Chg	Chg %
41067PEF	07-06-2012	PUBLIC EQUITY FUND	PEF	0.2669	0.0004	0.15%
41068PIX	08-06-2012	PUBLIC INDEX FUND	PIX	0.6857	-0.0020	-0.29%
41067PSF	07-06-2012	PUBLIC SAVINGS FUND	PSF	0.5610	0.0019	0.34%
41068PRSF	08-06-2012	PUBLIC REGULAR SAVINGS FUND	PRSF	0.6192	-0.0004	-0.06%

I am able to lookup the information I need based on the "Index" via VLOOKUP and can populate it into a specific column. No issue about that. What I want to achieve is to automatically populate it into a table based on the "Fund Abbr" and "Date" column. This is where I want it to be auto populated:

	05/06/2012	06/06/2012	07/06/2012	08/06/2012	09/06/2012
PEF			0.2669
PRSF				0.6192
PSF			0.5610

I have created a macro that will refresh the "Connection" with the latest data from the internet. Now, I am looking for a way to write a macro that could pick the fund types based on the date and auto fill it into the relevant cells. Can anyone help me?

Thanks.

**oeldere** · 06-08-2012, 02:58 PM

Isn't it easier to make an pivot table and refresh the data by a macro.

I can't help you with the macro (VBA).

**donmach** · 06-08-2012, 03:02 PM

Originally Posted by oeldere

Isn't it easier to make an pivot table and refresh the data by a macro.

I can't help you with the macro (VBA).

Do you mean creating a Pivot Table based on "Funds Data" sheet?

**oeldere** · 06-08-2012, 03:25 PM

Yes, with the data you needed.

**GazP** · 06-08-2012, 03:48 PM

I think this fits with your example table:

=SUMIFS('Funds Data'!$E$2:$E$5,'Funds Data'!$D$2:$D$5,'Funds History'!$A2,'Funds Data'!$B$2:$B$5,'Funds History'!B$1)

It totals the NAV column from Funds Data where it matches the date and Fund Abbreviation for a particular intersection in the table. Is that what you wanted?

**donmach** · 06-08-2012, 05:17 PM

Sorry if my explanation is not good enough. I'm fairly new to Excel and have been trying to get this excel sheet to work the last couple of days...

Attached is my Excel Sheet Example and what I have done so far:

Download fund prices from the internet on a daily basis to the "Funds Data" sheet when I click on a button (this button in in the "Funds History" Sheet). So, will change everyday when I click the button.
In the "Funds History" sheet, I pulled the fund prices I wanted based on the Date and Fund Types (Fund Abbr column) using VLOOKUP.
Now, I have a table with Date and Fund Types in "Funds History" sheet. I need these values to be filled into them bearing in mind that "Funds Data" Sheet changes everyday. I want the fund prices stored in the table and not change dynamically. How can I do this?