Hi Experts,

I have the following code, which takes the User's input, via a dropdown list, and based on the selection made or "description" in column G, the corresponding number, either in column C or column E, is populated in a specific cell in a different worksheet of the same workbook.  Also, the code determines that if the amount is in Column E, this number is converted to a negative number.  Furthermore, if the User selects the same "description" in Column G for multiple line items, the code sums the corresponding cells, if both numbers are in Column C or it nets the amounts if these amounts are in different columns C or E and it places the total or net of these amounts in that one specific cell in a separate worksheet, as I stated previously.

``````Option Explicit

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name = "TB Import (A)" Then
If Not Application.Intersect(Target, ['TB Import (A)'!C:G]) Is Nothing Then
RecalculateAll 1
End If
End If
If Sh.Name = "AJEs (I)" Then
If Not Application.Intersect(Target, ['AJEs (I)'!D:H]) Is Nothing Then
RecalculateAll 2
End If
End If
If Sh.Name = "AJEs (I)" Then
If Not Application.Intersect(Target, ['AJEs (I)'!R:V]) Is Nothing Then
RecalculateAll 3
End If
End If
End Sub

Private Sub RecalculateAll(nType As Integer)
Dim nCtr As Integer, dSum As Double, oDataColA As Range, oDataColB As Range, oCatCol As Range
Dim oThisCat As Range

Application.EnableEvents = False
Application.ScreenUpdating = False

For nCtr = 2 To Application.Range("Descriptions!A1").End(xlDown).Row
Set oThisCat = Application.Range("Descriptions!A:A").Cells(nCtr)
Select Case nType
Case 1
Set oDataColA = ['TB Import (A)'!C:C]
Set oDataColB = ['TB Import (A)'!E:E]
Set oCatCol = ['TB Import (A)'!G:G]
Case 2
Set oDataColA = ['AJEs (I)'!F:F]
Set oDataColB = ['AJEs (I)'!H:H]
Set oCatCol = ['AJEs (I)'!D:D]
Case 3
Set oDataColA = ['AJEs (I)'!T:T]
Set oDataColB = ['AJEs (I)'!U:U]
Set oCatCol = ['AJEs (I)'!R:R]
Case Else
End Select

dSum = Application.WorksheetFunction.SumIfs(oDataColA, oCatCol, "=" & oThisCat.Value) - Application.WorksheetFunction.SumIfs(oDataColB, oCatCol, "=" & oThisCat.Value)
Application.Range("'" & oThisCat.Offset(, 1).Value & "'!" & oThisCat.Offset(, nType + 1).Value).Value = dSum
Next

Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
``````

The code above works perfectly, but I would like to know which [b]vba funcion[/b] I could replace "dSum" with so that instead of reflecting the total or net amount, or a single number, a formula with cell addresses is populated in the corresponding worksheet.

More specifically, in the attached excel spreadsheet you can see that in worksheet "TB Import (A)" I have assigned the "cash" description to three line items.  The amounts in these three line items net to \$250 dollars, and as such, in worksheet "Balance Sheet (H)" in D6, there is an amount of \$250, but what I would like to see instead in "Balance Sheet (H)" cell D6 is the following formula ='TB Import (A)'!C8+'TB Import (A)'!C9-'TB Import (A)'!E10.  The purpose of this change would be to have a better trail to follow, instead of just a number.

Please let me know if you require more detail or further explanation.

Any help  on this will be greatly appreciated.

Thanks,

Sura
FinancialData-Template-3---Copy.xlsm
###### Who is Participating?

Commented:
Sometimes its the little things, like syntax errors:

