Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Making a table for multiple sheets Excel 2007
I have a workbook with 7 or more sheets of data and a sheet named table.
I have an IF statement in column K which looks at the date of the activity, (H2<0,"Over Due",0). In column L is the activity, Title, Design, Purchase, install, etc. On the sheet Table I have a table with activity in cloumn A2:A100 and sheet names in row B2 : B8, this is my table. I need to look in column L (All Sheets we data) if Over Due is found then my table will display the results in the relevent section of the table. Please can you help? General Section1 Section2 Title 3 2 5 Design 1 3 4 Purchase 2 2 1 I would appreciate a formula and VBA Code. Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Making a table for multiple sheets Excel 2007
I don't think you need a macro. You can use Indirect to get the sheet
addresses and sumproduct to look at two columns on each sheet. I think you meant in your instructions that the sheet names are in B1:H1 (not B2:B8). Put this formula in cell b2 and copy to all locations in your table =SUMPRODUCT(--(INDIRECT(B$1&"!K1:K1000")="Over Due"),--(INDIRECT(B$1&"!L1:L1000")=$A2)) B1 will be the sheet name K1:K1000 and L1:L1000 is the data cell on each data sheet A2 is the activity. "REVILO" wrote: I have a workbook with 7 or more sheets of data and a sheet named table. I have an IF statement in column K which looks at the date of the activity, (H2<0,"Over Due",0). In column L is the activity, Title, Design, Purchase, install, etc. On the sheet Table I have a table with activity in cloumn A2:A100 and sheet names in row B2 : B8, this is my table. I need to look in column L (All Sheets we data) if Over Due is found then my table will display the results in the relevent section of the table. Please can you help? General Section1 Section2 Title 3 2 5 Design 1 3 4 Purchase 2 2 1 I would appreciate a formula and VBA Code. Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Making a table for multiple sheets Excel 2007
Yes you are correct row 1 has sheet names.
The formula works a treat. -- Thank you for your time. Revilo. "Joel" wrote: I don't think you need a macro. You can use Indirect to get the sheet addresses and sumproduct to look at two columns on each sheet. I think you meant in your instructions that the sheet names are in B1:H1 (not B2:B8). Put this formula in cell b2 and copy to all locations in your table =SUMPRODUCT(--(INDIRECT(B$1&"!K1:K1000")="Over Due"),--(INDIRECT(B$1&"!L1:L1000")=$A2)) B1 will be the sheet name K1:K1000 and L1:L1000 is the data cell on each data sheet A2 is the activity. "REVILO" wrote: I have a workbook with 7 or more sheets of data and a sheet named table. I have an IF statement in column K which looks at the date of the activity, (H2<0,"Over Due",0). In column L is the activity, Title, Design, Purchase, install, etc. On the sheet Table I have a table with activity in cloumn A2:A100 and sheet names in row B2 : B8, this is my table. I need to look in column L (All Sheets we data) if Over Due is found then my table will display the results in the relevent section of the table. Please can you help? General Section1 Section2 Title 3 2 5 Design 1 3 4 Purchase 2 2 1 I would appreciate a formula and VBA Code. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table with multiple sheets in Excel 2000 | Excel Discussion (Misc queries) | |||
Excel 2007: Create a pivot table where data is in multible sheets | Excel Worksheet Functions | |||
Does anyone have any good references for making excel sheets into webpages? | Excel Discussion (Misc queries) | |||
Pivot Table from multiple sheets | Excel Discussion (Misc queries) | |||
excel newb needs some help making multiple sheets communicate for ecommerce project! | Excel Discussion (Misc queries) |