how to combine text+value in formula please

[slimimi]

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.

 

[schielrn]

What happens if you put this in a cell:

=indirect("MT4|BID!EURUSDm")

 

[slimimi]

I get a #ref error

 

[schielrn]

That doesn't make sense to me?

If you put

=MT4|BID!EURUSDm

It returns a value correct?

If that is true then I cannot see why this doesn't work:

=indirect("MT4|BID!EURUSDm")

Is there any way you can send me the spreadsheet? Just remove any personal data if you are able to. You can send it here.

 

[slimimi]

Sure gonna send it now - its actually a template so its blank.
I have highlighted the cells in yellow:

Sorry about this loooooonggg explanation - just wanted to try and make things clearer if i could:

C4 is where the user will select the currency pair

BP4 is where i wanted to paste a MAX formula which will take its value from YOUR formula that you are helping me with

BQ4 is where i wanted to paste a MIN formula which will take its value from YOUR formula that you are helping me with

K4 should be what triggers YOUR formula to kick off AND also triggers off and enters formulas into BP4 and BQ4

BO4 should be what STOPS your formula and returns fixed values into BP4 and BQ4 based on current figure in those cells

The only problem is that you will need to have MT4 (metatrader) on your machine i think for you to be able to see the =MT4|BID!EURUSDm formula work (i could be wrong though).

(to make matters worse) i also wanted to include in the formula another IF scenario whereby it looks at column E to see if user is entering into a Buy or Sell. I will try to explain further.

I was planning to use the MAX formula, assuming user is entering into a BUY, to create the HIGH value in Cell BP4 and the MIN formula to create the LOW value in Cell BQ4.


However, if user is entering into a SELL, then i need to reverse those and use the MAX formula to create the LOW in cell BQ4 and then the MIN formula to create the HIGH in cell BP4. I hope that this makes sense...

Do you think its better if i try to do all this in VB instead?

You see - to make matters EVEN worse )))))

Once user enters Open Price into K4, for example, thats when i really wanted the BP4 and BQ4 cells to get filled. Something like - user hits returns after entering price in K4 and then userform comes up telling them that prices will be collected live.

Once user enters CLOSE price into BO4, then i wanted BP4 and BQ4 to now be filled with FIXED values (based on the last figures) and the MAX and MIN formulas to stop.

wooooooooaaahhhh.
Sorry about all this.

Just thought by explaining the whole thing - it might make more sense.
Ok gonna send the file now - its a .rar.
cheers a million for this...

 

[schielrn]

Ok I had to wait until I got home to look at it because I didn't have a program at work to open a rar file and every webpage I tried to download something that would open it was blocked. So I opened it at home and it looks that I cannot see what you are seeing without having MT4 (metatrader). Unfortunately that may not work with the indirect function and really the only way I see getting around this would be maybe with a named range or nested if statements to return what you need. You would need to combine the if statements into named ranges because there can only be 7 if statements in one formula in pre-2007 Excel. Since there are more than 7 currencies, you would need at least 3 named ranges of 7 if statements I believe and then pull in the MT4|BID!EURUSDm.

Sorry that I couldn't be of more assistance, since I don't have MT4.

One last thing you could try is putting MT4|BID!EURUSDm in a named range called EURUSD and then use this to call the named range:

=indirect(substitute(C4,"/","")

That may work and it may not.

 

[slimimi]

Hi there - thanks for your reply and help.

You should have been able to use winrar to extract the file.
MT4 is basically MetaTrader4 - its a free platform - if you were interested - you could download it - just google MetaTrader4 - just so you can see how the DDE works with it

Thanks for all your help - i would like to try the named range solution and nested with IF statements. However, do you think its better for me to program all that i mentioned in VB rather than trying to go the formula root? would i have less limitations if i do that?

Would you be able to talk me through the named range solution or VB one regarding what i emailed you about? (only if you have time though)

I would really appreciate it.
Thanks in advance and thanks anyway for taking the time to look at my sheet.

Best Regards,
Imran pishori