return value from other spreadsheet

[Guest]

i have this formula
=IF(COUNTIF(Path/Stocks'!$B$10:$B$413,A4),"S",IF(COUNTIF('Path/Income
securities'!$B$10:$B$413,A4),"FI",IF(COUNTIF('Path/Property &
Infrastructure'!$B$10:$B$413,A4),"PI","None")))
i want S, FI or PI to be returned depending on where the value in A4 is
found, but i get a value message.
Please help.
thank you

 

[Guest]

Some functions do not work unless the referenced workbook is open. COUNTIF()
is one of those that doesn't. I suggest that you rewrite using the MATCH()
function instead.

The other option is to open up all referenced workbooks and then use Save As
and save as a Workspace instead of individual workbooks. That will insure
that all referenced workbooks are open each time that you open the workspace
in the future.

 

[Guest]

Consistent with the points by JLatham that COUNTIF doesn't work with closed
referenced books, perhaps you could try something like this instead:

=IF(ISNUMBER(MATCH(A4,path_[book1.xls]Stocks'!$B$10:$B$413,0)),"S",IF(ISNUMBER(MATCH(A4,path_[book1.xls]Income
Securities'!$B$10:$B$413,0)),"FI",IF(ISNUMBER(MATCH(A4,path_[book1.xls]Property & Infrastructure'!$B$10:$B$413,0)),"PI","None")))

Adapt the above to suit the path & name of the referenced workbook ..

 

[Guest]

