• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Rotating name formula

M

Mooiman

New Member
I’m a train driver and working a 33-week link, starting Sunday to Saturday.

After the first week the date must change, with it the names in B3 and C3 and move one row down to B4 and C4 and the rest of the names

The last week the names in B35 and C35 must move up to week one B3 and C3.

I would appreciate it If you could help me with a formula to rotate names when date changes
 

Attachments

  • FORMULA.xlsx
    10.9 KB · Views: 4
This can be used by a simply VLOOKUP function

1] You just set up a Result Table with header as same as the Source Table

2] Criteria of week is in cell M1, you enter number 1 to 33 (This example I enter 17)

Then

3] In L3, formula copied across right:

=VLOOKUP($M$1,$A$3:$J$35,MATCH(L$2,$A$2:$J$2,0),0)

1696114177060.png
 

Attachments

  • FORMULA (1).xlsx
    13.8 KB · Views: 11
Thank you for responding so quickly.
I just want to make sure that the formula in your respond Is what you mend in Sheet2 of the attachment.
 

Attachments

  • FORMULA.xlsx
    12.8 KB · Views: 5

Mooiman​

As You've read both Forum's rules
... please reread this Forum's and follow too:

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...
chandoo.org
  • Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
  • If you do cross-post, please put that in your post.
  • Also if you have cross-posted and get an Solution elsewhere, have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.
 
I do such tasks using Lambda functions, but then all my Excel formulas are Lambda functions!
Code: 
= IncrementDatesλ(baseDate, 1)

= IncrementRosterλ(baseRoster, 1)
where
Code: 
IncrementDatesλ
= LET(
    w,    IF(ISOMITTED(weeks), 1, weeks),
    date, 1 + 7 * (w - 1) + DATEVALUE(TEXTAFTER(priorDate, "-")),
    UPPER(TEXT(date, "dd mmmm") & " - " & TEXT(date + 6, "dd mmmm yyyy"))
  )
 
IncrementRosterλ
  = LET(
      w,      IF(ISOMITTED(weeks), 1, weeks),
      rotate, MOD(w, 5),
      roster, IF(rotate,
              VSTACK(TAKE(priorRoster, -w), DROP(priorRoster, -w)),
            priorRoster),
      roster
  )
 

Attachments

  • Formula Link.xlsx
    20.1 KB · Views: 8
Thank you for your respond
Need more help inserting formulas. First time I had a challenge like this with the formulas.
Get a #NAME? shown when I insert formulas in cell.
 
Firstly and foremost, you need to be using Excel 365 for any solution I write to work.
Then, if you open Name Manager you will see the Lambda functions IncrementDatesλ and IncrementRosterλ that must be copied to any new workbook if the functions are to work. On a more trivial level the functions reference the cell F1 by the name baseDate and range B3:C7 as baseRoster. The functions are written either to reference their immediate predecessor and increment one week or to reference the base case and increment the number of weeks input.
 
In that case you may need to go back to basics. I think you have SEQUENCE and MOD to generate row indices and the INDEX function is capable of returning arrays of results given arrays as row and column parameters.
 
Code: 
= INDEX(baseRoster, 1 + MOD(SEQUENCE(5,,-1), 5), {1,2})
will cycle the base roster one place. Referring to the output table will cycle it one further step. Otherwise the -1 becomes -2 and so on.
Similarly the formula for the date range is
Code: 
= UPPER(
    TEXT(1 + DATEVALUE(TEXTAFTER(baseDate, "-")), "dd mmmm") &
    " - " &
    TEXT(1 + DATEVALUE(TEXTAFTER(baseDate, "-")) + 6, "dd mmmm yyyy")
  )
Makes me appreciate 365 and Lambda, where all the unsightly details are decently hidden from view!
 

Attachments

  • Formula Link.xlsx
    20.2 KB · Views: 12
As with all my formulas, this appears to use defined names and not cell references to return data. Are the names defined in the new workbook and do they refer to the correct cells?
 
You must log in or register to reply here.
Back
Top