i want my cell to return a value if J5 is 1 or 2 (which themselves are
results of formulas)
i have this thusfar
=IF(J6=1,"SELL?","",(IF(J6=2,"SELL?","")))

Try: =IF(OR(J5={1,2}),"SELL?","")

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Micayla Bergen" wrote in message
...
i want my cell to return a value if J5 is 1 or 2 (which themselves are
results of formulas)
i have this thusfar
=IF(J6=1,"SELL?","",(IF(J6=2,"SELL?","")))

IF(OR(U5=<'MDA Portfolio Summary'!H2,ACQUIRE,U5='MDA Portfolio Summary'!J2,
REDUCE}))
Thanks Max. now i want to have a cell return either Acquireif it is less
than H2 or Reduce if it is more than J2

"Max" wrote:

Try: =IF(OR(J5={1,2}),"SELL?","")

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Micayla Bergen" wrote in message
...
i want my cell to return a value if J5 is 1 or 2 (which themselves are
results of formulas)
i have this thusfar
=IF(J6=1,"SELL?","",(IF(J6=2,"SELL?","")))

Try something like:

=IF(U5="","",IF(U5<='MDA Portfolio Summary'!H2,"ACQUIRE",IF(U5='MDA
Portfolio Summary'!J2,"REDUCE","")))

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Micayla Bergen" wrote in message
...
IF(OR(U5=<'MDA Portfolio Summary'!H2,ACQUIRE,U5='MDA Portfolio
Summary'!J2,
REDUCE}))
Thanks Max. now i want to have a cell return either Acquireif it is less
than H2 or Reduce if it is more than J2

So =IF(U4="","",IF(U4<'MDA Portfolio Summary'!H8,"ACQUIRE",IF(U4'MDA
Portfolio Summary'!J8,"REDUCE",""))) was working, but now i get a REF error.
the values in H8 and J8 are percentages, and U4 is also a formula result as a
percentage. is this why i get a ref error, i cant find anything else wrong
Thanks again

"Max" wrote:

Try something like:

=IF(U5="","",IF(U5<='MDA Portfolio Summary'!H2,"ACQUIRE",IF(U5='MDA
Portfolio Summary'!J2,"REDUCE","")))

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Micayla Bergen" wrote in message
...
IF(OR(U5=<'MDA Portfolio Summary'!H2,ACQUIRE,U5='MDA Portfolio
Summary'!J2,
REDUCE}))
Thanks Max. now i want to have a cell return either Acquireif it is less
than H2 or Reduce if it is more than J2

I have =IF(U4="","",IF(U4<'MDA Portfolio Summary'!H8,"ACQUIRE",IF(U4'MDA
Portfolio Summary'!J8,"REDUCE",""))) which should work but it actually
returns a REF error. i havent deleted anything so dont understand the error.
the answers in U4 and J and H8 are percentages. Is this the issue?

"Max" wrote:

Try something like:

=IF(U5="","",IF(U5<='MDA Portfolio Summary'!H2,"ACQUIRE",IF(U5='MDA
Portfolio Summary'!J2,"REDUCE","")))

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Micayla Bergen" wrote in message
...
IF(OR(U5=<'MDA Portfolio Summary'!H2,ACQUIRE,U5='MDA Portfolio
Summary'!J2,
REDUCE}))
Thanks Max. now i want to have a cell return either Acquireif it is less
than H2 or Reduce if it is more than J2

Don't think the percentages in U4, J8 and H8 are an issue causing #REF!
Maybe one of the 3 precedent cells (U4, J8 and H8) is evaluating to #REF!
That's all it takes to cause the IF formula to, in turn, evaluate to #REF!
Try a re-check on the formulas in the 3 precedent cells (U4, J8 and H8)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Micayla Bergen" wrote in message
...
I have =IF(U4="","",IF(U4<'MDA Portfolio Summary'!H8,"ACQUIRE",IF(U4'MDA
Portfolio Summary'!J8,"REDUCE",""))) which should work but it actually
returns a REF error. i havent deleted anything so dont understand the
error.
the answers in U4 and J and H8 are percentages. Is this the issue?

Hi Max
the values in H8 and J8 are just numbers as percentages, there are no
formulas in those cells. U4 is a formula but shows the result not a ref error

"Max" wrote:

Don't think the percentages in U4, J8 and H8 are an issue causing #REF!
Maybe one of the 3 precedent cells (U4, J8 and H8) is evaluating to #REF!
That's all it takes to cause the IF formula to, in turn, evaluate to #REF!
Try a re-check on the formulas in the 3 precedent cells (U4, J8 and H8)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Micayla Bergen" wrote in message
...
I have =IF(U4="","",IF(U4<'MDA Portfolio Summary'!H8,"ACQUIRE",IF(U4'MDA
Portfolio Summary'!J8,"REDUCE",""))) which should work but it actually
returns a REF error. i havent deleted anything so dont understand the
error.
the answers in U4 and J and H8 are percentages. Is this the issue?

Email over a copy of your file? I'll take a look.
Email to: demechanik <atyahoo<dotcom
Let me know if you're sending or not here.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Micayla Bergen" wrote in message
...
Hi Max
the values in H8 and J8 are just numbers as percentages, there are no
formulas in those cells. U4 is a formula but shows the result not a ref
error

will send now, thanks Max

"Max" wrote:

Email over a copy of your file? I'll take a look.
Email to: demechanik <atyahoo<dotcom
Let me know if you're sending or not here.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Micayla Bergen" wrote in message
...
Hi Max
the values in H8 and J8 are just numbers as percentages, there are no
formulas in those cells. U4 is a formula but shows the result not a ref
error

Aha ... there was a line break in the formula which was fouling up the
returns for " 'MDA Portfolio Summary'!J8 " and " 'MDA Portfolio Summary'!R8
" in both formula cells ! This was causing the #REF! errors

Example, for the formula in W4 in sheet: Template

: =IF(U4="","",IF(U4<'MDA Portfolio Summary'!H8,"ACQUIRE",IF(U4'MDA
**Portfolio Summary'!J8,"REDUCE",""))

Just place the cursor at the asterisked point (**) in the formula, do a
back-space, then press a single space to correct .. so that it all reads
properly in a single line

Note that you'd normally need to rectify the inevitable line-breaks/wraps
present when you copy paste formulas (especially long formulas) direct
from posts.

I've sent the corrected file to you.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Micayla Bergen" wrote in message
...
will send now, thanks Max

Maybe something has happened to the reference source 'MDAPortfolio
Summary'!J8..........

Vaya con Dios,
Chuck, CABGx3





"Micayla Bergen" wrote in message
...
Hi Max
the values in H8 and J8 are just numbers as percentages, there are no
formulas in those cells. U4 is a formula but shows the result not a ref
error

"Max" wrote:

Don't think the percentages in U4, J8 and H8 are an issue causing #REF!
Maybe one of the 3 precedent cells (U4, J8 and H8) is evaluating to
#REF!
That's all it takes to cause the IF formula to, in turn, evaluate to
#REF!
Try a re-check on the formulas in the 3 precedent cells (U4, J8 and H8)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Micayla Bergen" wrote in
message
...
I have =IF(U4="","",IF(U4<'MDA Portfolio
Summary'!H8,"ACQUIRE",IF(U4'MDA
Portfolio Summary'!J8,"REDUCE",""))) which should work but it actually
returns a REF error. i havent deleted anything so dont understand the
error.
the answers in U4 and J and H8 are percentages. Is this the issue?

"CLR" wrote
Maybe something has happened to
the reference source 'MDA Portfolio Summary'!J8..........

The source J8 was okay, but the formula was broken at that exact point
reference point by the dreaded line-breaks/wrapse <g
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Thank you so much Max! i would not have thought that a line break in a
formula would make a difference!

"Micayla Bergen" wrote:

will send now, thanks Max

"Max" wrote:

Email over a copy of your file? I'll take a look.
Email to: demechanik <atyahoo<dotcom
Let me know if you're sending or not here.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Micayla Bergen" wrote in message
...
Hi Max
the values in H8 and J8 are just numbers as percentages, there are no
formulas in those cells. U4 is a formula but shows the result not a ref
error

"Micayla Bergen" wrote
Thank you so much Max!
i would not have thought that a line break in a
formula would make a difference!

You're welcome !
Yes, it's important to check / remove these line-break(s) after pasting the
posted formulas
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Good job Max...........glad you got it fixed for him........

Vaya con Dios,
Chuck, CABGx3



"Max" wrote in message
...
"CLR" wrote
Maybe something has happened to
the reference source 'MDA Portfolio Summary'!J8..........

The source J8 was okay, but the formula was broken at that exact point
reference point by the dreaded line-breaks/wrapse <g
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Thanks, Chuck !
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"CLR" wrote in message
...
Good job Max...........glad you got it fixed for him........

Vaya con Dios,
Chuck, CABGx3

Max now the formulas on the 'mda model weighting' sheet are giving a ref
error. so i have deleted all the line breaks but the error remains. i am only
getting the error on the stocks that the clients have, not for every one, but
i cant find an error (or line break) on the individual sheets that the
formulas refer to
egad!

"Max" wrote:

"Micayla Bergen" wrote
Thank you so much Max!
i would not have thought that a line break in a
formula would make a difference!

You're welcome !
Yes, it's important to check / remove these line-break(s) after pasting the
posted formulas
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

This is actually a subject for another post(s) !
Let me take a closer look at the other formulas a little later.
I'll post back here.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Micayla Bergen" wrote in message
...
Max now the formulas on the 'mda model weighting' sheet are giving a ref
error. so i have deleted all the line breaks but the error remains. i am
only
getting the error on the stocks that the clients have, not for every one,
but
i cant find an error (or line break) on the individual sheets that the
formulas refer to
egad!

... now the formulas on the 'mda model weighting' sheet
are giving a ref error ...

First, a clarification that the above actually had *nothing* to do with the
previous issue(s) raised and resolved earlier in this thread. I've cleaned
up and reconstructed the formulas in the problem sheet as per below
(Corrected file sent to you)

Clean-up and reconstruction of formulas
in sheet: MDA Model Weighting
------------------------------------------
The vlookup range was adjusted to: $A$4:$J$39 instead of $A$4:$I$39
The col index ref "10" in the vlookup for "Value" was causing the #REF!
everywhere previously because it pointed to a non-existent range ($A$4:$I$39
is only 9 cols!)

To facilitate copying down of formulas and to suit the sheet structure where
you had sheetnames merged and straddling 3 cols from cols D to AV, INDIRECT
was used. Along the way there were a couple of inconsistencies in the
sheetname structure for some sheets which were found and cleaned up.

Extraction of the 3 col index refs for the Vlookup per group of 3 cols was
automated via using another nested vlookup which read the 3 col headers
within each group: No. of Shares (4), Value (10), and Portfolio Weighting
(6)

Example: ... VLOOKUP(D$2,MDA_1,2,0) ...
where MDA_1 is a named range referring to a table in a new sheet: Index

No. of Shares 4
Value 10
Portfolio Weighting 6

Reconstructed formulas for cols D to AV
------------------------------------------------------
Typical set of 3 cols, e.g.: cols D to F

D1 contained the sheetname, e.g.: A & E John Super
(D1 was merged over D1:F1)

D2:F2 contained the 3 col headers:

No. of Shares
Value
Portfolio Weighting

Formula placed in D3:

=IF(ISNA(VLOOKUP($A3,INDIRECT("'"&$D$1&"'!$A$4:$J$ 39"),VLOOKUP(D$2,MDA_1,2,0
),0)),"",VLOOKUP($A3,INDIRECT("'"&$D$1&"'!$A$4:$J$ 39"),VLOOKUP(D$2,MDA_1,2,0
),0))

D3 was copied across 3 cols to F3

D3:F3 was then selected and copied for each successive set of 3 cols to the
right, to G3:I3, J3:L3, ... AT3:AV3 etc, and Edit Replace used to replace
the $D$1 to $G$1, $J$1 ... etc

D3:AV3 could then be selected and copied down to AV47
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Aha, just noticed that you've changed the subject line of your original post
since .. it's a bit too late but I've read in posts here that one shouldn't
touch the subject line of any original post, as it might cause downstream
problems with threading either here or in google's archives or elsewhere ..

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----