Formula to return a value based on two cells and on entering data

N

noreendeasy

New Member
Joined
Jul 16, 2014
Messages
32
Hi there,

I have a spreadsheet that I would like to return 1. Text String based on which cells are entered and 2. Calculated Formula. Both are in different cells and I would like them merged on data is entered.

Its hard to explain but I'll show you what I mean

This starts blank until we get an exception and then we need to figure out whats causing it

[TABLE="width: 425"]
<TBODY>[TR]
[TD][/TD]
[TD]A</SPAN>
[/TD]
[TD]B</SPAN>
[/TD]
[TD]C</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]1</SPAN>
[/TD]
[TD]Movement</SPAN>
[/TD]
[TD]Movement Value</SPAN>
[/TD]
[TD]BPS</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]2</SPAN>
[/TD]
[TD]Net Gain/Loss </SPAN>
[/TD]
[TD="align: right"]0.16</SPAN>
[/TD]
[TD="align: right"]0.02</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]3</SPAN>
[/TD]
[TD]Income</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4</SPAN>
[/TD]
[TD]Forward Contracts</SPAN>
[/TD]
[TD="align: right"]0.1</SPAN>
[/TD]
[TD="align: right"]0.01</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]5</SPAN>
[/TD]
[TD]Total</SPAN>
[/TD]
[TD][/TD]
[TD="align: right"]0.03</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6</SPAN>
[/TD]
[TD]Narrative:</SPAN>
[/TD]
[TD]Main Driver of Movement is _____ in this case (A Value) Net Gain/Loss of (C Value) ____ .01 and Forward Contracts of .01 giving an overall movement of _____.03 (C Total)</SPAN>
[/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]
So to explain above what I need is to formula the Narrative:

The user pastes values in B which calculates C.

The Narrative needs to return whats in A and C (as above) based on what the user pastes in B.

So if this changed to:
[TABLE="width: 425"]
<TBODY>[TR]
[TD][/TD]
[TD]A</SPAN>
[/TD]
[TD]B</SPAN>
[/TD]
[TD]C</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]1</SPAN>
[/TD]
[TD]Movement</SPAN>
[/TD]
[TD]Movement Value</SPAN>
[/TD]
[TD]BPS</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]2</SPAN>
[/TD]
[TD]Net Gain/Loss </SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3</SPAN>
[/TD]
[TD]Income</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4</SPAN>
[/TD]
[TD]Forward Contracts</SPAN>
[/TD]
[TD="align: right"]0.2</SPAN>
[/TD]
[TD="align: right"]0.03</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]5</SPAN>
[/TD]
[TD]Total</SPAN>
[/TD]
[TD][/TD]
[TD="align: right"]0.03</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]</SPAN>
[/TD]
[TD]</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

The Narrative would need to be automatically updated to using a formula : Is this possible?
[TABLE="width: 425"]
<TBODY>[TR]
[TD="align: right"]6</SPAN>
[/TD]
[TD]Narrative:</SPAN>
[/TD]
[TD]Main Driver of Movement is Income of .03 giving an overall total of .03
</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]


 
Last edited:
Sort by date Sort by votes
OK, I believe this should do what you are looking for. I have written the VBA as a User-Defined Function so you can use it basically as you would a normal Excel formula.

It must be placed in a Module
Code: 
Public Function CreateNarrative(rngMovements As Range, rngBPS As Range) As String
Dim finalText As String
Dim addedCount As Long
    Application.Volatile
    finalText = "Main Driver of Movement is" & vbCrLf
    addedCount = 0
    For Each Cll In rngBPS
        If Cll.Value <> 0 Then
            If addedCount > 0 Then
                If WorksheetFunction.Index(rngMovements, Cll.Row - rngBPS.Row + 1, Cll.Column - rngBPS.Column + 1) = "Total" Then
                    finalText = finalText & " giving an overall total of " & Cll.Value
                Else
                    finalText = finalText & " and"
                End If
            End If
            If WorksheetFunction.Index(rngMovements, Cll.Row - rngBPS.Row + 1, Cll.Column - rngBPS.Column + 1) <> "Total" Then
                finalText = finalText & " " & _
                    WorksheetFunction.Index(rngMovements, Cll.Row - rngBPS.Row + 1, Cll.Column - rngBPS.Column + 1) & " of " & _
                    Cll.Value & vbCrLf
                addedCount = addedCount + 1
            End If
        End If
    Next Cll
    
    
    CreateNarrative = Left(finalText, Len(finalText) - 1) 'remove final line break
