kgarland17
New Member
- Joined
- Jun 17, 2021
- Messages
- 8
- Office Version
- 365
- Platform
- Windows
I have a database that has invoicing data and booking, but the booking is missing a crucial piece of data that doesn't become active until it is invoiced.
So I created a pivot table of invoiced data to attempt to be able to get a formula into the booking portion of database. Perhaps this isn't the way to go? Not sure.
=IF(INDEX(Pivot Table!$A$2:$S$8,MATCH(SourceDB!a2,Pivot Table!$A$3:$A$8,0),19)=0,INDEX(Pivot Table!$A$3:$S$8,MATCH(SourceDB!a2,Pivot Table!$A$2:$a$8,0),18),INDEX(Cost_Book_Gap!$A$3:$S$8,MATCH(SourceDB!a2,Pivot Table!$A$2:$A$8,0),19))
Is there any easier way/better formula to go about this?
Data to pull last non-zero data point by category
So I created a pivot table of invoiced data to attempt to be able to get a formula into the booking portion of database. Perhaps this isn't the way to go? Not sure.
=IF(INDEX(Pivot Table!$A$2:$S$8,MATCH(SourceDB!a2,Pivot Table!$A$3:$A$8,0),19)=0,INDEX(Pivot Table!$A$3:$S$8,MATCH(SourceDB!a2,Pivot Table!$A$2:$a$8,0),18),INDEX(Cost_Book_Gap!$A$3:$S$8,MATCH(SourceDB!a2,Pivot Table!$A$2:$A$8,0),19))
Is there any easier way/better formula to go about this?
| Invoice Date | Category | need formula in cells below | |||
6/11/2021 | ######## | 6/14/2021 | 1113 | ||
6/11/2021 | ######## | 6/14/2021 | 2223 | ||
4/22/2021 | ######## | 6/16/2021 | 2224 | ||
2/17/2021 | ######## | 6/25/2021 | 2224 | ||
4/5/2021 | ######## | 6/30/2021 | 2223 | ||
4/30/2021 | 7/2/2021 | 7/2/2021 | 1114 | ||
1/6/2021 | ######## | 7/14/2021 | 1112 | ||
6/8/2021 | 8/2/2021 | 8/2/2021 | 2222 | ||
6/2/2021 | ######## | 8/12/2021 | 1113 | ||
3/31/2021 | ######## | 11/1/2021 | 2223 |
Data to pull last non-zero data point by category
| 2020 | 2021 | |||||||||||||||||
| Category | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | Jan | Feb | Mar | Apr | May | Jun |
| 1111 | 0 | 0 | 273.77 | 0 | 0 | 0 | 0 | 0 | 0 | 274.32 | 0 | 0 | 0 | 0 | 0 | 0 | 291.92 | 0 |
| 1112 | 253.11 | 0 | 264.41 | 265.8372 | 266.84 | 266.8555 | 288.67 | 293.67 | 280.9331 | 0 | 293.67 | 0 | 285.3423 | 284.3644 | 289.3794 | 290.54 | 290.14 | 292.28 |
| 1113 | 196.63 | 197.85 | 197.31 | 199.03 | 199.89 | 0 | 199.9 | 0 | 228.58 | 0 | 0 | 225.12 | 219.25 | 219.3067 | 219.2574 | 219.85 | 217.98 | 0 |
| 2222 | 0 | 0 | 0 | 0 | 0 | 125.61 | 0 | 135.83 | 0 | 0 | 0 | 135.99 | 0 | 0 | 136.0933 | 0 | 0 | 0 |
| 2223 | 0 | 0 | 121.6067 | 121.75 | 121.07 | 122.9062 | 125.79 | 135.181 | 134.9914 | 133.886 | 133.0498 | 133.1605 | 134.6252 | 133.0939 | 133.1253 | 134.1776 | 133.4153 | 133.033 |
| 2224 | 0 | 0 | 120.88 | 0 | 120.5004 | 120.414 | 120.04 | 135.3226 | 135.939 | 133.8736 | 132.7633 | 132.7174 | 132.706 | 132.84 | 132.9839 | 132.9091 | 132.9527 | 132.62 |
