• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to find an amount from another sheet?

Hi ,

The table which I have set up in a separate worksheet is a static table , since it is not going to change with any new input values you might insert in columns A and B , in Sheet1.

If you have to eliminate this table , the formula that is developed will have to do something similar , since the logic demands it. Since every formula in column D will need to have this logic builtin , you can see that if the formula is copied down thousands of rows , the computing time will only increase.

If at all you think this present set up will not work , I suspect that a substitute formula , which does not make use of the predefined table in Sheet4 , will never work.

The alternative is VBA ; is this acceptable ?

Narayan
 
Hi Narayan,

I noticed that some addition does not work like the policy# 22355248; add (1,550.00) + (144.00) + 718.00 = (976.00). The formula could not recognized that with some different combinations, skipping another cell or two. Can that be fixed?
 
Hi ,

This is something which is not possible using the method I have given. Sorry.

The method can only be used to find combinations which add up to a given number , when there are 2 combinations , and not when there are more than 2. This is because we are creating a matrix of combinations ; thus the row and cell intersection gives us a number which is the result of 2 additions.

If you have a situation where a number can be the result of adding 3 or more numbers , then you need to use VBA. Even if a formula solution is possible , I doubt that it can be through a formula which can be copied down.

Narayan
 
Hi ,

I cannot write the VBA for a generic case , where there can be any number of combinations that add up to a given number ; I think there is a well-known solution of Tushar Mehta's , which you can see here :

http://www.tushar-mehta.com/excel/templates/match_values/

If you can confirm that there can be at most 3 combinations which can add up to the entered number , then I can try to code this restricted case.

Narayan
 
You must log in or register to reply here.
Back
Top