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!

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!

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!

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 -