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

[noreendeasy]

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

	A</SPAN>	B</SPAN>	C</SPAN>
1</SPAN>	Movement</SPAN>	Movement Value</SPAN>	BPS</SPAN>
2</SPAN>	Net Gain/Loss </SPAN>	0.16</SPAN>	0.02</SPAN>
3</SPAN>	Income</SPAN>
4</SPAN>	Forward Contracts</SPAN>	0.1</SPAN>	0.01</SPAN>
5</SPAN>	Total</SPAN>		0.03</SPAN>
6</SPAN>	Narrative:</SPAN>	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>

<TBODY>
</TBODY>

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:

	A</SPAN>	B</SPAN>	C</SPAN>
1</SPAN>	Movement</SPAN>	Movement Value</SPAN>	BPS</SPAN>
2</SPAN>	Net Gain/Loss </SPAN>
3</SPAN>	Income</SPAN>
4</SPAN>	Forward Contracts</SPAN>	0.2</SPAN>	0.03</SPAN>
5</SPAN>	Total</SPAN>		0.03</SPAN>
</SPAN>	</SPAN>

<TBODY>
</TBODY>

The Narrative would need to be automatically updated to using a formula : Is this possible?

6</SPAN>

Narrative:</SPAN>

Main Driver of Movement is Income of .03 giving an overall total of .03 </SPAN>

<TBODY>
</TBODY>

 

[BiocideJ]

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

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)

 

[noreendeasy]

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!

	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>
E368</SPAN>
	Prior Day NAV</SPAN>	14.26309</SPAN>
	Movement</SPAN>	Movements</SPAN>
	PERIOD INCOME</SPAN>		0.00</SPAN>
	PERIOD AMORT/ACCRET</SPAN>		0.00</SPAN>
	PERIOD INFLATION COMPENSATION</SPAN>		0.00</SPAN>
	PERIOD EXPENSES −325,664.37 0.01116−</SPAN>		0.00</SPAN>
	INCOME DISTRIBUTIONS</SPAN>		0.00</SPAN>
	EXCHANGE GAIN/LOSS RECLASSED AS INCOME</SPAN>	-0.00724</SPAN>	-0.05</SPAN>
	PERIOD REALIZED GAIN/LOSS </SPAN>		0.00</SPAN>
	GAIN/LOSS EFFECT OF TRANSACTIONS </SPAN>		0.00</SPAN>
	NET GAIN/LOSS EFFECT </SPAN>	-0.04953</SPAN>	-0.35</SPAN>
	GAIN/LOSS DISTRIBUTIONS</SPAN>		0.00</SPAN>
	CAPITAL SHARE ACTIVITY</SPAN>		0.00</SPAN>
	SUBSCRIPTIONS</SPAN>		0.00</SPAN>
	REDEMPTIONS</SPAN>		0.00</SPAN>
	NET OF CAPITAL SHARE ACTIVITY</SPAN>		0.00</SPAN>
	PRELIMINARY NET ASSETS</SPAN>		0.00</SPAN>
	EFFECT OF PRICE CHANGES ON HOLDINGS</SPAN>		0.00</SPAN>
	ADJUSTMENT FOR AMORT/ACCRET</SPAN>		0.00</SPAN>
	RECEIVABLES/PAYABLE</SPAN>		0.00</SPAN>
	INCOME</SPAN>		0.00</SPAN>
	CCT FORWARD CONTRACTS</SPAN>		0.00</SPAN>
	CCT SPOT CONTRACTS</SPAN>		0.00</SPAN>
	ADJUSTMENT FOR GLOBAL FUTURES</SPAN>	0.14463</SPAN>	1.01</SPAN>
	OTHER MISCELLANEOUS ADJUSTMENTS</SPAN>		0.00</SPAN>
			0.62</SPAN>

<TBODY>
</TBODY>

 

[noreendeasy]

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

 

[BiocideJ]

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.

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.

 

[BiocideJ]

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