Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-06-2023, 10:39 PM
redirected_math redirected_math is offline more than 64 level nesting Windows 10 more than 64 level nesting Office 2019
Novice
more than 64 level nesting
 
Join Date: Jun 2023
Posts: 1
redirected_math is on a distinguished road
Default more than 64 level nesting

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
File Type: xlsx sample 1.xlsx (178.0 KB, 10 views)
File Type: docx tried formula.docx (12.7 KB, 3 views)

Last edited by redirected_math; 06-06-2023 at 10:44 PM. Reason: simplified the attatchment (tried formula.docx)
Reply With Quote
  #2  
Old 06-07-2023, 02:46 AM
ArviLaanemets ArviLaanemets is offline more than 64 level nesting Windows 8 more than 64 level nesting Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

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!
Reply With Quote
  #3  
Old 06-10-2023, 12:49 PM
p45cal's Avatar
p45cal p45cal is offline more than 64 level nesting Windows 10 more than 64 level nesting Office 2021
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

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:
Code:
=IF(COUNTIF($A$3:$A19,$A3)>1,"Date: " & TEXT(DATE(RIGHT($A3,4),MID($A3,10,2),MID($A3,7,2))+1,"dd/mm/yyy"),$A3)
This can be copied to every 17th row as has been done in the attached.
Attached Files
File Type: xlsx msofficeforums50953sample 1.xlsx (160.0 KB, 2 views)
Reply With Quote
  #4  
Old 06-11-2023, 09:29 PM
stricky stricky is offline more than 64 level nesting Windows 10 more than 64 level nesting Office 2019
Novice
 
Join Date: Jun 2023
Location: Bangladesh
Posts: 19
stricky is on a distinguished road
Default

Quote:
Originally Posted by p45cal View Post
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:
Code:
=IF(COUNTIF($A$3:$A19,$A3)>1,"Date: " & TEXT(DATE(RIGHT($A3,4),MID($A3,10,2),MID($A3,7,2))+1,"dd/mm/yyy"),$A3)
This can be copied to every 17th row as has been done in the attached.

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
File Type: xlsx msofficeforums50953sample 1.xlsx (159.7 KB, 1 views)
Reply With Quote
  #5  
Old 06-12-2023, 06:59 AM
ArviLaanemets ArviLaanemets is offline more than 64 level nesting Windows 8 more than 64 level nesting Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

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
File Type: xlsx sample 1 (1).xlsx (191.6 KB, 2 views)
Reply With Quote
  #6  
Old 06-12-2023, 08:09 AM
p45cal's Avatar
p45cal p45cal is offline more than 64 level nesting Windows 10 more than 64 level nesting Office 2021
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
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
  #7  
Old 06-12-2023, 08:11 AM
p45cal's Avatar
p45cal p45cal is offline more than 64 level nesting Windows 10 more than 64 level nesting Office 2021
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

The Excel file attached:
Attached Files
File Type: xlsx msofficeforums50953sample 1.xlsx (163.8 KB, 1 views)
Reply With Quote
  #8  
Old 06-13-2023, 12:16 AM
ArviLaanemets ArviLaanemets is offline more than 64 level nesting Windows 8 more than 64 level nesting Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

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
File Type: xlsx sample 1 (1).xlsx (193.4 KB, 1 views)
Reply With Quote
  #9  
Old 06-13-2023, 12:22 AM
ArviLaanemets ArviLaanemets is offline more than 64 level nesting Windows 8 more than 64 level nesting Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

Sorry! I made some later changes in example file, but did post the earlier edition! A proper attachment is here!
Attached Files
File Type: xlsx sample 1 (1).xlsx (193.5 KB, 3 views)
Reply With Quote
  #10  
Old 06-16-2023, 06:39 AM
stricky stricky is offline more than 64 level nesting Windows 10 more than 64 level nesting Office 2019
Novice
 
Join Date: Jun 2023
Location: Bangladesh
Posts: 19
stricky is on a distinguished road
Default

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"

Last edited by stricky; 06-16-2023 at 06:45 AM. Reason: Note" in the video i forgot to change the shift B to A"
Reply With Quote
  #11  
Old 06-19-2023, 12:52 PM
p45cal's Avatar
p45cal p45cal is offline more than 64 level nesting Windows 10 more than 64 level nesting Office 2021
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

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.
Reply With Quote
  #12  
Old 06-19-2023, 08:18 PM
stricky stricky is offline more than 64 level nesting Windows 10 more than 64 level nesting Office 2019
Novice
 
Join Date: Jun 2023
Location: Bangladesh
Posts: 19
stricky is on a distinguished road
Default

here are the files of 2 days.
Attached Files
File Type: zip files.zip (1.53 MB, 1 views)
Reply With Quote
  #13  
Old 06-21-2023, 08:25 AM
p45cal's Avatar
p45cal p45cal is offline more than 64 level nesting Windows 10 more than 64 level nesting Office 2021
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

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
Reply With Quote
  #14  
Old 06-22-2023, 07:31 PM
stricky stricky is offline more than 64 level nesting Windows 10 more than 64 level nesting Office 2019
Novice
 
Join Date: Jun 2023
Location: Bangladesh
Posts: 19
stricky is on a distinguished road
Default

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

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
INDEX and MATCH nesting with if Peter Simpson Excel Programming 1 05-27-2019 06:53 AM
more than 64 level nesting Nesting Merge Fields LearningMom Mail Merge 9 09-25-2017 01:44 PM
more than 64 level nesting Automatic numbered lists - Cannot create new point at level 2, after making level 3 points. icor1031 Word 1 01-14-2016 09:24 PM
Nesting and IF & AND statement lynchbro Excel 4 04-16-2015 11:39 AM
more than 64 level nesting Nesting Variables SET, LISTNUM, and REF Wyskers Word 1 11-13-2011 05:43 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 12:51 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft