how to combine text+value in formula please

slimimi

Well-known Member
Joined
May 27, 2008
Messages
532
Hi there
Anyone know how to do the following please: :)

Example,

I want the user to select a currency pair in cell B4, eg, EUR/USD

Then cell R4 will look to see if there is any data in B4 and, if so, return the following value into Cell R4....

=MT4|BID! & cell B4 & "m"

the cell b4 is the bit i am not sure about.

Thanks in advance.
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

IvenBach

Board Regular
Joined
May 31, 2008
Messages
212
=IF(MT4|BID!B4<>"",MT4|BID!B4&"m","")

I think this is what you are looking for. I can be wrong.
 

slimimi

Well-known Member
Joined
May 27, 2008
Messages
532
Hi iven - thanks for your help. Its almost there in terms of what i am looking for - would you mind helping me to tweak it please?

I will be more specific with my actual spreadsheet cells to make things easier.

In C4 : user will enter a currency pair, ie, EUR/USD (i need to figure out how to get rid of the / symbol too)

So in BP4 i would like it to grab the relevant price data via DDE for the correct currency pair entered in C4.

the formula i am using to grab this data is : =MT4|BID!EURUSDm : this works fine

so this formula needs to change the EURUSD bit to whatever currency pair (minus the / symbol) is selected in C4. Hope this makes sense so far.

I wanted to put an IF statement too in BP4 so that if nothing is entered in C4 then BP4 should just remain blank. i guess something like a =if(C4>0,etc

thanks in advance if you can help... :)
 

schielrn

Well-known Member
Joined
Apr 4, 2007
Messages
6,939
Maybe try:

=if(c4="","",indirect("MT4|BID!" & substitute(c4,"/","") & "m"))

Hope that helps.
 

slimimi

Well-known Member
Joined
May 27, 2008
Messages
532

ADVERTISEMENT

Maybe try:

=if(c4="","",indirect("MT4|BID!" & substitute(c4,"/","") & "m"))

Hope that helps.
hi thanks alot for your help. I learnt about substitute from you and its a great feature.
However - i cant get the whole formula to work. I keep getting a ref error/

I went to Cell A1 and typed in EUR/USD
then i pasted your formula into B1 and changed the C4's to A1s

Would you be able to help further on this please?
 

schielrn

Well-known Member
Joined
Apr 4, 2007
Messages
6,939
Ok what result do you get with the formula:

="MT4|BID!" & substitute(A1,"/","") & "m"

In your example I get

<TABLE style="WIDTH: 153pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=204 border=0 x:str><COLGROUP><COL style="WIDTH: 153pt; mso-width-source: userset; mso-width-alt: 7460" width=204><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 153pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=204 height=17>MT4|BID!EURUSDm</TD></TR></TBODY></TABLE>

Is that the exact name of the sheet?
 

slimimi

Well-known Member
Joined
May 27, 2008
Messages
532

ADVERTISEMENT

Yes you are right - thats what i am getting now but the problem is that it is appearing as text in a cell. I am not referencing to any sheet but to an Application called MT4.exe

For example, if i type in the following into a cell :

=MT4|BID!EURUSDm

this will give me the current Bid Price for the Euro against the US dollar and constantly changes via DDE

The problem is the part i have highlighted in red will change depending on what currency pair the user selects from Cell A1

Hope that helps.
Thanks a million in advance
 

schielrn

Well-known Member
Joined
Apr 4, 2007
Messages
6,939
Is EURUSDm a named range on the sheet MT4|BID?

I'm not sure why the indirect function would not be providing an answer?
 
Last edited:

slimimi

Well-known Member
Joined
May 27, 2008
Messages
532
If it helps - i have used data validation in a cell whereby the user selects from a drop down list of currency pairs. Its not a named range (i dont think so anyway).

They basically just select from 10 currency pairs.

What you have taught me so far works perfect with what text it places in the cell but i just need excel to see that as a formula, like, =MT4|BID!EURUSDm instead of just showing the text "MT4|BID!EURUSDm" in the cell.

Hope that makes sense.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,259
Messages
5,600,575
Members
414,390
Latest member
plimbu

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
Top