Hi all
I see there is some proble with the data only.which needs some changes to be
done.
Thank you so much for the help.
Thanks
Jagadeesh B S
On Thu, Nov 18, 2010 at 7:56 PM, Ajay Varshney
<ajayvarshney21...@gmail.com>wrote:
> Problem is with the data on the Data tab.
>
>
> Almost all accounts have more than 1 entry which is causing mismatch in the
> results.
>
>
>
>
>
>
>
>
>
>
> On Thu, Nov 18, 2010 at 6:23 PM, Paul Schreiner <schreiner_p...@att.net>wrote:
>
>> 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
>>
>
> --
>
> ----------------------------------------------------------------------------------
> 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
