Help! Need to fill depreciation table based on dropdown date selection!

Hi everyone,

I'm not even sure what I'm trying to do is possible but I figured I would try to find out! Basically, I want to be able to fill only certain cells in an operating impact/depreciation schedule based on a month/year combination selected from a drop down list.

So, say a $100,000 purchase was made in Feb 2013. The "user" would enter 100000 in H4 and select "Feb 2013" from a drop down list in H2. This would populate a formula (H4/36) in the cells of the depreciation schedule, BEGINNING with B3 and ending with E2. Depending on the Month/year selected in the dropdown, the cells that filled with info would always be different. Please see my attachment and let me know if you need further clarification. Thanks, I'm hoping somehow, someway this is possible!!

How does this work for you?

---

Originally Posted by bentleybob

How does this work for you?

OMG how did you do that?!!! Genius! thank you so so much!!

Originally Posted by bentleybob

How does this work for you?

http://www.excelforum.com/search.php?search_type=1

Thank you so much again for you help. I have another quick question if you have time to answer it - Would it be possible to include multiple purchases in the schedule? I thought by using this formula I might be able to:

=IF(DATE(VALUE("20"&RIGHT(C$4,2)),VLOOKUP(LEFT($B5,3),$P$5:$Q$16,2,FALSE),1)>=DATE(RIGHT($J$5,4),VLOOKUP(LEFT($J$5,3),$P$5:$Q$16,2,FALSE),1),$I$5/36,"") + IF(DATE(VALUE("20"&RIGHT(C$4,2)),VLOOKUP(LEFT($B5,3),$P$5:$Q$16,2,FALSE),1)>=DATE(RIGHT($J$6,4),VLOOKUP(LEFT($J$6,3),$P$5:$Q$16,2,FALSE),1),$I$6/36,"")

But I am getting an error. I'd ideally like to show multiple purchases. If you get a chance, look at my updated spreadsheet and you'll see what I mean. Thanks again!!!

I haven't spent any time looking at your formula (will try to later), but I suspect you're getting an error because you're trying to add numbers and text. Change the double quotes to zeroes and it will likely work for you.

Originally Posted by bentleybob

I haven't spent any time looking at your formula (will try to later), but I suspect you're getting an error because you're trying to add numbers and text. Change the double quotes to zeroes and it will likely work for you.

Thank you!! Changing the double quotes to zeroes worked for the first three columns (2013 - 2015) but I am having trouble with the last column (2016). For instance, if you have a chance to look at my sheet, I've selected a purchase in January 2013 and January 2016, but the 2016 is not calculating properly. Thanks again for all of your help, I'm not sure why I'm struggling so much with this last column.

The problem is that the formula -- which was originally intended for one purchase -- checks to see if there are already 36 monthly elements, in which case it knows that the asset is fully depreciated. That test no longer works because you have multiple, overlapping items. You will need to rework this. Alernatively, you could have a separate depreciation schedule for each purchase and add them for like classes (e.g., hardware, software, etc.).

Thanks again. I've completely reworked the schedule. Could you take a look and tell me if, beginning in AM5, there is a way to use a COUNTIF function to check and see if there were already 36 monthly elements?

In E5, change the formula to:

Please Login or Register  to view this content.

and copy across and down.