DRSteele
Well-known Member
- Joined
- Mar 31, 2015
- Messages
- 2,640
- Office Version
- 365
- Platform
- Windows
Can someone please help me?
I wish to fill in the missing data for cells where there is a weekend, a holiday or an NA error. I need a formula to get it done. Like this, where the blue stuff in columnO I filled in from the prior valid date by typing it:
I wish to fill in the missing data for cells where there is a weekend, a holiday or an NA error. I need a formula to get it done. Like this, where the blue stuff in columnO I filled in from the prior valid date by typing it:
Book1 | ||||||||
---|---|---|---|---|---|---|---|---|
M | N | O | P | Q | R | |||
11 | DATA | |||||||
12 | date | value | desired1 | date | desired2 | |||
13 | 12/23/2022 | $ 19,506.65 | $ 19,506.65 | 12/23/2022 | $ 19,506.65 | |||
14 | 12/27/2022 | #N/A | $ 19,506.65 | 12/24/2022 | $ 19,506.65 | |||
15 | 12/28/2022 | $ 19,284.10 | $ 19,284.10 | 12/25/2022 | $ 19,506.65 | |||
16 | 12/29/2022 | $ 19,485.89 | $ 19,485.89 | 12/26/2022 | $ 19,506.65 | |||
17 | 12/30/2022 | $ 19,384.92 | $ 19,384.92 | 12/27/2022 | $ 19,506.65 | |||
18 | 1/2/2023 | #N/A | $ 19,384.92 | 12/28/2022 | $ 19,284.10 | |||
19 | 1/3/2023 | #N/A | $ 19,384.92 | 12/29/2022 | $ 19,485.89 | |||
20 | 1/4/2023 | $ 19,588.83 | $ 19,588.83 | 12/30/2022 | $ 19,384.92 | |||
21 | 1/5/2023 | $ 19,506.84 | $ 19,506.84 | 12/31/2022 | $ 19,384.92 | |||
22 | 1/6/2023 | $ 19,814.51 | $ 19,814.51 | 1/1/2023 | $ 19,384.92 | |||
23 | 1/9/2023 | $ 19,857.07 | $ 19,857.07 | 1/2/2023 | $ 19,384.92 | |||
24 | 1/10/2023 | $ 19,898.86 | $ 19,898.86 | 1/3/2023 | $ 19,384.92 | |||
25 | 1/4/2023 | $ 19,588.83 | ||||||
26 | 1/5/2023 | $ 19,506.84 | ||||||
27 | 1/6/2023 | $ 19,814.51 | ||||||
28 | 1/7/2023 | $ 19,814.51 | ||||||
29 | 1/8/2023 | $ 19,814.51 | ||||||
30 | 1/9/2023 | $ 19,857.07 | ||||||
31 | 1/10/2023 | $ 19,898.86 | ||||||
32 | 1/11/2023 | $ 19,898.86 | ||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
N14,N18:N19 | N14 | =NA() |
R13:R32 | R13 | =XLOOKUP(Q13,$M$13:$M$24,$O$13:$O$24,,-1) |