Formula for lookup value from multipule worksheet in summary sheet where have sheet list

[emran06]

Dear Friends,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I have one workbook. It has 100 worksheets in deferent name. One is summary sheet of them. In the summary sheet listed all worksheets name in column A1 to A100.<o></o>
<o></o>
Now I want every worksheets data value (grand total) in column B1 to B100 against listed worksheets name.<o></o>
<o></o>
Please let me know what the formula for lookup value from multiple worksheet in summary sheet with a single command where have sheet list?<o></o>

 

[Aladin Akyurek]

Dear Friends,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I have one workbook. It has 100 worksheets in deferent name. One is summary sheet of them. In the summary sheet listed all worksheets name in column A1 to A100.<o></o>
<o></o>
Now I want every worksheets data value (grand total) in column B1 to B100 against listed worksheets name.<o></o>
<o></o>
Please let me know what the formula for lookup value from multiple worksheet in summary sheet with a single command where have sheet list?<o></o>

Which range from all those sheets are you summing is not given...

B1, just enter and copy down:

=SUM(INDIRECT("'"&A1&"'!E2:E100"))

which sums E2 to E100 from a given sheet. Adjust the range to suit.

 

[emran06]

Dear Aladin,
Thank you very much for your quick response. I have applied your formula and it works nicely. but the formula works only one row for one worksheet. If i copy for next sheet it does not work. Pls help me.

My requirements like this-

<table border="0" cellpadding="0" cellspacing="0" width="239"><col style="width: 95pt;" width="126"> <col style="width: 85pt;" width="113"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 95pt;" width="126" height="17">Sheet Name</td> <td style="width: 85pt;" width="113">Sheet Sum</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Kamal</td> <td align="right">50</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Rahim</td> <td align="right">45</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Azad</td> <td align="right">35</td> </tr> </tbody></table>

 

[Aladin Akyurek]

Dear Aladin,
Thank you very much for your quick response. I have applied your formula and it works nicely. but the formula works only one row for one worksheet. If i copy for next sheet it does not work. Pls help me.

My requirements like this-

<TABLE border=0 cellSpacing=0 cellPadding=0 width=239><COLGROUP><COL style="WIDTH: 95pt" width=126><COL style="WIDTH: 85pt" width=113><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="WIDTH: 95pt; HEIGHT: 12.75pt" height=17 width=126>Sheet Name</TD><TD style="WIDTH: 85pt" width=113>Sheet Sum</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="HEIGHT: 12.75pt" height=17>Kamal</TD><TD align=right>50</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="HEIGHT: 12.75pt" height=17>Rahim</TD><TD align=right>45</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="HEIGHT: 12.75pt" height=17>Azad</TD><TD align=right>35</TD></TR></TBODY></TABLE>

If A1 is Kamal...

=SUM(INDIRECT("'"&A1&"'!E2:E100"))

will sum E2:E100 from the sheet called Kamal.

If you copy it down, it will do the same for Rahim and Azad. The formula picks out E2:E100 from each sheet.

 

[emran06]

Dear Aladin Akyurek,
Many thanks again for your cordial solution. I have face another problem during apply the formula. I have used the formula =SUM(INDIRECT("'"&A1&"'!E2:E100")) as per your instruction. it is OK but it has need add a conditional term with the formula. Above formula will work if column G11 is getter then column D11 or less then that of all sheet. I mean if one column value (date) is less or getter then another column value (date) then =SUM(INDIRECT("'"&A1&"'!E2:E100")). is it possible?

Please help me. You cooperation will be highly appreciated.

Thanks in advance.

Emran

 

[Aladin Akyurek]

Dear Aladin Akyurek,
Many thanks again for your cordial solution. I have face another problem during apply the formula. I have used the formula =SUM(INDIRECT("'"&A1&"'!E2:E100")) as per your instruction. it is OK but it has need add a conditional term with the formula. Above formula will work if column G11 is getter then column D11 or less then that of all sheet. I mean if one column value (date) is less or getter then another column value (date) then =SUM(INDIRECT("'"&A1&"'!E2:E100")). is it possible?

Please help me. You cooperation will be highly appreciated.

Thanks in advance.

Emran

Exemple:

=SUMPRODUCT(
    INDIRECT("'"&A1&"'!E2:E100"),
    --(INDIRECT("'"&A1&"'!D2:D100") > INDIRECT("'"&A1&"'!G2:G100"))

which reads: Sum values in E whenever D > G.

 

[emran06]

Dear Sir,
I am very much glad that I became a member of a resourceful website like Mrexcel.com where find solution of critical excelling very quickly. Then thanks to you for reply me without feel bother. Sir, I have applied your formula, the result of the formula is not exactly that I want. My requirement is-
<table border="0" cellpadding="0" cellspacing="0" width="291"><col style="width: 69pt;" width="92"> <col style="width: 75pt;" width="100"> <col style="width: 74pt;" width="99"> <tbody><tr style="height: 36pt;" height="48"> <td class="xl30" style="height: 36pt; width: 69pt;" width="92" height="48">Sheet Name</td> <td class="xl27" style="border-left: medium none; width: 75pt;" width="100">Deliver Within Time</td> <td class="xl27" style="border-left: medium none; width: 74pt;" width="99">Late Delivery</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl30" style="height: 12.75pt; border-top: medium none;" height="17">Aarti</td> <td class="xl28" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl28" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl30" style="height: 15pt; border-top: medium none;" height="20">ACG</td> <td class="xl29" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl29" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl30" style="height: 15pt; border-top: medium none;" height="20">Acid</td> <td class="xl29" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl28" style="border-top: medium none; border-left: medium none;"> </td> </tr> </tbody></table>
If the sheet name "Aarti" delivery date (column D11) is getter then received date (G11) then count/sum (in appropriate) rows D11:D100 (that rows filled up with date).

Pls help me.

 

[Aladin Akyurek]

Dear Sir,
I am very much glad that I became a member of a resourceful website like Mrexcel.com where find solution of critical excelling very quickly. Then thanks to you for reply me without feel bother. Sir, I have applied your formula, the result of the formula is not exactly that I want. My requirement is-
<table border="0" cellpadding="0" cellspacing="0" width="291"><col style="width: 69pt;" width="92"> <col style="width: 75pt;" width="100"> <col style="width: 74pt;" width="99"> <tbody><tr style="height: 36pt;" height="48"> <td class="xl30" style="height: 36pt; width: 69pt;" width="92" height="48">Sheet Name</td> <td class="xl27" style="border-left: medium none; width: 75pt;" width="100">Deliver Within Time</td> <td class="xl27" style="border-left: medium none; width: 74pt;" width="99">Late Delivery</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl30" style="height: 12.75pt; border-top: medium none;" height="17">Aarti</td> <td class="xl28" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl28" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl30" style="height: 15pt; border-top: medium none;" height="20">ACG</td> <td class="xl29" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl29" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl30" style="height: 15pt; border-top: medium none;" height="20">Acid</td> <td class="xl29" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl28" style="border-top: medium none; border-left: medium none;"> </td> </tr> </tbody></table>
If the sheet name "Aarti" delivery date (column D11) is getter then received date (G11) then count/sum (in appropriate) rows D11:D100 (that rows filled up with date).

Pls help me.

I don't know what "getter" is. You mean greater?

A2: Aarti

B2, just enter and copy down:

=SUMPRODUCT(--((INDIRECT("'"&A1&"'!D11:D100") > INDIRECT("'"&A1&"'!G11:G100")))

which counts every D-value (date) that is greater than the coressponding G-value.