Nested If problem

[Harish Sharma]

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!

 

[Pete_UK]

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

 

[Harish Sharma]

This was really a great logic and simple answer to my problem.
Thank you so much.

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

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!

 

[Pete_UK]

You're welcome, Harish - thanks for feeding back.

Pete

This was really a great logic and simple answer to my problem.
Thank you so much.

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

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 -