Lookup to compile information

[dhubz]

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

 

[Marcelo Branco]

Maybe...

Sheet1

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

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="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

6.01​

[/TD]
[TD]

5​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

6.02​

[/TD]
[TD]

3​

[/TD]
[TD]

1​

[/TD]
[TD]

0​

[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

7.01​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

4​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

7.02​

[/TD]
[TD]

0​

[/TD]
[TD]

2​

[/TD]
[TD]

1​

[/TD]
[TD]

2​

[/TD]
[/TR]
</tbody>[/TABLE]


Sheet2

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

MONTH​

[/TD]
[TD]

OCT​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

ITEM​

[/TD]
[TD]

MONTHLY TOTAL​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

6.01​

[/TD]
[TD]

5​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

6.02​

[/TD]
[TD]

4​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

7.02​

[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Array formula in A4 copied down
=IFERROR(INDEX(Sheet1!$A$2:$A$5,SMALL(IF(MMULT(ISNUMBER(SEARCH($B$1,Sheet1!$B$1:$E$1))*Sheet1!$B$2:$E$5,TRANSPOSE(COLUMN(Sheet1!$B$1:$E$1)^0)),ROW(Sheet1!$A$2:$A$5)-ROW(Sheet1!$A$2)+1),ROWS(A$4:A4))),"")
Ctrl+Shift+Enter, not just Enter

Array formula in B4 copied down
=IF(A4="","",SUM(IF(ISNUMBER(SEARCH($B$1,Sheet1!$B$1:$E$1)),IF(Sheet1!$A$2:$A$5=A4,Sheet1!$B$2:$E$5))))
Ctrl+Shift+Enter, not just Enter

Hope this helps

M.

 

[dhubz]

Hi M.

Were you able to get this to function properly. I've copied and pasted your formula, used Crtl+Shft+Enter. But it doesn't work. I've tried it on a new workbook with the exact example, but nothing. No errors just blank. When I manually enter the item number on Sheet2! it sums to 0. I can read the formula, and I see what it should be doing but it doesn't seem to work. I am sure I am missing something
Thanks
D

 

[Marcelo Branco]

Hi M.

Were you able to get this to function properly. I've copied and pasted your formula, used Crtl+Shft+Enter. But it doesn't work. I've tried it on a new workbook with the exact example, but nothing. No errors just blank. When I manually enter the item number on Sheet2! it sums to 0. I can read the formula, and I see what it should be doing but it doesn't seem to work. I am sure I am missing something
Thanks
D

Be sure to confirm the formulas with Ctrl+Shift+Enter simultaneously that is:
1. type in the formula (or copy/paste)
2. With the cursor inside the formula bar keep pressed the Ctrl and Shift keys and hit Enter.
3. If these steps are performed properly, Excel automatically wraps the formula with curly braces. Check.

Also check the sheet names and the ranges carefully.
I have tested the formulas and everything worked perfectly.

M.

 

[Marcelo Branco]

Question
Are the dates in B1:E1 text or real dates (numbers)?
I've assumed they are text.
What about the drop down in B1 in Sheet2, text or real date?

M.

 

[dhubz]

Question
Are the dates in B1:E1 text or real dates (numbers)?
I've assumed they are text.
What about the drop down in B1 in Sheet2, text or real date?

M.

Hi Marcelo,

I am clearly doing something wrong. The dates are text, the drop down is text. I have made just a test sheet for your formula, that matches exactly to the sample. I've copy/pasted the formula, ctrl+shift+enter for the array. I've actually tried it by removing the drop down and putting in "July" text manually, and removing the numbers from the header date text, and just put "June" and "July". What is the ^0 in the equation?
Thanks for the help
Dave

 

[Marcelo Branco]

Please show us the new data sample containing only the months in B1:E1 (June, July etc), without the numbers, along with the expected results.

M.