keySheet = "'AJEs (I)'!" <- see that Exclamation point, and the single quotes around the sheet names.. In the build, I build sheet, address and the key sheet has to be formatted like this for it to work (so I don't have to do "'" & mySheet. & "'!" & column & "7" <- a bit of a pain to code, but once it works its ok.

I wanted to save myself the trouble, so I made keySheet (which is mySheet in the function call) have the single quotations and exclamation point!

So, here's the resolved code:
For nCtr = 2 To Application.Range("Descriptions!A1").End(xlDown).Row
Set oThisCat = Application.Range("Descriptions!A:A").Cells(nCtr)
Select Case nType
Case 1
'Set oDataColA = ['TB Import (A)'!C:C]
'Set oDataColB = ['TB Import (A)'!E:E]
'Set oCatCol = ['TB Import (A)'!G:G]
keySheet = "'TB Import (A)'!"        descrCol = "G"
debitCol = "C"
creditCol = "E"
Case 2
'Set oDataColA = ['AJEs (I)'!F:F]
'Set oDataColB = ['AJEs (I)'!H:H]
'Set oCatCol = ['AJEs (I)'!D:D]
keySheet = "'AJEs (I)'!"        descrCol = "D"
debitCol = "F"
creditCol = "H"

I hope you don't mind, but I moved the functioning code to a module, to help against future "why doesn't it work" issues, which can happen in the SHEETCODE page...

Enjoy!

Dave
FinancialData-Template-3-r3.xlsm
0

Commented:
So - you would like the formula to be pasted in as you've specified?  Why not put the SUMIFS equation in the spreadsheet, if you don't mind my asking?

Dave
0

Commented:
Very clever macro.

It would take one just as clever to find all the "Cash" items and show the math in that cell.  What if there were 20 items in the list marked with the description "Cash"?  The user could just go to the first tab and see all the cash items.  A SUMIFS formula in the balance sheet would do as much - not be as descriptive - but it would show the user what columns you were summing based on the CASH modifier in the description.

Do you really want to go down this route?

Dave
0

AccountantAuthor Commented:
Dave how is it going,

Thanks much for your responses.  I was testing the sumif formula, but for some reason, it wasn't working for me.  Let me continue testing it here.  The cool thing about the code was that the calculations were everytime a change was made to the TB Import (A) worksheet.  Can this be replicated with the SUMIF formula.
0

Commented:
Absolutely - let me make the change and show you.  Glad you sent the note, cause I was trying to build the formula,lol!

Dave
0

Commented:
Put this in cell D6 of your balance sheet:

=SUMIFS('TB Import (A)'!\$C\$7:\$C\$93,'TB Import (A)'!\$G\$7:\$G\$93,"Asset:Cash")-SUMIFS('TB Import (A)'!\$D\$7:\$D\$93,'TB Import (A)'!\$G\$7:\$G\$93,"Asset:Cash")

Dave
0

AccountantAuthor Commented:
Dave,

Regarding your second post.  Yes, it's actually the part that it's not "as descriptive" with which I'm having a problem.  From a manager's point of view, whose are a bit "old school" if you will, they'd like to be able to easily double click on a cell and have that action show the multiple cells that make up a number.  It's more of a "I don't trust that you did it right" mentality, even though it's formula-driven.  I don't know if that makes sense.
0

Commented:
Correction:

=SUMIFS('TB Import (A)'!\$C\$7:\$C\$93,'TB Import (A)'!\$G\$7:\$G\$93,"Asset:Cash")-SUMIFS('TB Import (A)'!\$E\$7:\$E\$93,'TB Import (A)'!\$G\$7:\$G\$93,"Asset:Cash")
0

Commented:
You could have a "hidden" column (like column 0) that has the account you're looking for, thus changing the formula to:

=SUMIFS('TB Import (A)'!\$C\$7:\$C\$93,'TB Import (A)'!\$G\$7:\$G\$93,\$O6)-SUMIFS('TB Import (A)'!\$E\$7:\$E\$93,'TB Import (A)'!\$G\$7:\$G\$93,\$O6)

See attached - balance sheet (note, I changed your subroutines in the VBA so they wouldn't FIRE by renaming the function to functionXXX

Dave
FinancialData-Template-3-r1.xlsm
0

AccountantAuthor Commented:
Dave,

Thanks for your suggestions.  These work fine, but the problem that my managers are having, is that they are not able to, with the click of one button, see the cells that make up the \$250 in cash, from my original example.  See, the SUMIF formula works like a charm, but it's more of a "behind the scenes" approach, as is the code I had originally.  They'd like to be able to double click on cell D6 in "Balance Sheet (H)" and see all the cells in "TB Import (A)" that make up this total.  It's unnecessary if you ask me, but hey, they're the ones that have the last say in this.  Any thoughts?
0

Commented:
So if they look at TB Import, they can see the linkage right there.  How about if they double click on it, it takes them to the TB Import sheet and highlights the cells being referenced?
0

Commented:
Nasty, but do-able:  See the code I added after your dSum assignment.  I did it just for the CASH case, so you can take it from there, I think.  I created a function that built the formula based on what was in the TB Import sheet.

Does this work for you?

Cheers,

Dave

Private Sub RecalculateAll(nType As Integer)
Dim nCtr As Integer, dSum As Double, oDataColA As Range, oDataColB As Range, oCatCol As Range
Dim oThisCat As Range

Application.EnableEvents = False
Application.ScreenUpdating = False

For nCtr = 2 To Application.Range("Descriptions!A1").End(xlDown).Row
Set oThisCat = Application.Range("Descriptions!A:A").Cells(nCtr)
Select Case nType
Case 1
Set oDataColA = ['TB Import (A)'!C:C]
Set oDataColB = ['TB Import (A)'!E:E]
Set oCatCol = ['TB Import (A)'!G:G]
Case 2
Set oDataColA = ['AJEs (I)'!F:F]
Set oDataColB = ['AJEs (I)'!H:H]
Set oCatCol = ['AJEs (I)'!D:D]
Case 3
Set oDataColA = ['AJEs (I)'!T:T]
Set oDataColB = ['AJEs (I)'!U:U]
Set oCatCol = ['AJEs (I)'!R:R]
Case Else
End Select

dSum = Application.WorksheetFunction.SumIfs(oDataColA, oCatCol, "=" & oThisCat.Value) - Application.WorksheetFunction.SumIfs(oDataColB, oCatCol, "=" & oThisCat.Value)
Application.Range("'" & oThisCat.Offset(, 1).Value & "'!" & oThisCat.Offset(, nType + 1).Value).Value = dSum

If nType = 1 Then
Application.Range("'" & oThisCat.Offset(, 1).Value & "'!" & oThisCat.Offset(, nType + 1).Value).Formula = "=" & UpdateNoDSUMShow("'TB Import (A)'!")
End If
Next

Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

Private Function UpdateNoDSUMShow(mySheet As String) As String
Dim myCell As Range, myFormula As String

If Cells(myCell.Row, "C").Value <> 0 Then
myFormula = myFormula & " + " & mySheet & "C" & myCell.Row
ElseIf Cells(myCell.Row, "E").Value <> 0 Then
myFormula = myFormula & " - " & mySheet & "E" & myCell.Row
End If
Next myCell

UpdateNoDSUMShow = myFormula
End Function
FinancialData-Template-3-r1.xlsm
0

Commented:
I'm getting closer...

Dave
``````Private Sub RecalculateAll(nType As Integer)
Dim nCtr As Integer, dSum As Double, oDataColA As Range, oDataColB As Range, oCatCol As Range
Dim oThisCat As Range

Application.EnableEvents = False
Application.ScreenUpdating = False

For nCtr = 2 To Application.Range("Descriptions!A1").End(xlDown).Row
Set oThisCat = Application.Range("Descriptions!A:A").Cells(nCtr)
Select Case nType
Case 1
'Set oDataColA = ['TB Import (A)'!C:C]
'Set oDataColB = ['TB Import (A)'!E:E]
'Set oCatCol = ['TB Import (A)'!G:G]
Application.Range("'" & oThisCat.Offset(, 1).Value & "'!" & oThisCat.Offset(, nType + 1).Value).Formula = "=" & UpdateNoDSUMShow("'TB Import (A)'!", oThisCat.Value, "G", "C", "E")
Case 2
'Set oDataColA = ['AJEs (I)'!F:F]
'Set oDataColB = ['AJEs (I)'!H:H]
'Set oCatCol = ['AJEs (I)'!D:D]
Application.Range("'" & oThisCat.Offset(, 1).Value & "'!" & oThisCat.Offset(, nType + 1).Value).Formula = "=" & UpdateNoDSUMShow("'TB Import (A)'!", oThisCat.Value, "D", "F", "H")
Case 3
'Set oDataColA = ['AJEs (I)'!T:T]
'Set oDataColB = ['AJEs (I)'!U:U]
'Set oCatCol = ['AJEs (I)'!R:R]
Application.Range("'" & oThisCat.Offset(, 1).Value & "'!" & oThisCat.Offset(, nType + 1).Value).Formula = "=" & UpdateNoDSUMShow("'TB Import (A)'!", oThisCat.Value, "R", "T", "U")
Case Else
End Select

'dSum = Application.WorksheetFunction.SumIfs(oDataColA, oCatCol, "=" & oThisCat.Value) - Application.WorksheetFunction.SumIfs(oDataColB, oCatCol, "=" & oThisCat.Value)
'Application.Range("'" & oThisCat.Offset(, 1).Value & "'!" & oThisCat.Offset(, nType + 1).Value).Value = dSum

Next

Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
Private Function UpdateNoDSUMShow(mySheet As String, myAccount As String, descrCol As String, debitCol As String, creditCol As String) As String
Dim myCell As Range, myFormula As String

For Each myCell In Range(Range(mySheet & descrCol & "7").Address, Range(mySheet & descrCol & "65535").End(xlUp).Address)
If myCell.Value = myAccount Then
If Cells(myCell.Row, debitCol).Value <> 0 Then
myFormula = myFormula & " + " & mySheet & debitCol & myCell.Row
ElseIf Cells(myCell.Row, creditCol).Value <> 0 Then
myFormula = myFormula & " - " & mySheet & creditCol & myCell.Row
End If
End If
Debug.Print myFormula
Next myCell

UpdateNoDSUMShow = myFormula
End Function
``````
0

Commented:
Ok, here goes.  I think I did it.  You didn't have data but for the TB Import sheet, so that's all I tested.  It should go similarly for the other sheets.

Based on type, I set the KeySheet name, the description column to test, the debit column and the credit column

Then, I assigned the formula for the destination cell to be based on the function call, passing these 4 parameters.

The function UpdateNoDSUMShow(mySheet As String, myAccount As String, descrCol As String, debitCol As String, creditCol As String) As String

searches in the sheet - mySheet, for myAccount to be used, looking in the descrCol.  If it matches, it looks for non-zero value in the debit Col and thus build formula using + sign.  Otherwise, if finds nonzero in the credit Col, it builds formula using the - sign.  I assume credit or debit but not both on a single entry.

When the formula is finished being built (e.g., the range is examined) then it passes it back.  A null formula = "" will place a 0 in that cell.

The range it searches is from row 7 to the last used row (from bottom to xlup) of the range descrCol range, as both sheets start in row 7.

See code below, and attached file.
Enjoy!

Dave
``````Private Sub RecalculateAll(nType As Integer)
Dim nCtr As Integer, dSum As Double, oDataColA As Range, oDataColB As Range, oCatCol As Range
Dim oThisCat As Range
Dim keySheet As String, descrCol As String, debitCol As String, creditCol As String

Application.EnableEvents = False
Application.ScreenUpdating = False

For nCtr = 2 To Application.Range("Descriptions!A1").End(xlDown).Row
Set oThisCat = Application.Range("Descriptions!A:A").Cells(nCtr)
Select Case nType
Case 1
'Set oDataColA = ['TB Import (A)'!C:C]
'Set oDataColB = ['TB Import (A)'!E:E]
'Set oCatCol = ['TB Import (A)'!G:G]
keySheet = "'TB Import (A)'!"
descrCol = "G"
debitCol = "C"
creditCol = "E"
Case 2
'Set oDataColA = ['AJEs (I)'!F:F]
'Set oDataColB = ['AJEs (I)'!H:H]
'Set oCatCol = ['AJEs (I)'!D:D]
keySheet = "'AJEs (I)'"
descrCol = "D"
debitCol = "F"
creditCol = "H"
Case 3
'Set oDataColA = ['AJEs (I)'!T:T]
'Set oDataColB = ['AJEs (I)'!U:U]
'Set oCatCol = ['AJEs (I)'!R:R]
keySheet = "'AJEs (I)'"
descrCol = "R"
debitCol = "T"
creditCol = "U"
Case Else
End Select

Application.Range("'" & oThisCat.Offset(, 1).Value & "'!" & oThisCat.Offset(, nType + 1).Value).Formula = "=" & UpdateNoDSUMShow(keySheet, oThisCat.Value, descrCol, debitCol, creditCol)
'dSum = Application.WorksheetFunction.SumIfs(oDataColA, oCatCol, "=" & oThisCat.Value) - Application.WorksheetFunction.SumIfs(oDataColB, oCatCol, "=" & oThisCat.Value)
'Application.Range("'" & oThisCat.Offset(, 1).Value & "'!" & oThisCat.Offset(, nType + 1).Value).Value = dSum

Next

Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
Private Function UpdateNoDSUMShow(mySheet As String, myAccount As String, descrCol As String, debitCol As String, creditCol As String) As String
Dim myCell As Range, myFormula As String

For Each myCell In Range(Range(mySheet & descrCol & "7").Address, Range(mySheet & descrCol & "65535").End(xlUp).Address)
If myCell.Value = myAccount Then
If Cells(myCell.Row, debitCol).Value <> 0 Then
myFormula = myFormula & " + " & mySheet & debitCol & myCell.Row
ElseIf Cells(myCell.Row, creditCol).Value <> 0 Then
myFormula = myFormula & " - " & mySheet & creditCol & myCell.Row
End If
End If
Debug.Print myFormula
Next myCell

If myFormula = "" Then myFormula = "0"

UpdateNoDSUMShow = myFormula
End Function
``````
FinancialData-Template-3-r2.xlsm
0

Commented:
PS - I recommend putting non-event functions into a MODULE.  Generally speaking, only the Worksheet_Change or other subs should be in the Sheet codepage, and the rest into a module as public RecalculateALL subroutine and private UpdateNoDSUMShow (so not user viewable) function.

Dave
0

AccountantAuthor Commented:
Hi Dave,

I apologize for the delay in my response, but my day is barely getting started here :).  I'll test this last code you provided and let you know.  From the top I can tell you that yes, it'd be awesome if the code takes the reviewer to the "TB Import (A)" worksheet and highlights it.

Testing now, be right back :)
0

