Indirect formula to extract data

[sachar]

Dear All,

With reference to the attach sample file, with help of Sumif formula, I got the result at the sheet no 2 in Column ”B”. May I know, how can I get the same with the help of “Indirect formula” by an extract from worksheet name with the condition of total “Linen”?

Please help me.

 

[Monty]

Hello Sachar.

Let's break apart the formula you see in the screenshot and understand.

As you know, the usual way to reference another sheet in Excel is writing the sheet's name followed by the exclamation mark and a cell / range reference, like SheetName!Range. Since a sheet name often contains a space(s), you'd better enclose it (the name, not a space : ) in single quotes to prevent an error, for example 'My Sheet!'$A$1.

And now, all you have to do is to enter the sheet name in one cell, the cell address in another, concatenate them in a text string, and feed that string to the INDIRECT function. Remember that in a text string, you have to enclose each element other than a cell address or number in double quotes and link all elements together using the concatenation operator (&).

Given the above, we get the following pattern:

INDIRECT("'" & Sheet's name & "'!" & Cell to pull data from)

Hope you like it...Let me know any challenges.

 

[bosco_yip]

Try.....

Sum of "Linen" in 3 worksheets.

In C2 formula :

=SUMPRODUCT(SUMIF(INDIRECT("'"&A2:A4&"'!B6:B36"),B1,INDIRECT("'"&A2:A4&"'!C6:C36")))

Regards
Bosco

 

[sachar]

Dear Monty & Bosco,

Thanks to both are help me to solved my problems.