Hi Everyone,
I am hoping someone can help me out on this one. I have a table similar to below, only larger. Headers are dates usually 6 months or so, but each column is a week. The rows are items. This table is on one worksheet. On a separate sheet I want to have a dynamic monthly total sheet, there will be a drop down to select the month. From past history Excel doesn't like to recognize date formats when they are used in a table header, I will need to look for text "month" in any searches. I am sure there is a combination of sumifs and lookups, maybe a macro is easier.
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ITEM[/TD]
[TD]Oct 20 2018[/TD]
[TD]Oct 27 2018[/TD]
[TD]Nov 3 2018[/TD]
[TD]Nov 10 2018[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]6.01
[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]6.02[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]7.01[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]7.02[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[/TR]
</tbody>[/TABLE]
Example of what I am looking for:
If I chose Oct as the month this should be the result, it should populate a list with only items that actually have totals greater than 0
[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]MONTH[/TD]
[TD="align: center"]OCT[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]ITEM[/TD]
[TD="align: center"]MONTHLY TOTAL[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]6.01[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]6.02[/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]7.02[/TD]
[TD="align: center"]2[/TD]
[/TR]
</tbody>[/TABLE]
Thanks
I am hoping someone can help me out on this one. I have a table similar to below, only larger. Headers are dates usually 6 months or so, but each column is a week. The rows are items. This table is on one worksheet. On a separate sheet I want to have a dynamic monthly total sheet, there will be a drop down to select the month. From past history Excel doesn't like to recognize date formats when they are used in a table header, I will need to look for text "month" in any searches. I am sure there is a combination of sumifs and lookups, maybe a macro is easier.
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ITEM[/TD]
[TD]Oct 20 2018[/TD]
[TD]Oct 27 2018[/TD]
[TD]Nov 3 2018[/TD]
[TD]Nov 10 2018[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]6.01
[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]6.02[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]7.01[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]7.02[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[/TR]
</tbody>[/TABLE]
Example of what I am looking for:
If I chose Oct as the month this should be the result, it should populate a list with only items that actually have totals greater than 0
[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]MONTH[/TD]
[TD="align: center"]OCT[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]ITEM[/TD]
[TD="align: center"]MONTHLY TOTAL[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]6.01[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]6.02[/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]7.02[/TD]
[TD="align: center"]2[/TD]
[/TR]
</tbody>[/TABLE]
Thanks