AccountantAuthor Commented:
I'm back.

Thanks for your help with this Dave.

For worksheet TB Import (A), the User will always have both an account name (Column B) and a Debit (Column C) or Credit (Column E).  For worksheet "AJEs (I)", however, the User will enter amounts and will select a description for each entry.  Because of this, I'm getting a "Run-time error '1004': Method "Range" of Object '_Global' failed and it highlights the
``````For Each myCell In Range(Range(mySheet & descrCol & "7").Address, Range(mySheet & descrCol & "65535").End(xlUp).Address)
``````
.

So the main difference between these worksheets is that the AJEs (I) worksheet will always be empty, or in other words, there will not be amounts because these amounts vary from project to project.  The User will enter amounts, Debit or Credit, and assign a description.

Is there a way to account for this in the AJEs worksheet?

Thanks again,

Sura

0

Commented:
I'm not sure - how about some test data?  Walk me through it.

I don't even know if what you had before worked, as there was no data in this area.

Dave
0

AccountantAuthor Commented:
So in the excel file you provided in your previous post, if you look at the Balance Sheet (H) cell D6, the amount shown is (82,860.77), and it shows the formula, awesome.  As part of completing this project, we might need to increase this amount by say, \$20,000, this is just an example.  To do this, we would go to worksheet "AJEs (I)" to D8 and select from the dropdown list, "Assets:Cash" and type \$20,000 in F8.

