| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
return value from other spreadsheet
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
return value from other spreadsheet
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. "Micayla Bergen" wrote: 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
return value from other spreadsheet
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(MATC H(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 .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Micayla Bergen" wrote: 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
return value from other spreadsheet
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:\Docume nts and Settings\Gillian Mason\My Documents\[copy 2Share Recommendations.xls]Income Securities'!$B$10:$B$413,A4,0),"FI",IF(MATCH('C:\D ocuments 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. :o( "JLatham" wrote: 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. "Micayla Bergen" wrote: 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
return value from other spreadsheet
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(MATC H(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? "Max" wrote: 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(MATC H(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 .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Micayla Bergen" wrote: 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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
return value from other spreadsheet
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 on the situation) will be interpreted by the system as an attempt to concatenate two strings, usually with failure in situations like this one. "Micayla Bergen" wrote: 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(MATC H(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? "Max" wrote: 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(MATC H(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 .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Micayla Bergen" wrote: 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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
return value from other spreadsheet
"Micayla Bergen" wrote:
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 .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
return value from other spreadsheet
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(MATC H(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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Micayla Bergen" wrote: 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(MATC H(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? |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
return value from other spreadsheet
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. "Max" wrote: 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(MATC H(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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Micayla Bergen" wrote: 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(MATC H(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? |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
return value from other spreadsheet
"JLatham" wrote:
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", -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Matching and Moving Data From One Spreadsheet to Another? | Excel Discussion (Misc queries) | |||
| compare two spreadsheets and return a value | Excel Worksheet Functions | |||
| Copy From One Spreadsheet To Another Spreadsheet | Excel Discussion (Misc queries) | |||
| Displaying contents of cell on different spreadsheet | Excel Discussion (Misc queries) | |||
| Huge problem with "if" formula's | Excel Discussion (Misc queries) | |||
Linear Mode
