View Single Post
 
Old 06-12-2023, 08:09 AM
p45cal's Avatar
p45cal p45cal is offline Windows 10 Office 2021
Expert
 
Join Date: Apr 2014
Posts: 871
p45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond repute
Default

I see Arvi has posted too, and I would advise following his ideas; they'll be easier to adjust for future months.
My offering is a bit of a half way house towards his solution.
In the attached (in the next message - can only attach 5 files per message) I've set up columns AQ:AR, which the main forms refer to. This pair of columns is referred to in formulae in your forms. This pair of columns refers to the 2 columns to the left (AO:AP) in green, and it's these two columns that you edit.
The advantage is that they're all together (contiguous), which makes it much easier to get your forms right.
Since your shifts and dates seem (occasionally) to be random (bespoke), I've made it so that you only have to get the green cells in columns AO and AP correct.

This is down to your ingenuity, you can put formulae in there if you want or use drag/copy to fill them:
For example, you can select 2 or 3 cells vertically (eg. AP2:AP3) and drag/copy down to get repeating A/B, but if the sequence isn't always A/B you can correct that manually, select another 2 cells again and drag copy down again.
With the dates, similar.

A hint about getting dates to appear in pairs without having to enter each date in each cell, if you want pairs of dates, start by manually entering two dates in 4 cells like this:
2023-06-12_154812.jpg
then drag down as far as you need, leaving:
2023-06-12_154947.jpg
then while all the cells are still selected, press F5 on the keyboard to get the Goto dialogue box, click Special…, then choose Blanks, then OK,
2023-06-12_155032.jpg
which should leave you like this:
2023-06-12_155119.jpg
then without clicking on anything else, type = on the keyboard, then the up arrow key on the keyboard, then hold the control key while you press Enter. This will put a formula into each of those blank cells leaving:
2023-06-12_155208.jpg
Then to finish off, select all the cells again and copy, paste special, values, to replace those formulae with plain values.

Last edited by p45cal; 06-13-2023 at 02:39 AM.
Reply With Quote