Hello!



Sorry for the dumb name of the topic - I am a beginner in excel and I know not that much so it's difficult for me to define the issue here.

I attached my excel file which I am referring to. It's kinda difficult to explain what I wanna do here but I will try.

What I wanna do here, is calculate lot sizes - certain variables.
The main issue here, in order to calculate these, there are different conditions for different situations.
What is going to be used here:

1) Cell B8 - dropdown menu under 'Deposit Currency'
2) Cell C12 - dropdown menu under 'Traded Pair'

How to do the following:
When I select something from cell C12, it would take the number out of a spreadsheet called 'quotes' that matches the value (the name) in the cell C12. Dropdown menu is made out of that list in 'quotes'.
For example, if EURUSD is selected in C12 , it would take the value out of a cell C5 in the spreadsheet called 'quotes'.

another thing is matching B8 with C12 under different conditions:
If the value in B8 stands as the first word in C12 (i.e. USD/CAD), perform a multiplication;
If the value in B8 stands as the second word in C12 (i.e. GBP/USD) perform a division;


I am not sure if you are going to understand what I want to do here, I am really bad at defining situations so it may be confusing.

I would find this by myself but as I told earlier, I am not that advanced in excel so I have no idea what to even look for haha

If there is someone who is willing to help me, please give me your skype or contact me via skype karsyz , it will be much easier to communicate and explain the issue!

Thank you!

https://ufile.io/3imwk

I don't have Excel available at home, so I can't see your file. and you did left out most important part of information - the structure of data on sheet quotes! So I have to explain how I would solve a task like this instead simple solution. I hope your Excel isn't older than version 2010.

1. All formulas are much simpler, when using Names and defined Tables. So
a) define cells B8 and C12 as names nDepositCurrency and nTradedPair;
b) define datarange on sheet quotes as table, p.e. tQuotes. NB! when defining table, you have to include column headers, and it will be easier to work with table, when column headers are single word. P.e. TradedPair, SomeOtherColumn, Course, ... And be sure that column TradedPair is leftmost one;

2. Define a Name nDirection = IF(LEFT(nTradedPair,3)=nDepositCurrency,1,0);

3. Define a Name nCourse = VLOOKUP(nTradedPair, tQuotes, COLUMN(tQuotes[[#Headers],[Course]]) - COLUMN(tQuotes[[#Headers],[TradedPair]]) +1,0)
(you can make it simpler when table column TradedPair is in column A)

3. I don't know the structure of table your formula goes into, but it looks like you have there some column with values you want to multiply or divide with course from sheet quotes. So let's assume you define this too as table. p.e. tData, and there is a column Values, and a column Results. Insert into any cell in column Results the formula
=[@Values]*IF(nDirection>0,nCourse,1/nCourse)
When there were no formulas in column Results, or the whole column had same formula in it before, the whole column is updated with new formula.