Add value to another cell in other sheet when choosing from dropdown list

Copper Contributor

Hello everyone :) 

I am trying to create my own budget scheme to keep track of my finances. 
I have an idea of how I want it but do not have the right competences to implement it. Will try to make it as thorough as possible. 
Maybe this is really hard, or not possible, but would be interested to see. 

Imagine a "Budget" sheet with January on the top row 1, cell B1, February in cell C1(followed by the other months). 
Below there is two rows, Income (cell A2) and Expenses (cell A3) (simplified, have more categories in the real one). In cell B2 should the Income-value be, and in B3 the Expense-value.

 

In  Sheet 2, let´s call it "Overview" I would like to have this type of setup:

  • TODAY() in cell D1 and MONTH(F1) in cell E1 to capture tge current month 
  • A cell A1 where you can type a value 
  • A cell B1 which is a dropdown list with Income, Expenses to choose from 

The formulas should do the following 

  • When you type the value seldomly, nothing should happen. But when you choose a category from the dropdown list, that value should be transferred to the "Budget" sheet. 
    E.g. in "Overview", cell A1 I type 500, and choose Income from the dropdown in B1. That value should go to "Budget" and add to cell B2. Similar for Expense, it should add to cell B3.
  • The date should be captured so it knows that if it is January (E1 = 1) and February (E1 = 2), so it should know which column to add the value to. 

 

Appreciate all feedback, assistance, and example sheets :D 

2 Replies

@Dojje7 

I've made an example within one sheet where you can enter a value and choose a selection from a dropdown list. Perhaps this is similar to what you are looking for.

 
 

@Dojje7 

I'm also curious if you can invent a formula that can retain a value once calculated (e.g. capture a number from "Overview" to "Budget" sheets when the year and month match while the number remains once TODAY() is past those year and month). VBA should be able to handle the requirement, but I'm not certain that using formulas only is viable.
There's one workaround I could think of: Enable iterative calculation in Formulas Excel options will let you use circular reference, thus the formulas under each month in "Budget" sheet can be something like below. 

 

=IF(YEAR(overview_date)&MONTH(overview_date)&overview_category=YEAR(budget_date)&MONTH(budget_date)&budget_category,overview_data,self_referencing_cell)

 

See attached file. I checked it works, but I have no idea how detrimental the consequences of letting circular references can be. Hope Excel gurus provide us with better solutions.