End Function

You can then call the UDF from Excel via the following formula (based on the ranges you specified)
=CreateNarrative(A2:A27,C2:C27)
 
Upvote 0

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
That worked excellent except for two things: Please note I did edit your formula as the data was in different rows

Formula:

=CreateNarrative(B8:B34,D8:D34)

1. Can we rounddown the numbers as it brings it a whole string? we can rounddown to 2 deceimal places please!
2. It Didn't bring in the total showed in red:

Can you change that?

Also would you be able to explain how you did this? If not all good!
[TABLE="width: 541"]
<TBODY>[TR]
[TD][/TD]
[TD="colspan: 4"]Main Driver of Movement is

EXCHANGE GAIN/LOSS RECLASSED AS INCOME of -0.050760389228421

and NET GAIN/LOSS EFFECT of -0.347259955591671

and ADJUSTMENT FOR GLOBAL FUTURES of 1.01401589697604

and of 0.615995552155949</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 5"]E368</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Prior Day NAV</SPAN>
[/TD]
[TD]14.26309</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Movement</SPAN>
[/TD]
[TD]Movements</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]PERIOD INCOME</SPAN>
[/TD]
[TD][/TD]
[TD]0.00</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]PERIOD AMORT/ACCRET</SPAN>
[/TD]
[TD][/TD]
[TD]0.00</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]PERIOD INFLATION COMPENSATION</SPAN>
[/TD]
[TD][/TD]
[TD]0.00</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]PERIOD EXPENSES −325,664.37 0.01116−</SPAN>
[/TD]
[TD][/TD]
[TD]0.00</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]INCOME DISTRIBUTIONS</SPAN>
[/TD]
[TD][/TD]
[TD]0.00</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]EXCHANGE GAIN/LOSS RECLASSED AS INCOME</SPAN>
[/TD]
[TD]-0.00724</SPAN>
[/TD]
[TD]-0.05</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]PERIOD REALIZED GAIN/LOSS </SPAN>
[/TD]
[TD][/TD]
[TD]0.00</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]GAIN/LOSS EFFECT OF TRANSACTIONS </SPAN>
[/TD]
[TD][/TD]
[TD]0.00</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]NET GAIN/LOSS EFFECT </SPAN>
[/TD]
[TD]-0.04953</SPAN>
[/TD]
[TD]-0.35</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]GAIN/LOSS DISTRIBUTIONS</SPAN>
[/TD]
[TD][/TD]
[TD]0.00</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]CAPITAL SHARE ACTIVITY</SPAN>
[/TD]
[TD][/TD]
[TD]0.00</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]SUBSCRIPTIONS</SPAN>
[/TD]
[TD][/TD]
[TD]0.00</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]REDEMPTIONS</SPAN>
[/TD]
[TD][/TD]
[TD]0.00</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]NET OF CAPITAL SHARE ACTIVITY</SPAN>
[/TD]
[TD][/TD]
[TD]0.00</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]PRELIMINARY NET ASSETS</SPAN>
[/TD]
[TD][/TD]
[TD]0.00</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]EFFECT OF PRICE CHANGES ON HOLDINGS</SPAN>
[/TD]
[TD][/TD]
[TD]0.00</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]ADJUSTMENT FOR AMORT/ACCRET</SPAN>
[/TD]
[TD][/TD]
[TD]0.00</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]RECEIVABLES/PAYABLE</SPAN>
[/TD]
[TD][/TD]
[TD]0.00</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]INCOME</SPAN>
[/TD]
[TD][/TD]
[TD]0.00</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]CCT FORWARD CONTRACTS</SPAN>
[/TD]
[TD][/TD]
[TD]0.00</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]CCT SPOT CONTRACTS</SPAN>
[/TD]
[TD][/TD]
[TD]0.00</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]ADJUSTMENT FOR GLOBAL FUTURES</SPAN>
[/TD]
[TD="align: right"]0.14463</SPAN>
[/TD]
[TD]1.01</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]OTHER MISCELLANEOUS ADJUSTMENTS</SPAN>
[/TD]
[TD][/TD]
[TD]0.00</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0.62</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]
 
