mgabriel2490
New Member
- Joined
- Mar 1, 2016
- Messages
- 41
I have data that is pulled into a sheet that looks like below. I have made it a table since data is added to it, so I can reference the table columns and not update the ranges each time.
<tbody>
</tbody>
On another sheet/tab I want to have a summary for each day. The following Index/Match formula worked before making the Data into a table....
'=INDEX(Data!G$2:G$4591,MATCH(1,('Daily'!$A17=Data!$B$2:$B$4591)*('Daily Shop data'!$A$16=Data!$C$2:$C$4591),0))
My summary sheet looks like this...
<tbody>
</tbody>
I have tried this formula but cannot get it to work:
=INDEX(INDIRECT("data["&B10&"]"),MATCH(1,($A11="Pivot[Date]")*($A$10="pivot[area]"),0))
I want it to Index based on the summary page's column name that matches with the data table's column name, then match for whatever date (4-jan, 5-jan, etc), and also match for Sector A Summary.
Any help is greatly appreciated!
Date | Area | Product | Widget | Quota | Needles | All | a | b | c | d | e | f | g | h | i | Total | TestA | TestB | Per | % | DT |
4-Jan | a | 1 | ab | 20 | 100 | 345 | 0 | 30 | 0 | 0 | 0 | 0 | 0 | #N/A | 60 | 90 | 255 | 280.3738 | 24 | 110.0% | |
4-Jan | b | 2 | ac | 40 | 20 | 135 | 0 | 30 | 0 | 0 | 0 | 0 | 0 | #N/A | 0 | 30 | 105 | 70.01167 | 11 | 66.7% | |
4-Jan | c | 3 | ad | 60 | 1752.54 | 480 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | #N/A | 0 | 0 | 480 | 494.8348 | 219 | 103.1% | |
4-Jan | d | 4 | ae | 80 | 280 | 480 | 0 | 30 | 0 | 0 | 0 | 0 | 0 | #N/A | 60 | 90 | 390 | 420 | 43 | 107.7% | |
4-Jan | e | 5 | af | 20 | 128 | 480 | 0 | 0 | 0 | 0 | 20 | 0 | 0 | #N/A | 60 | 80 | 400 | 448.0747 | 19 | 112.0% | |
4-Jan | f | 6 | ag | 40 | 2600 | 260 | 0 | 0 | 0 | 0 | 25 | 0 | 40 | #N/A | 45 | 110 | 150 | 81.93277 | 1040 | 54.6% | |
4-Jan | g | 7 | ah | 60 | 130 | 480 | 0 | 15 | 0 | 0 | 0 | 0 | 30 | #N/A | 60 | 105 | 375 | 455.0758 | 21 | 121.4% | |
4-Jan | h | 8 | ai | 80 | 270 | 480 | 0 | 20 | 35 | 0 | 10 | 0 | 0 | #N/A | 60 | 125 | 355 | 405 | 46 | 114.1% | |
4-Jan | i | 9 | aj | 20 | 7419 | 480 | 0 | 0 | 0 | 0 | 70 | 0 | 30 | #N/A | 60 | 160 | 320 | 600.9721 | 1391 | 187.8% | |
4-Jan | j | 10 | ak | 40 | 317 | 600 | 35 | 20 | 20 | 0 | 45 | 45 | 30 | #N/A | 60 | 255 | 345 | 317 | 55 | 91.9% | |
4-Jan | k | 11 | al | 60 | 40000 | 480 | 0 | 48 | 0 | 0 | 0 | 0 | 30 | #N/A | 60 | 138 | 342 | 336.0403 | 7018 | 98.3% | |
4-Jan | l | 12 | am | 80 | 1804.61 | 480 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | #N/A | 0 | 0 | 480 | 509.5369 | 226 | 106.2% | |
4-Jan | m | 13 | an | 20 | 28391 | 480 | 0 | 53 | 0 | 19 | 0 | 0 | 60 | #N/A | 60 | 192 | 288 | 238.513 | 5915 | 82.8% | |
4-Jan | n | 14 | ao | 40 | 6100 | 480 | 0 | 20 | 0 | 0 | 30 | 0 | 30 | #N/A | 60 | 140 | 340 | 494.1272 | 1076 | 145.3% | |
4-Jan | 0 | 0 | 0 | - | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | #N/A | 0 | 0 | - | - | - | - | - |
4-Jan | 0 | 0 | 0 | - | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | #N/A | 0 | 0 | - | - | - | - | - |
4-Jan | 0 | 0 | 0 | - | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | #N/A | 0 | 0 | - | - | - | - | - |
4-Jan | 0 | 0 | 0 | - | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | #N/A | 0 | 0 | - | - | - | - | - |
4-Jan | 0 | 0 | 0 | - | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | #N/A | 0 | 0 | - | - | - | - | - |
4-Jan | 0 | 0 | 0 | - | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | #N/A | 0 | 0 | - | - | - | - | - |
4-Jan | 0 | 0 | 0 | - | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | #N/A | 0 | 0 | - | - | - | - | - |
4-Jan | 0 | 0 | 0 | - | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | #N/A | 0 | 0 | - | - | - | - | - |
4-Jan | 0 | 0 | 0 | - | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | #N/A | 0 | 0 | - | - | - | - | - |
4-Jan | 0 | 0 | 0 | - | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | #N/A | 0 | 0 | - | - | - | - | - |
4-Jan | Sector A Summary | 89312.15 | 6140 | 35 | 266 | 55 | 19 | 200 | 45 | 250 | #N/A | 645 | 1515 | 4625 | 5151.493 | 1221.67 | 107.27% | 24.67% |
<tbody>
</tbody>
On another sheet/tab I want to have a summary for each day. The following Index/Match formula worked before making the Data into a table....
'=INDEX(Data!G$2:G$4591,MATCH(1,('Daily'!$A17=Data!$B$2:$B$4591)*('Daily Shop data'!$A$16=Data!$C$2:$C$4591),0))
My summary sheet looks like this...
Sector A Summary | Needles | All | a | b | c | d | e | f | g | h | i | Total | TestA | TestB | Per | % | DT |
4-Jan | |||||||||||||||||
5-Jan | |||||||||||||||||
6-Jan | |||||||||||||||||
7-Jan |
<tbody>
</tbody>
I have tried this formula but cannot get it to work:
=INDEX(INDIRECT("data["&B10&"]"),MATCH(1,($A11="Pivot[Date]")*($A$10="pivot[area]"),0))
I want it to Index based on the summary page's column name that matches with the data table's column name, then match for whatever date (4-jan, 5-jan, etc), and also match for Sector A Summary.
Any help is greatly appreciated!