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

#### noreendeasy

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

<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 B C 1 Movement Movement Value BPS 2 Net Gain/Loss 3 Income 4 Forward Contracts 0.2 0.03 5 Total 0.03

<TBODY>
</TBODY>

The Narrative would need to be automatically updated to using a formula : Is this possible?
 6 Narrative: Main Driver of Movement is Income of .03 giving an overall total of .03

<TBODY>
</TBODY>

Last edited:

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Anyone?

Your example isn't very clear (your first example the numbers don't seem to match your data), however, I *believe* this is what you are looking for.

="Main Driver of Movement is " & IF(C2<>0,CHAR(10) & A2&" of " & C2,"") & IF(COUNT(C2:C3)>1, " and ", "") & IF(C3<>0,CHAR(10) & A3&" of " & C3,"") & IF(COUNT(C2:C4)>1," and ", "")& IF(C4<>0,CHAR(10) & A4&" of " & C4,"") & " " & CHAR(10) & "giving an overall movement of " & C5

NOTE: the Char(10) characters will insert line breaks and need Wrap Text turned on. If you do not want line breaks, use this formula...

="Main Driver of Movement is " & IF(C2<>0, A2&" of " & C2,"") & IF(COUNT(C2:C3)>1, " and ", "") & IF(C3<>0, A3&" of " & C3,"") & IF(COUNT(C2:C4)>1," and ", "")& IF(C4<>0,A4&" of " & C4,"") & " " & "giving an overall movement of " & C5

Can you explain this and maybe I could understand it and edit it myself? Or else what is it you don't understand from my data? The BPS column isn't meant to add up its just an example....

Possibly this is better explained:

 A B C 1 Movement Movement Value BPS 2 Net Gain/Loss this column is a formula based on columns Bs data 3 Income =0.2*Nav*10000 4 Forward Contracts 0.2 0.03 5 Total 0.03

<TBODY>
</TBODY>
Thanks, Noreen

Last edited:
In your first example, you have 0.02 in the data for Net Gain/Loss but use 0.01 in the narrative.
In the second example, you have nothing for Income, but list 0.03 which is shown as the data for Forward Contracts.

Basically, what my formula does is check if the value in column C <> 0 and if so it lists the column header from A and the value from C. The countif determines whether the word AND should be used. If you have more specific questions or if it is not working as required, please let me know.

Hi Sorry I mixed it up,

You're completely spot on about what I need to do, it just doesn't seem to work.

I will use my real data it might explain it actually easier:

 Can you help create the formula for this please? Prior Day NAV 14.26309 Movement Movements BPS PERIOD INCOME 0.00 PERIOD AMORT/ACCRET 0.00 PERIOD INFLATION COMPENSATION 0.00 PERIOD EXPENSES 0.00 INCOME DISTRIBUTIONS 0.00 EXCHANGE GAIN/LOSS RECLASSED AS INCOME -0.00724 -0.05 PERIOD REALIZED GAIN/LOSS 0.00 GAIN/LOSS EFFECT OF TRANSACTIONS 0.00 NET GAIN/LOSS EFFECT -0.04953 -0.35 GAIN/LOSS DISTRIBUTIONS 0.00 CAPITAL SHARE ACTIVITY 0.00 SUBSCRIPTIONS 0.00 REDEMPTIONS 0.00 NET OF CAPITAL SHARE ACTIVITY 0.00 PRELIMINARY NET ASSETS 0.00 EFFECT OF PRICE CHANGES ON HOLDINGS 0.00 ADJUSTMENT FOR AMORT/ACCRET 0.00 RECEIVABLES/PAYABLE 0.00 INCOME 0.00 CCT FORWARD CONTRACTS 0.00 CCT SPOT CONTRACTS 0.00 ADJUSTMENT FOR GLOBAL FUTURES 0.14463 1.01 OTHER MISCELLANEOUS ADJUSTMENTS 0.00 Total 0.62

<TBODY>
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>

Thank you, the actual data makes more sense and I see why my formula doesn't work well (there are simply too many rows for my current method)
Are these always the options listed or are the items in the Movement list variable? That is, always X number of rows with the Total in the same place?

Thank you, the actual data makes more sense and I see why my formula doesn't work well (there are simply too many rows for my current method)
Are these always the options listed or are the items in the Movement list variable? That is, always X number of rows with the Total in the same place?

Yes these are the only options and the total is always in the same place, it comes from a report.

Are you open to using a VBA solution? I don't think a native Excel formula is going to be sufficient in this case.

Yes VBA is ok, I have basic / ok understanding of it - use it often for Macros! Cna you help, I wouldn't know how to use it in this instance? Thanking you kindly

Replies
0
Views
594
Replies
2
Views
243
Replies
5
Views
455
Replies
2
Views
397
Replies
1
Views
333

1,196,369
Messages
6,014,885
Members
441,854
Latest member
Amstaff

### 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.

### Which adblocker are you using?

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

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