hi,


here is the situation. in "sample 1" file i need to change date and shift based on previous cell date. currently i managed to if A3 has date "DATE: 06/06/2023" then A20 will be "DATE: 06/06/2023" and if a3 has date "DATE: 06/06/2023" then A20 will be "DATE: 07/06/2023" and so on. it depends on "shift" as you can see i have to check it manually if there is production in both shifts of the day. if not i have to hide the entire row of page of that shift. if suddenly no production on a given date i change in manually then rest the dates goes following that date. shift formula and date formula is separated in sample 1 A20, A21 and so on.


And shift cell A21 is simple, if A4 is "SHIFT : A" then A21 is "SHIFT : B" & A4 is "SHIFT : B" then A21 is "SHIFT : A".




here is what i want to do-

if A3 has date "DATE: 06/06/2023" & A4 is "SHIFT : A" then A20 will be "DATE: 06/06/2023" because A20 page is the same day but different shift.


if A3 has date "DATE: 06/06/2023" & A4 is "SHIFT : B" then A20 will be "DATE: 07/06/2023" because its the next day another shift.


now i want to combine this functions. so i tried the formula in "tried formula.docx".


it gives "more than 64 level nesting" error while i paste it.


please help me. i tried best i could to explain. please use the excel file to understand the situation more.

Attached Files

	sample 1.xlsx (178.0 KB, 9 views)
	tried formula.docx (12.7 KB, 2 views)

My advice is, instead a sheet, where you have data organized as you want to read/print them, used as data entry sheet and source of calculations, have separate sheets for those tasks.

Data entry sheet will have a single table, where in addition of current columns you'll have additionally columns for date and shift (as 2 leftmost columns).

On report sheet, at top you can have data validation lists to determine report conditions, and then there is/are report table(s) designed in a way you like them, where all table values cells are calculated from data entry table according to selected report conditions. The number of different report sheets is not limited (you can have daily reports, or weekly/monthly/yearly reports, or whatever. And you don't have to use the same design for different report sheets (but you can).

The main formulas to get any numeric info (both entry values and summary info) from data entry table to report(s) will be SUMIFS(), and probably also COUNTIFS(). To get any text info, you need a hidden column in data entry table, where all rows of data entry table are numbered (=ROW()-ROW(SomeHeaderCell). Then you can use SUMIFS() to get the row number for wanted info, and use INDEX() formula to get wanted info from proper column and row. As you see, with such design all used formulas will be relatively simple ones!

In the attached cell AM1 contains your start date (which you can adjust).
Cell A3 contains a formula which uses AM1 to create the first date.
Then in cell A20 there is:
This can be copied to every 17th row as has been done in the attached.

Attached Files

msofficeforums50953sample 1.xlsx (160.0 KB, 2 views)

Sorry for replying from another id I lost password for that a/c (redirected_math)



thanks @p45cal your solution is on the right track for me. but it still doesnt use shift info from A4. i need to change date manually if needed in any of those cells.


say i started A3 at 01/06/23 shift A, then A20 will be 01/06/23 shift B, A37 02/06/23 shift A and so on. then suddenly at A88 (where is 08/06/23 shift B now) i manually to change it to 11/06/23 shift B. so next A105 will automatically be 12/06/23 shift A. how do i do that. your formula doesnt solve that

Attached Files

msofficeforums50953sample 1.xlsx (159.7 KB, 1 views)

A start of how I would designed it! There is no limits what kind of reports you design there!

I added the ShiftRow column to have an identifier for entries for same shift in same date. In case some column of your data can serve in same way, you can use this column instead (As your tables were empty of data, I had no clue what all those columns contain!). Another possibility is calculate similar indentifers based on entry position.

At end of DataInput table are a couple of helper columns (the can be made hidden). The RowNo column is used to get info into report table(s). DateNo column is added for case, when you want to design a report, which displays data from several dates before or after the date selected on report sheet (you calculate DateNo for selected date, and for every report table, you increase/decrease this number by 1 to get the date for this particular report table.

The table on sheet Calendary is used as source table for data validation list used for date selection.

Attached Files

sample 1 (1).xlsx (191.6 KB, 2 views)

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.

The Excel file attached:

Attached Files

msofficeforums50953sample 1.xlsx (163.8 KB, 1 views)

Probably you may be interested on another way to create unique row identifiers for certain shift on certain date - a dynamic named list, which allows enter only next sequential number for any row in table which match certain grouping conditions. I had to add 2 additional helper columns (LastSR and NextSR), and to create a dynamic Named Range (lNextSR), which is used as source range for Data Validation List in column ShifRow. In case date and shift cells are filled for active row, the data validation list allows enter only the next sequential number for this shift on this date. When either date or shift is undetermined, the string NextSR is only choice - to remind to fill 2 previous fields.

NB! In case you delete some ShiftRow value which isn't the last one, you can't enter the deleted number into this cell. To restore previous numbering, you have to clear all ShiftRow values for this shift and date, bigger than deleted one, and then refill them all in proper order.

Attached Files

sample 1 (1).xlsx (193.4 KB, 1 views)

Sorry! I made some later changes in example file, but did post the earlier edition! A proper attachment is here!

Attached Files

sample 1 (1).xlsx (193.5 KB, 3 views)

Thanks ArviLaanemets and p45cal for your replies.


p45cal you 1st solution was pretty good if shift info was addable.


both of your 2nd solution is kind of complex. even if i could manage that my subordinates wont.


here a video of workflow. sorry there is no audio. if you dont understand it i will reupload it.
Note" in the video i forgot to change the shift B to A"

Not promising anything, but could you attach a workbook with what seems to be your source data as in 2 seconds into your video?
As a matter of interest, where does this data come from? It might be easier to work directly from that.

here are the files of 2 days.

Attached Files

files.zip (1.53 MB, 1 views)

These don't seem very alike; I was hoping to use B601, B606, B612 to help put together your output but the likes of these cells aren't present in your attached files.

From video:
2023-06-21_162100.jpg

From attached zip file:
2023-06-21_162151.jpg

nope, in video i was making a point of workflow.