Nested If problem
Friends,
I want to evaulate 9 cells of different workbooks to check if they are empty and take value of the cell which is non empty(assuming only one cell contains value) into my master sheet. I will than plan to drag expand this cell to map all the cells accross the 9 workbooks. I can use this using nested if, however excel 2003 will allow for only 7 levels. Here is what I plan to do: =if(isempty('C:\[wrksheet1.xls]sheet1'!A1)=false,''C:\[wrksheet1.xls]sheet1'!A1,if(isempty('C:\[wrksheet2.xls]sheet1'!A1=false,'C:\[wrksheet2.xls]sheet1'!A1, ............if(isempty('C:\[wrksheet9.xls]sheet1'!A1=false,'C:\[wrksheet9.xls]sheet1'!A1))))))))) Is there any other way to do this. Thanks! |
Nested If problem
If you can guarantee that all but one cell is empty, then you can just
concatenate them all together - no need for IFs, i.e.: ='C:\[wrksheet1.xls]sheet1*'!A1 & 'C:\[wrksheet2.xls]shee*t1'!A1 & ... ..........'C:\[wrksheet9.x*ls]sheet1'!A1 If you really want to use IFs, then you can concatenate those together to avoid nesting limits: =if('C:\[wrksheet1.xls]sheet1'!A1)="","",'C:\[wrksheet1.xls]sheet1*'! A1) & if('C:\[wrksheet2.xls]sheet1'!A1="","",'C:\[wrksheet2.xls]shee*t1'!A1) & ... .............if('C:\[wrksheet9.xls]sheet1'!A1="","",'C:\[wrksheet9.x*ls] sheet1'!A1) Hope this helps. Pete On Aug 20, 8:17*pm, Harish Sharma wrote: Friends, I want to evaulate 9 cells of different workbooks to check if they are empty and take value of the cell which is non empty(assuming only one cell contains value) into my master sheet. I will than plan to drag expand this cell to map all the cells accross the 9 workbooks. I can use this using nested if, however excel 2003 will allow for only 7 levels. Here is what I plan to do: =if(isempty('C:\[wrksheet1.xls]sheet1'!A1)=false,''C:\[wrksheet1.xls]sheet1*'!A1,if(isempty('C:\[wrksheet2.xls]sheet1'!A1=false,'C:\[wrksheet2.xls]shee*t1'!A1, ............if(isempty('C:\[wrksheet9.xls]sheet1'!A1=false,'C:\[wrksheet9.x*ls]sheet1'!A1))))))))) Is there any other way to do this. Thanks! |
Nested If problem
This was really a great logic and simple answer to my problem.
Thank you so much. "Pete_UK" wrote: If you can guarantee that all but one cell is empty, then you can just concatenate them all together - no need for IFs, i.e.: ='C:\[wrksheet1.xls]sheet1Â*'!A1 & 'C:\[wrksheet2.xls]sheeÂ*t1'!A1 & ... ..........'C:\[wrksheet9.xÂ*ls]sheet1'!A1 If you really want to use IFs, then you can concatenate those together to avoid nesting limits: =if('C:\[wrksheet1.xls]sheet1'!A1)="","",'C:\[wrksheet1.xls]sheet1Â*'! A1) & if('C:\[wrksheet2.xls]sheet1'!A1="","",'C:\[wrksheet2.xls]sheeÂ*t1'!A1) & ... .............if('C:\[wrksheet9.xls]sheet1'!A1="","",'C:\[wrksheet9.xÂ*ls] sheet1'!A1) Hope this helps. Pete On Aug 20, 8:17 pm, Harish Sharma wrote: Friends, I want to evaulate 9 cells of different workbooks to check if they are empty and take value of the cell which is non empty(assuming only one cell contains value) into my master sheet. I will than plan to drag expand this cell to map all the cells accross the 9 workbooks. I can use this using nested if, however excel 2003 will allow for only 7 levels. Here is what I plan to do: =if(isempty('C:\[wrksheet1.xls]sheet1'!A1)=false,''C:\[wrksheet1.xls]sheet1Â*'!A1,if(isempty('C:\[wrksheet2.xls]sheet1'!A1=false,'C:\[wrksheet2.xls]sheeÂ*t1'!A1, ............if(isempty('C:\[wrksheet9.xls]sheet1'!A1=false,'C:\[wrksheet9.xÂ*ls]sheet1'!A1))))))))) Is there any other way to do this. Thanks! |
Nested If problem
You're welcome, Harish - thanks for feeding back.
Pete On Aug 21, 1:20*pm, Harish Sharma wrote: This was really a great logic and simple answer to my problem. Thank you so much. "Pete_UK" wrote: If you can guarantee that all but one cell is empty, then you can just concatenate them all together - no need for IFs, i.e.: ='C:\[wrksheet1.xls]sheet1*'!A1 & 'C:\[wrksheet2.xls]shee*t1'!A1 & *... ..........'C:\[wrksheet9.x*ls]sheet1'!A1 If you really want to use IFs, then you can concatenate those together to avoid nesting limits: =if('C:\[wrksheet1.xls]sheet1'!A1)="","",'C:\[wrksheet1.xls]sheet1*'! A1) & if('C:\[wrksheet2.xls]sheet1'!A1="","",'C:\[wrksheet2.xls]shee*t1'!A1) & ... .............if('C:\[wrksheet9.xls]sheet1'!A1="","",'C:\[wrksheet9.x*ls] sheet1'!A1) Hope this helps. Pete On Aug 20, 8:17 pm, Harish Sharma wrote: Friends, I want to evaulate 9 cells of different workbooks to check if they are empty and take value of the cell which is non empty(assuming only one cell contains value) into my master sheet. I will than plan to drag expand this cell to map all the cells accross the 9 workbooks. I can use this using nested if, however excel 2003 will allow for only 7 levels. Here is what I plan to do: =if(isempty('C:\[wrksheet1.xls]sheet1'!A1)=false,''C:\[wrksheet1.xls]sheet1**'!A1,if(isempty('C:\[wrksheet2.xls]sheet1'!A1=false,'C:\[wrksheet2.xls]she*e*t1'!A1, ............if(isempty('C:\[wrksheet9.xls]sheet1'!A1=false,'C:\[wrksheet9.x**ls]sheet1'!A1))))))))) Is there any other way to do this. Thanks!- Hide quoted text - - Show quoted text - |
All times are GMT +1. The time now is 10:56 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com