Last edited:
Upvote 0
Also, it needs to finish with a text string after what it details so it would read:

Main driver of movement is __________ giving an overall movement of (total goes in here)____ 0.62 above

Thanks, Noreen
 
Upvote 0
OK, first post will be the corrected solution and then I'll make another post explaining.

Changes to this code include removing the portion to identify the total cell (it will need to be done through the formula as explained below)
AND rounding all outputs to two decimal places.
Code: 
Public Function CreateNarrative(rngMovements As Range, rngBPS As Range) As String
Dim finalText As String
Dim addedCount As Long
    Application.Volatile
    finalText = "Main Driver of Movement is" & vbCrLf
    addedCount = 0
    For Each Cll In rngBPS
        If Cll.Value <> 0 Then
            If addedCount > 0 Then
                finalText = finalText & " and"
            End If
            If WorksheetFunction.Index(rngMovements, Cll.Row - rngBPS.Row + 1, Cll.Column - rngBPS.Column + 1) <> "Total" Then
                finalText = finalText & " " & _
                    WorksheetFunction.Index(rngMovements, Cll.Row - rngBPS.Row + 1, Cll.Column - rngBPS.Column + 1) & " of " & _
                    Round(Cll.Value, 2) & vbCrLf
                addedCount = addedCount + 1
            End If
        End If
    Next Cll
    
    CreateNarrative = Left(finalText, Len(finalText) - 1) 'remove final line break
End Function

The updated formula to type in a cell will be
=createnarrative(B8:B32,D8:D32)&CHAR(10)&" giving an overall movement of " &ROUND(D33,2)

WHERE B8:B32/D8:D32 is all of the data between AND excluding the HEADER and TOTAL rows. B8 should be PERIOD INCOME and B32 should be OTHER MISCELLANEOUS ADJUSTMENTS so adjust as necessary.
Then the text for the Total is added on in the same manner as my initial example so this cell needs to be referenced directly (D33) as the value for the total row.
 
Last edited:
Upvote 0
Explanation:

So this VBA code is a function that takes arguments like any Excel formula and returns some required data back (in this case the narrative)
In this case, the two arguments are ranges which are expected to contain the Movements and BPS values.

When called, the VBA code sets a temporary string [finalText] to the "Main Driver of Movement is "
then it loops through each cell in the BPS range [For Each Cll In rngBPS]
If the current cell value <> 0 then the code adds the corresponding cell in the Movement Range and the BPS value to the [finalText]
The code also keeps track of how many rows have been added (addedCount) and adds "and" before the data if it is not the first row.
After all cells in BPS have been looped through, [finalText] contains the full narrative and gets sent back to Excel via
[CreateNarrative = Left(finalText, Len(finalText) - 1)] which also removes the trailing line break character
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
Return A Cell Value Based On the First Occurrence of data
Replies
5
Views
3K
Fluff
Fluff
C
  • Question Question
Return closest date based on three criteria being true, or return "no date found"
Replies
8
Views
388
chris_bosten
C
C
  • Question Question
Formula returning #VALUE! help
2
Replies
10
Views
159
Peter_SSs
Peter_SSs
C
  • Solved
Return a Prior Month's Value from within the Same Table?
2 3
Replies
22
Views
465
Peter_SSs
Peter_SSs
B
  • Solved
How to return closest value based on two criteria?
Replies
6
Views
415
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,798
Messages
6,181,038
Members
453,014
Latest member
Chris258

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top