Hi,
I have a spreadsheet that is tracking finances across a number of teams. The spreadsheet looks similar to the much simplified version below:
<tbody>
</tbody>
In the example above, the current date is some point between 15th Jan and 22nd Jan. Managing the finances on a weekly basis thus means all 'cost to date' values are pulled from columns C&D, while all estimated remaining costs are pulled from columns E&F.
I would like to create a conditional formula that checks that the cells referenced in the fomula of each cell in columns H and J is referencing the correct column in terms of date.
I'm happy to add two reference cells on a hidden sheet with the columns to be checked against to enable this, but I'm not sure what the formula would look like - or if it is even possible!
Thanks in advance!
Matt
I have a spreadsheet that is tracking finances across a number of teams. The spreadsheet looks similar to the much simplified version below:
A | B | C | D | E | F | G | H | J | |
1 | Cost/day | 1st Jan | 8th Jan | 15th Jan | 22nd Jan | Total Cost | Cost To Date | Estimated Remaining Cost | |
2 | Tom | £100 | 5 | 5 | 4 | 5 | =SUM(C2:F2)*B2 | =SUM(C2:D2)*B2 | =SUM(E2:F2)*B2 |
3 | Geoff | £120 | 4 | 3.5 | 3 | 4 | =SUM(C3:F3)*B3 | =SUM(C3:D3)*B3 | =SUM(E3:F3)*B3 |
4 | Lucy | £80 | 5 | 5 | 5 | 5 | =SUM(C4:F4)*B4 | =SUM(C4:D4)*B4 | =SUM(E4:F4)*B4 |
5 | Norman | £50 | 2 | 3 | 2 | 4 | =SUM(C5:F5)*B5 | =SUM(C5:D5)*B5 | =SUM(E5:F5)*B5 |
<tbody>
</tbody>
In the example above, the current date is some point between 15th Jan and 22nd Jan. Managing the finances on a weekly basis thus means all 'cost to date' values are pulled from columns C&D, while all estimated remaining costs are pulled from columns E&F.
I would like to create a conditional formula that checks that the cells referenced in the fomula of each cell in columns H and J is referencing the correct column in terms of date.
I'm happy to add two reference cells on a hidden sheet with the columns to be checked against to enable this, but I'm not sure what the formula would look like - or if it is even possible!
Thanks in advance!
Matt