Lookup to compile information

dhubz

New Member
Joined
Sep 10, 2014
Messages
48
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Last edited:
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top