We then go back to Balance Sheet (H) and this \$20,000 should be reflected in F6.  Also, when you click on this F6, we should see =+'AJEs (I)!D8
0

AccountantAuthor Commented:
Correction, to
Also, when you click on this F6, we should see =+'AJEs (I)!D8

The formula shown in Balance Sheet (H) F8 should be =+'AJEs (I)'F8
0

Commented:
ok let me see...  I never tested type 2 and 3 as I stated.  Let's make 'em work!

Dave
0

AccountantAuthor Commented:
OH YES!

This works perfectly :)

Thank you SO much!

One quick question:  As you know, currently I have three cases for which the code is "listening" if you will, even thought this is not flash, right, but anyway.  Technically, if I were to add one more case, the code should work, correct.  This new case, will be another sets of columns in the AJEs (I) worksheet.
0

Commented:
It should work.  If you have additional issues, just post a RELATED question and I'll be alerted and jump on it.

If theres a bug on the existing work, just post here.

Dave
0

AccountantAuthor Commented:
Got it!

I'm attempting it right now.

Thanks a million Dave, you are the best! :)
0

Commented:
Can you explain how you rated this post?  Just trying to understand your "Good".  Thx.

Dave
0

AccountantAuthor Commented:
I apologize!  I'm at work and I just awarded the points and did not go through the ratings.  How can I go back.  No, your input and Help was EXCELLENT Dave.
0

Commented:
No worries - I think the Rating # is based on how many messages are in the form and no impact on the solution provider.  I did think that "Good" or "Excellent" was a direct result of your input, which is why I asked...

Dave
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.