I have a workbook consisting of worksheets with the same layout by month (i.e, Jan 2012, Feb 2012). From that I wish to lookup across each of the worksheets and list all category levels and corresponding months for the three criteria in columns B, C and D which are drop down boxes.
List Based
<tbody>
</tbody>
<tbody>
</tbody>
<tbody>
</tbody>
Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
I was able to get the formula to work for the May 2012 worksheet. However, I need to do this for all worksheets in the named range MnthTabs. The above table shows the results I am looking for so it does not necessarily match the formulas shown. But the formula works for the one month I tested.
In a previous post, I received help which allowed me to sum all occurrences based on two criteria accross sheets:
Where MnthTabs is the list of worksheets, G7 is the criteria (Pictorial, Creative etc.) and E52 is the criteria for the persons name.
Perhaps some combination of the two formulas would work. Any ideas?
Excel 2003, native functions only.
List Based
| * | B | C | D | E | F |
| 7 | * | General | * | Category | * |
| 8 | Name | Category | Place | Level | Month |
| 9 | Tuco | Pictorial | 3rd Place | PI | Jan |
| 10 | * | * | * | PI | Apr |
| 11 | * | * | * | PI | May |
| 12 | * | * | * | #NUM! | * |
<tbody>
</tbody>
| Spreadsheet Formulas | ||||||||||
<tbody> </tbody> | ||||||||||
| Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER! |
<tbody>
</tbody>
| Data Validation in Spreadsheet | ||||||||||||||||||||
<tbody> </tbody> |
<tbody>
</tbody>
Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
I was able to get the formula to work for the May 2012 worksheet. However, I need to do this for all worksheets in the named range MnthTabs. The above table shows the results I am looking for so it does not necessarily match the formulas shown. But the formula works for the one month I tested.
In a previous post, I received help which allowed me to sum all occurrences based on two criteria accross sheets:
Code:
=SUMPRODUCT(--(T(OFFSET(INDIRECT("'"&MnthTabs&"'!D9:D100"),ROW(INDIRECT("D9:D100"))-9,,))=G$7),--(T(OFFSET(INDIRECT("'"&MnthTabs&"'!C9:C100"),ROW(INDIRECT("C9:C100"))-9,,))=$E52))Where MnthTabs is the list of worksheets, G7 is the criteria (Pictorial, Creative etc.) and E52 is the criteria for the persons name.
Perhaps some combination of the two formulas would work. Any ideas?
Excel 2003, native functions only.
