Forum Discussion
Asaf_AM
Apr 18, 2023Copper Contributor
look for a specific date from table of ranges of dates
Hello everyone and thanks from advanced.
I need to look for a specific date (receiving date) from other table with ranges of dates (each month divided for 2) and take the value of the cell output cell of this period (lead time).
receiving date lead time
30/03/2023 | |
21/03/2023 | |
period from to lead time
1 | 01/01/2023 | 15/01/2023 | 40 |
2 | 16/01/2023 | 31/01/2023 | 40 |
3 | 01/02/2023 | 14/02/2023 | 38 |
4 | 15/02/2023 | 28/02/2023 | 33 |
5 | 01/03/2023 | 15/03/2023 | 32 |
6 | 16/03/2023 | 31/03/2023 | 32 |
7 | 01/04/2023 | 15/04/2023 | 28 |
8 | 16/04/2023 | 30/04/2023 | 26 |
9 | 01/05/2023 | 15/05/2023 | 24 |
10 | 16/05/2023 | 31/05/2023 | 21 |
I tested the workaround suggested by SergeiBaklan and this did work for me. It appears Pivot Charts, without a Pivot Table, using data directly from the data model have a visual bug with refreshing. The way around it is to rebuild the chart with a pivot table as I don't believe there's anyway to attach a pivot table to a pivot chart after the fact.
Although there's a workaround I still believe this is a visual bug. To emphasise this I rebuilt the workbook showing the issue (attached).
Two charts from the data model, with the exact same settings except one has a pivot table associated with it. Changing the source data and pressing Refresh All causes only the bottom chart to visually update:
Closing and reopening the workbook, modifying a filter or ticking defer layout update and then pressing update will all cause the top chart to visually update and show the same as the bottom chart. This is clearly not the intended functionality.
4 Replies
Sort By
- LorenzoSilver Contributor
Glad we could help & you have a solution
Next time(s) don't miss the 'Mark as solution' link at the bottom of the replies you get - HansVogelaar was kind enough to do it on your behalf but has more important things to do as a key contributor to this forum - Thanks & nice EOD
Let's say that the table with the ranges is on a sheet named Data in A2:D11 (with headers in row 1).
And the receiving dates are in A2 and down on another sheet.
Enter the following formula in B2, then fill down:
=IFERROR(VLOOKUP(A2,Data!$B$2:$D$11,3),"")