Help with referencing column data of a table depending on todays date.

1. Setup for PV Calculation
Let's assume the table data starts in cell A1:

Column G: Last Month PV
Column H: Current Month PV
Column I: Delta PV
2. Determine PV for Last Month
To get the PV for the last month, use INDEX and MATCH to find the value corresponding to the last month's date:

excel
Copy code
=INDEX($L$1:$P$1, MATCH(DATE(YEAR(TODAY()), MONTH(TODAY())-1, 1), $L$1:$P$1, 0))
This formula assumes:

Row 1 has dates for PV (e.g., 5/1/24, 6/1/24, etc.).
DATE(YEAR(TODAY()), MONTH(TODAY())-1, 1) gives the first day of last month.
$L$1:$P$1 is the range of dates in the header row.
3. Determine PV for Current Month
To get the PV for the current month:

excel
Copy code
=INDEX($L$1:$P$1, MATCH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1), $L$1:$P$1, 0))
This formula uses the first day of the current month to find the corresponding PV value.

4. Calculate Delta PV
To calculate the Delta PV as the difference between the current month and last month PV:

excel
Copy code
=H2 - G2
Where H2 is the Current Month PV and G2 is the Last Month PV.

5. Calculate Progress in Month
WD So Far: Number of workdays so far in the current month:

excel
Copy code
=NETWORKDAYS(E2, TODAY())
Where E2 is the first day of the month.

WD Mo Total: Total workdays in the current month:

excel
Copy code
=NETWORKDAYS(E2, F2)
Where E2 is the first day of the month and F2 is the last day of the month.

Prog. In Mo: Progress percentage:

excel
Copy code
=WD So Far / WD Mo Total
6. Example Formulas in Excel
Assuming the example data starts from row 2:

Last Month PV (G2):
excel
Copy code
=INDEX($L$2:$P$2, MATCH(DATE(YEAR(TODAY()), MONTH(TODAY())-1, 1), $L$1:$P$1, 0))
Current Month PV (H2):
excel
Copy code
=INDEX($L$2:$P$2, MATCH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1), $L$1:$P$1, 0))
Delta PV (I2):
excel
Copy code
=H2 - G2
WD So Far (H2):
excel
Copy code
=NETWORKDAYS(D2, TODAY())
WD Mo Total (I2):
excel
Copy code
=NETWORKDAYS(D2, E2)
Prog. In Mo (J2):
excel
Copy code
=H2 / I2