• 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.

Formula Request, 2 dropdown box used to pull out table data

A

Afox10

New Member
Hey all,

Any thoughts on how to achieve a formula for the attached example?

I would like to use the 2 dropdown boxes ((M2)Year input and (M3)Month input) to produce (M4)Total Payments Made (pulling from-Payment column on the table) leading up to the input data.

The table starts with September 2005. If we input February, 2007, is there a formula that will return all the payments made up to that point (the total should be 18,301.68).

Formula would need to calculate for any year/month combo.

Thank you for any help you can provide!

AFox
 

Attachments

  • Example 1.xlsx
    17.6 KB · Views: 7
Bosco,

This is a very nice modification, thank you for sharing! Its amazing to me the different formula methods out there, that can ultimately lead to the same answer!
 
PCosta87 said:
Hi @bosco_yip

For some reason that does not work here because of the "M3"... however, changing M3 to 2 (no. of the month for February) and it works :confused:
Click to expand...
Hi PCosta,

1] But this =0+(M3&"/"&M2) worked in my Excel 2016.

If M3 : February (or 2) and M2 : 2010

=0+(M3&"/"&M2) -->

become

1/February/2010 (or 1/02/2010)

2] I attached herein the file with few other formula method

Regards
Bosco
 

Attachments

  • AccumPaymentExample 1.xlsx
    18.4 KB · Views: 4
Last edited:
It seems to work well in my spread sheet as well.

I had another question that you guys might be able to help with. So in my original question I asked how to sum the numbers up.

I would also like to show what the payment amount for that month/year is in a separate cell. Ive attached the same example sheet with the added cell location below the total payments section. The value should be 1,016.76.

Thoughts on this type of formula?

Thanks again!!
 

Attachments

  • Example 1.xlsx
    18.1 KB · Views: 5
Afox10 said:
It seems to work well in my spread sheet as well.

I had another question that you guys might be able to help with. So in my original question I asked how to sum the numbers up.

I would also like to show what the payment amount for that month/year is in a separate cell. Ive attached the same example sheet with the added cell location below the total payments section. The value should be 1,016.76.

Thoughts on this type of formula?

Thanks again!!
Click to expand...

Try,

In M5 :

=INDEX(Machine1_Table[Payment],MATCH(0+(M3&"/"&M2),Machine1_Table[Date],0))

Regards
Bosco
 
Bosco,

This works perfectly in the example sheet (and even if I move the formula to another sheet within the same workbook), however, when I retype the formula in the master workbook and link accordingly to the corresponding cells, I end up getting a #REF. Everything seems to mimic your above formula so I'm not exactly sure why I'm getting the error. I will continue to dig through this formula and see what might be going on.

Thank you for providing the ground work!

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