I changed my formula to
=IF(MATCH('C:\Documents and Settings\Gillian Mason\My Documents\[copy 2Share
Recommendations.xls]Stocks'!$B$10:$B$413,A4,0),"S",IF(MATCH('C:\Documents and
Settings\Gillian Mason\My Documents\[copy 2Share Recommendations.xls]Income
Securities'!$B$10:$B$413,A4,0),"FI",IF(MATCH('C:\Documents and
Settings\Gillian Mason\My Documents\[copy 2Share Recommendations.xls]Property
& Infrastructure'!$B$10:$B$413,A4,0),"PI","None")))
but still have a value error.
(

 

[Guest]

Thanks Max. i tried that
=IF(ISNUMBER(MATCH(A6,C:\Documents and Settings\Gillian Mason\My
Documents\[copy 2Share
Recommendations.xls]Stocks'!$B$10:$B$413,0)),"S",IF(ISNUMBER(MATCH(A6,
C:\Documents and Settings\Gillian Mason\My Documents\[copy 2Share
Recommendations.xls]Income
Securities'!$B$10:$B$413,0)),"FI",IF(ISNUMBER(MATCH(A6, C:\Documents and
Settings\Gillian Mason\My Documents\[copy 2Share Recommendations.xls]Property
& Infrastructure'!$B$10:$B$413,0)),"PI","None")))
but says there is something wrong w the path, even though i cant see what it
is. is it possible its just the position of my commas and apostrophies?

Consistent with the points by JLatham that COUNTIF doesn't work with closed
referenced books, perhaps you could try something like this instead:

=IF(ISNUMBER(MATCH(A4,path_[book1.xls]Stocks'!$B$10:$B$413,0)),"S",IF(ISNUMBER(MATCH(A4,path_[book1.xls]Income
Securities'!$B$10:$B$413,0)),"FI",IF(ISNUMBER(MATCH(A4,path_[book1.xls]Property & Infrastructure'!$B$10:$B$413,0)),"PI","None")))

Adapt the above to suit the path & name of the referenced workbook ..

---

i have this formula
=IF(COUNTIF(Path/Stocks'!$B$10:$B$413,A4),"S",IF(COUNTIF('Path/Income
securities'!$B$10:$B$413,A4),"FI",IF(COUNTIF('Path/Property &
Infrastructure'!$B$10:$B$413,A4),"PI","None")))
i want S, FI or PI to be returned depending on where the value in A4 is
found, but i get a value message.
Please help.
thank you

 

[Guest]

Micayla, If you have cut and pasted the formula into your posting here, I do
not see a leading single quote mark for your references to paths and/or
worksheets.

Any time you have spaces in your path/file/sheet names you need to enclose
everything before the exclamation point in front of the cell range reference
in single quote marks. Two examples, one for an open book, one for same book
closed on a shared drive:
='[Foreign Book.xls]Sheet1'!$B$10:$B$413
='\\Antec\shareddocs\[Foreign Book.xls]Sheet1'!$B$10:$B$413

I also recommend against using spaces, special symbols (like the & symbol)
and such in names. A personal thing, but it generally complicates matters.
I use the underscore symbol to provide spacing like
Property_and_Infrastructure or Income_Securities. I know it isn't as pretty,
but it does alleviate some problems like this can be introduced by them. For
example, the & symbol, if not enclosed in single or double quotes (depending

Thanks Max. i tried that
=IF(ISNUMBER(MATCH(A6,C:\Documents and Settings\Gillian Mason\My
Documents\[copy 2Share
Recommendations.xls]Stocks'!$B$10:$B$413,0)),"S",IF(ISNUMBER(MATCH(A6,
C:\Documents and Settings\Gillian Mason\My Documents\[copy 2Share
Recommendations.xls]Income
Securities'!$B$10:$B$413,0)),"FI",IF(ISNUMBER(MATCH(A6, C:\Documents and
Settings\Gillian Mason\My Documents\[copy 2Share Recommendations.xls]Property
& Infrastructure'!$B$10:$B$413,0)),"PI","None")))
but says there is something wrong w the path, even though i cant see what it
is. is it possible its just the position of my commas and apostrophies?

Consistent with the points by JLatham that COUNTIF doesn't work with closed
referenced books, perhaps you could try something like this instead:

=IF(ISNUMBER(MATCH(A4,path_[book1.xls]Stocks'!$B$10:$B$413,0)),"S",IF(ISNUMBER(MATCH(A4,path_[book1.xls]Income
Securities'!$B$10:$B$413,0)),"FI",IF(ISNUMBER(MATCH(A4,path_[book1.xls]Property & Infrastructure'!$B$10:$B$413,0)),"PI","None")))

Adapt the above to suit the path & name of the referenced workbook ..

---

i have this formula
=IF(COUNTIF(Path/Stocks'!$B$10:$B$413,A4),"S",IF(COUNTIF('Path/Income
securities'!$B$10:$B$413,A4),"FI",IF(COUNTIF('Path/Property &
Infrastructure'!$B$10:$B$413,A4),"PI","None")))
i want S, FI or PI to be returned depending on where the value in A4 is
found, but i get a value message.
Please help.
thank you

 

[Guest]

I changed my formula to: =IF(MATCH(...),..

It won't work this way, Micayla
(besides the syntax mistakes in your change)

We need to use IF(ISNUMBER(MATCH(..)),..
Try it again as per earlier suggestion ..

---

 

[Guest]

Perhaps try opening the referenced workbook (eg: book1.xls) first,
then try it as:

=IF(ISNUMBER(MATCH(A4,[book1.xls]Stocks'!$B$10:$B$413,0)),"S",IF(ISNUMBER(MATCH(A4,'[book1.xls]Income
Securities'!$B$10:$B$413,0)),"FI",IF(ISNUMBER(MATCH(A4,'[book1.xls]Property
& Infrastructure'!$B$10:$B$413,0)),"PI","None")))

Opening the referenced book simultaneously makes it simpler to register the
formula as it "removes" the path from the formula. Leave it to Excel to
insert the path correctly when the referenced book is subsequently closed.

 

[Guest]

Max, check those single quote marks also - the leading one in the very first
MATCH() formula seems to be missing - needs one right after A4, to pair up
with the one just before the ! in that formula.

Perhaps try opening the referenced workbook (eg: book1.xls) first,
then try it as:

=IF(ISNUMBER(MATCH(A4,[book1.xls]Stocks'!$B$10:$B$413,0)),"S",IF(ISNUMBER(MATCH(A4,'[book1.xls]Income
Securities'!$B$10:$B$413,0)),"FI",IF(ISNUMBER(MATCH(A4,'[book1.xls]Property
& Infrastructure'!$B$10:$B$413,0)),"PI","None")))

Opening the referenced book simultaneously makes it simpler to register the
formula as it "removes" the path from the formula. Leave it to Excel to
insert the path correctly when the referenced book is subsequently closed.

---

Thanks Max. i tried that
=IF(ISNUMBER(MATCH(A6,C:\Documents and Settings\Gillian Mason\My
Documents\[copy 2Share
Recommendations.xls]Stocks'!$B$10:$B$413,0)),"S",IF(ISNUMBER(MATCH(A6,
C:\Documents and Settings\Gillian Mason\My Documents\[copy 2Share
Recommendations.xls]Income
Securities'!$B$10:$B$413,0)),"FI",IF(ISNUMBER(MATCH(A6, C:\Documents and
Settings\Gillian Mason\My Documents\[copy 2Share Recommendations.xls]Property
& Infrastructure'!$B$10:$B$413,0)),"PI","None")))
but says there is something wrong w the path, even though i cant see what it
is. is it possible its just the position of my commas and apostrophies?

 

[Guest]

Max, check those single quote marks also - the leading one in the very first
MATCH() formula seems to be missing - needs one right after A4, to pair up
with the one just before the ! in that formula.

Thanks for correction. Yes, it should be a pair of single quotes for the
sheet: Stocks, or, the single quotes could be removed altogether for Stocks
(as tested here)

This part :

=IF(ISNUMBER(MATCH(A4,[book1.xls]Stocks'!$B$10:$B$413,0)),"S",

should read as:

=IF(ISNUMBER(MATCH(A4,[book1.xls]Stocks!$B$10:$B$413,0)),"S",

---