Add value (number) to a dynamically referenced cell based on input in a cell in another sheet.

[Voxov]

Hi, is this even possible using Excel alone:

I will have 4 worksheets (named Dashboard, 2025, 2026, 2027)
Dashboard will have 3 columns (Year, Month, Value). Sheets 2025, 2026, 2027 will each have columns corresponding to months of the year for that sheet, ie. 1-12.

when I enter values in a row in Dashboard (eg, 2026, 7, 200) I'd like the value 200 to be added to any existing value in a cell in Sheet 2026, at column column 7. The cell in sheet 2026 will be on corresponding row to the entered one in Dashboard.

So, to be clear, no formulae exists in any of the possible target cells.

Any help gratefully received.

 

[Voxov]

Or will I need to use something like VBA ?

 

[Sektor]

Perhaps, I did something wrong, but take a look at workbook. Here's the code:

Sub AddValue()

  Dim wksTarget As Worksheet
  Dim wksDash As Worksheet
  Dim last_row As Long
  Dim rw As Long
 
  Set wksDash = Sheets("Dashboard")
  With wksDash
    last_row = .Cells(.Rows.Count, "B").End(xlUp).Row
  End With
  Set wksTarget = Sheets(CStr(wksDash.Cells(last_row, "B")))
  rw = last_row
  With wksTarget.Cells(rw, wksDash.Cells(last_row, "C"))
    .Value = .Value + wksDash.Cells(last_row, "D")
  End With
 
End Sub

 

[Voxov]

Thanks Sektor,
Unfortunately, I'm no VBA expert so I can't see where the script is failing.
When I debug I get the error 'subscript out of range' at line Set wksTarget = Sheets(CStr(wksDash.Cells(last_row, "B")))

 

[Voxov]

My mistake Sektor, it DOES work. Many thanks for your time. Great job.

 

[Voxov]

One last request (i know I'm pushing my luck).
Instead of the target cells being updated only when a new dashboard row is entered, could the target cells be updated when existing dashboard values are updated?

 

[Voxov]

... or a simple form that selects the record in Dashboard by row number, Year and Month and allows the value to be updated in the target sheet for that record.

 

[Sektor]

... or a simple form that selects the record in Dashboard by row number, Year and Month and allows the value to be updated in the target sheet for that record.

Could you elaborate more on this?

 

[Voxov]

Yes, sure.

If solution involves a Form (preferable):
I'm thinking the form will be in Dashboard and could have 4 input fields :
Row, named 'Type of Expense', where the User selects this from a drop-down list. Values such as 'Holiday', 'Travel' and 'Rent' are examples.
'Year', again a dropdown, values 2025 to 2040
'Month', dropdown, values 1 to 12 corresponding to months of the year
'Value', a free entry number.
The user will select, for example, Holiday, 2027, 7, 1500 and on update the value 1500 will be added to the corresponding cell for that year/month (as you already coded for)

If the solution is non-form:
I will list all the Type of Expenses in Dashboard Holiday, Travel, Rent etc in one column and the User will populate the other 3 adjoining columns for Year, Month and Value. This is more or less what you coded for already, except that should any row be amended by changing the Year, Month or Value then the resulting change will be added to any previous value for the target cell for that Month/Year.
This differs from what you coded in that the list of Types of expense will be fixed and non-changeable, but any of the other values for that Expense can be changed ie it doesn't work by just adding a new row (Expense) as it does now.

Hope all that makes sense.
Thanks again for your great work.

 

[Voxov]

I'm wondering if anyone can help with this.