yEval & Indirect

[mbshafe]

Hi,

I have created a yEval function that seems to work fine except when I am trying to perform a mathematical function on two references using INDIRECT...here's a simplified version of what I'm trying to do:

in A1 I have a value of 10, in A2 I have a value of 5
below, I have placed cell references $A$1 and $A$2 in B1 and B2

if I simply write the following formula, I get the intended result of "2"

=INDIRECT(B1)/INDIRECT(B2)

however, if I have the formula as text and attempt to use my yEval function, I get a #REF! error

the funny thing is that my yEval function works if I attempt to use it on either part of the expression, but it doesn't work when I attempt to perform the division

my function is as follows:

Function yEval(item As String)
yEval = Evaluate(item)
End Function

I know I can simply divide the formula up into different cells, but I'd really like to make it work...any thoughts?

TIA,
Mike

 

[MrKowz]

Welcome to the forums!

If you have the cell as text, try instead changing the formula to:

=B1&"/"&B2

 

[mbshafe]

My example was quite simplified relative to what I'm actually trying to do.

I'm trying to create a formula that will work for an entire array of data, basically a conditional formula (i.e. if a certain condition is satisfied, I want to use formula x)...this will allow me to have an array of data where a certain column needs to be calculated differently depending on what type of entry it is.

In my case, I am attempting to calculate the market value for different types of securities and present the results in the same view; however, the formula that I would use to calculate the MV is different for each different security type. In the list of data I have, there will be more than one entry for a given security type.

So I want to essentially say "if the security type is X, then use formula Y" and be able to copy the formula down and make it work regardless of which row I'm in. I've figured out how to define the address of the cells I want to use in the calculation, but in order to perform the calculation the only way I know is to use INDIRECT. This brings me back to my problem.

 

[mbshafe]

Any other thoughts? I'm completely out of ideas here.

 

[MrKowz]

Can you please give some direct examples of some of the formulas you might use?

 

[mbshafe]

Okie doke:

'=INDIRECT(ADDRESS(MATCH(Deals!A5,'Active Positions'!$B$11:$B$1000)+10,COLUMN('Active Positions'!O1),,,"Active Positions"))

I have this in cell T5 on the Deals worksheet. If I used the following formula:

=yeval(T5)

the value returned is $373,609.79 (the value from cell O15 on the Active Positions worksheet)

'=INDIRECT(ADDRESS(MATCH(Deals!A5,'Active Positions'!$B$11:$B$1000)+10,COLUMN('Active Positions'!G1),,,"Active Positions"))

I have this in cell T6 on the Deals worksheet. If I used the following formula:

=yeval(T6)

the value returned is $25,000,000

So, what I want to do is divide 373,609.79 / 25,000,000

Of course, I can do the following:

=yeval(T5)/yeval(T6)

But I don't know why I can't just put the entire formula in one cell, which would be:

'=INDIRECT(ADDRESS(MATCH(Deals!A5,'Active Positions'!$B$11:$B$1000)+10,COLUMN('Active Positions'!O1),,,"Active Positions"))/INDIRECT(ADDRESS(MATCH(Deals!A5,'Active Positions'!$B$11:$B$1000)+10,COLUMN('Active Positions'!G1),,,"Active Positions"))

I can't get any mathematical operations to work when I use the INDIRECT functions.

 

[MrKowz]

I believe it is because you have the ' in front of the formula. Try formatting the cell as text, then removing that single-apostrophe.

Also, you can get rid of the need for VBA and INDIRECT altogether with the following formula:

=INDEX('Active Positions'!$O$11:$O$1000,MATCH(Deals!A5,'Active Positions'!$B$11:$B$1000))

This formula could even be expanded to automatically choose what column to look in based on your security if you have column headers that you can match up to the security type.

 

[mbshafe]

This is fantastic! Thanks very much!!!