I placed a "=countif" function in the column adjacent to your "test"
=IF(H11<>F11,COUNTIF(Data!A:A,A11),"")
Now, I have no idea why you think that the account "1960D50000" for "Marl"
should be 0 when in the Data sheet it has:
1960D50000 EUR Marl -1155.19 -1380.34
But for the rest of them,
most have more than one record in the Data sheet.
VLookup only returns the first of the records.
If you want it to return a SUM of the records with the matching
Account number, we can change the VLookup function to SumIF.
In some cases, there is NO matching records, yet YOU think there should be.
There ARE matching numbers in your "Sheet3", so
clearly something is missing.
Also, be careful with the cell formats.
In some cases, because the account number contains only numbers,
Excel wants to convert it to a number.
So, in the Report you put a (') in front like: '2470670000
If in the Data sheet, the Account column is a Text format, this value
may not have the leading ('), so it would not find a match.
If you have any control over the values, you COULD create
a new column and prefix the values with "Z" (or some alpha character)
Paul
>
>From: Jagadeesh B S <jagg...@gmail.com>
>To: excel-macros@googlegroups.com
>Sent: Thu, November 18, 2010 1:18:24 AM
>Subject: Re: $$Excel-Macros$$ Help required for formule
>
>
>
>
>Hello friends my requirement is met in some way but not the complete solution.
>
>
>Thanks a lot to Paul for the help.
>
>
>This formula helped me a bit but not able to do well as per my requirement.
>Could you please look it to the attachment and help me again...?
>
>
>Thanks for your valuable assistance.
>
>
>Thanks
>Jagadeesh B S
>
>
>
>
>
>On Wed, Nov 17, 2010 at 6:56 PM, Paul Schreiner <schreiner_p...@att.net> wrote:
>
>Your formula for row 3 would look like:
>>
>>=IF(C3="USD",VLOOKUP(A3,Data!A:E,5,FALSE),VLOOKUP(A3,Data!A:E,4,FALSE))
>>
>>Paul
>>
>>>
>>>From: Jagadeesh B S <jagg...@gmail.com>
>>>To: excel-macros@googlegroups.com
>>>Sent: Wed, November 17, 2010 7:45:42 AM
>>>Subject: $$Excel-Macros$$ Help required for formule
>>>
>>>
>>>
>>>Hello Friends
>>>I need a formula or macro( preferabley formule) need to prepare this report .
>>>I need a formula which should get the details for the account # mentioned in
>>>the
>>>column A with the reference to the currency,form the sheet DATA, if the
>>>currency
>>>is USD It should pull the value in the column E and if the currency is other
>>>then USD it should pull the value in the column D.
>>>
>>>Please help with this formula…
>>>
>>>Thanks you very much.:)
>>>
>>>--
>>>----------------------------------------------------------------------------------
>>>
>>>Some important links for excel users:
>>>1. Follow us on TWITTER for tips tricks and links :
>>>http://twitter.com/exceldailytip
>>>2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>>>3. Excel tutorials at http://www.excel-macros.blogspot.com
>>>4. Learn VBA Macros at http://www.quickvba.blogspot.com
>>>5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>>
>>>To post to this group, send email to excel-macros@googlegroups.com
>>>
>>><><><><><><><><><><><><><><><><><><><><><><>
>>>Like our page on facebook , Just follow below link
>>>http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
>>>
>>--
>>----------------------------------------------------------------------------------
>>
>>Some important links for excel users:
>>1. Follow us on TWITTER for tips tricks and links :
>>http://twitter.com/exceldailytip
>>2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>>3. Excel tutorials at http://www.excel-macros.blogspot.com
>>4. Learn VBA Macros at http://www.quickvba.blogspot.com
>>5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>>To post to this group, send email to excel-macros@googlegroups.com
>>
>><><><><><><><><><><><><><><><><><><><><><><>
>>Like our page on facebook , Just follow below link
>>http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
>>
>--
>----------------------------------------------------------------------------------
>
>Some important links for excel users:
>1. Follow us on TWITTER for tips tricks and links :
>http://twitter.com/exceldailytip
>2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>3. Excel tutorials at http://www.excel-macros.blogspot.com
>4. Learn VBA Macros at http://www.quickvba.blogspot.com
>5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
>To post to this group, send email to excel-macros@googlegroups.com
>
><><><><><><><><><><><><><><><><><><><><><><>
>Like our page on facebook , Just follow below link
>http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
>
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
