Google Sheets – Display Date of Each Weekday of Current Week

I would suggest something that might not quite meet "delete the values" etc but might serve as giving the appearance of doing so. Use two sheets, one (say RefData) with dates across say Row1 (say starting with March 17 in B1) and cell values such as Exam in say Row3 of the corresponding column. The intention being to have no need to ever delete the contents of this sheet - merely keep extending it to the right.

The other sheet would be very much like the example provided by @Punchlinern, though maybe with =text(B1,"dddd") in B2 (copied across) since WEEKDAY is not necessary, and in say B4 and copied across:

=offset(RefData!$A$1,2,match(B1,RefData!1:1,0)-1) 

This sheet would provide a view of RefData and be populated exclusively by formulae - all data would be entered in RefData.

The optional fourth parameter for offset might be applied if to show more than just a single cell value per day, perhaps a row for each of morning, lunch, afternoon, dinner, evening.

This can be solved by introducting a separate sheet, let's call it 'Weekdays':

| Weekday | Number of stylists Site 1 | Number of stylists Site 2 | Number of stylists Site 3 |
|       1 |                         0 |                         0 |                         0 |
|       2 |                         0 |                         0 |                         0 |
|       3 |                         5 |                         3 |                         5 |
|       4 |                         4 |                         4 |                         4 |
...

The first column is the weekday (0 is Sunday, 1 is Monday, etc), the next columns are the number of stylists available on that weekday, on the different sites.

Having this table, I can use a formula to match a weekday + a site with a number of stylists, from the main sheet. The following formula is for Tuesdays on Site 1:

=VLOOKUP(B$2, Weekdays!$A$2:$D$8,2)

The VLOOKUP function (check out its documentation) takes the value from B2 (Tue, which translates to 2), and matches it against the first column in the Weekdays sheet. When it finds a matching row, it returns the 2^nd column - which is the Number of stylists Site 1.

Thus, the formula can be dragged across the entire row for Site 1, and it will adjust automatically.

For the other sites, you need to modify the last parameter of the formula. For Site 2, replace 2 with 3: =VLOOKUP(B$2, Weekdays!$A$2:$D$8,3), and for Site 3, replace with 4: =VLOOKUP(B$2, Weekdays!$A$2:$D$8,4)

I have set up an example spreadsheet to demonstrate this, feel free to copy it.