Find and match to fill data

[seanyeap]

Hi all,
I humbly seek assistance and help to fine tune my "find and match" function that works between 2 files. I am using the following:-

=INDEX('[Jan 2009 Sales & Gross Profit Record.xls]PG'!$D$3:$D$12,MATCH(C4,'[Jan 2009 Sales & Gross Profit Record.xls]PG'!$F$3:$F$9,0))

My problem is I am only able to do it in D9 where customer and amount are matched correctly. But when customer appears more than one then it doesnt match corectly.

Please refer to my attachment for better undestanding. Any tips and help is greatly appreciated.

[JBeaucaire]

Maybe like this? (attached)

[seanyeap]

Hi JBeaucaire,
Yes this is what I want. I have tried a couple of times, when I changed the customer name, it did not return the amount correctly. Pls adv. Thanks in advance

[JBeaucaire]

Typos aren't allowed. Every company in your sample sheet comes up when I put that name into C4. If it doesn't work for you, then it's a spelling issue.

[seanyeap]

Hi JBeaucaire,
I need to enter company name in C4 so that it will find and match to fill in the cells. I am sorry to trouble you again. Thanks

[seanyeap]

Hi,
When a company name is entered in C4, all related data from another worksheet will be copied to the cells. Thanks in advance.

[JBeaucaire]

I just tried entering each of these company names:

Super Co
Excellent Co
Standard Machinery
Donkey Corp
Panda Food Company
Fusion Marketing
Standard Machinery

...and each one caused the form to draw over the appropriate info.

The only thing I can think is that your "link" to the other sheet has gotten corrupted somehow. This is the exact sheetname used in the formulas:

Jan 2009 Sales & Gross Profit Record.xls

If yours differs in any way, click on EDIT > LINKS > CHANGE SOURCE and select your source sheet again.

[seanyeap]

Hi JBeaucaire,
I must take away your
'C:\Documents and Settings\Jerry2\Local Settings\Temp\
to replace mine as
[Jan 2009 Sales & Gross Profit Record.xls]
Am I right? I am confussed, sorry

[seanyeap]

Hi JBeaucaire,
I have tried numerous of times, it sometimes works sometimes doesnt work, I am confussed.

[JBeaucaire]

Open the document, click on EDIT > LINKS > CHANGE SOURCE

Click on YOUR source file.

[seanyeap]

Thanks master

[seanyeap]

Hi JBeaucaire,
I am working fine on 100 rows only. If more than 100 rows, it will return wrongly. Where can I amend the coding? Thanks in advance.

[JBeaucaire]

Just adjust the ranges equally. In cell A9 use this and copy down:

=IF($B9="","",INDEX('[Jan 2009 Sales & Gross Profit Record.xls]PG'!$A$1:$A$1000,MATCH($B9,'[Jan 2009 Sales & Gross Profit Record.xls]PG'!$C$1:$C$1000,0)))

And in cell B9, use this ARRAY-entered formula then copy down:

=IF(ROW($D9)-ROW($D$9)+1>$F$4,"",INDEX('[Jan 2009 Sales & Gross Profit Record.xls]PG'!C$1:C$1001,SMALL(IF('[Jan 2009 Sales & Gross Profit Record.xls]PG'!$F$1:$F$1000=$C$4,ROW($A$1:$A$1000),""),ROW($D9)-ROW($D$9)+1)))

.....for D9:

=IF($B9="","",INDEX('[Jan 2009 Sales & Gross Profit Record.xls]PG'!$D$1:$D$1000,MATCH($B9,'[Jan 2009 Sales & Gross Profit Record.xls]PG'!$C$1:$C$1000,0)))

[seanyeap]

Hi JBeaucaire,
Thanks for all the formulas, it works perfectly