Formula returning an error

Hi,

The following formula works as intended:

{=IF(COUNTIF($N$1:$N$1000,"?*")ROW(N2),"",INDEX(N $1:N$1000,SMALL(IF($N$1:$N$1000"",ROW($N$1:$N$10 00)),ROW(N2))))}

Now I want to insert it into another workbook so that the results are
displayed there also. Here I have modified the formula for this purpose:

{=[Flex_connection.xlsm]Names!O2,IF(COUNTIF($N$1:$N$1000,"?*")ROW(N2),"", INDEX(N$1:N$1000,SMALL(IF($N$1:$N$1000"",ROW($N$ 1:$N$1000)),ROW(N2))))}

This formula returns a #VALUE! error.

Anyone got any idea what I am doing wrong. Much appreciate any help given.

Save your work, close the workbook, open it. What happens? When the formula
is in the new workbook, you shouldn't see things like this:
[Flex_connection.xlsm]Names!

That kind of thing means you are referencing another workbook outside of the
current workbook that you are working in. Make sense?

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"gotroots" wrote:

Hi,

The following formula works as intended:

{=IF(COUNTIF($N$1:$N$1000,"?*")ROW(N2),"",INDEX(N $1:N$1000,SMALL(IF($N$1:$N$1000"",ROW($N$1:$N$10 00)),ROW(N2))))}

Now I want to insert it into another workbook so that the results are
displayed there also. Here I have modified the formula for this purpose:

{=[Flex_connection.xlsm]Names!O2,IF(COUNTIF($N$1:$N$1000,"?*")ROW(N2),"", INDEX(N$1:N$1000,SMALL(IF($N$1:$N$1000"",ROW($N$ 1:$N$1000)),ROW(N2))))}

This formula returns a #VALUE! error.

Anyone got any idea what I am doing wrong. Much appreciate any help given.

Ryan,

Save your work, close the workbook, open it. What happens? When the formula
is in the new workbook, you shouldn't see things like this:
[Flex_connection.xlsm]Names!

I am not sure if you are correct about that, I have other formulas elsewhere
in the workbook which references a workbook outside of the workbook which
returns a result. The problem still exists after I do what you advise.

"ryguy7272" wrote:

Save your work, close the workbook, open it. What happens? When the formula
is in the new workbook, you shouldn't see things like this:
[Flex_connection.xlsm]Names!

That kind of thing means you are referencing another workbook outside of the
current workbook that you are working in. Make sense?

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"gotroots" wrote:

Hi,

The following formula works as intended:

{=IF(COUNTIF($N$1:$N$1000,"?*")ROW(N2),"",INDEX(N $1:N$1000,SMALL(IF($N$1:$N$1000"",ROW($N$1:$N$10 00)),ROW(N2))))}

Now I want to insert it into another workbook so that the results are
displayed there also. Here I have modified the formula for this purpose:

{=[Flex_connection.xlsm]Names!O2,IF(COUNTIF($N$1:$N$1000,"?*")ROW(N2),"", INDEX(N$1:N$1000,SMALL(IF($N$1:$N$1000"",ROW($N$ 1:$N$1000)),ROW(N2))))}

This formula returns a #VALUE! error.

Anyone got any idea what I am doing wrong. Much appreciate any help given.