View original (French)

Formula Offset

Solved
nathan01983 Posted messages 30 Registration date   Status Member Last intervention   -  
via55 Posted messages 14393 Registration date   Status Member Last intervention   -

Hello everyone

I need your help, I would like to copy a formula in an Excel table.
Let me explain, I want that when I put an "X" in cell H9, an "X" appears in every 4 cells.
The 4 is a value I retrieve from cell G9 (of course, this value can change).
After that, I plan to extend it to all the rows since the frequency will change for each row.
I'm not sure if I'm being very clear lol

Thank you anyway for your help.

3 answers

  1.
    via55 Posted messages 14393 Registration date   Status Member Last intervention   2 759
     

    Hello

    Formula to put in I9

    =IF(MOD(COLUMN()-COLUMN($H9),$G9)=0,"X","")

    Then stretch to the right and down

    Best regards

    Via

    1
    1. nathan01983 Posted messages 30 Registration date   Status Member Last intervention   10
       

      Hello

      Thank you very much. It's exactly what I was trying to do.

      0
    2. nathan01983 Posted messages 30 Registration date   Status Member Last intervention   10
       

      Hello

      Thank you again for your formula.
      I would like to refine my table.
      So I'm reaching out for your help again.

      So I would like my "H9" to be variable depending on a date that can change.

      I would like the formula to start from this date if my starting date is January 12, 2023.

      Do you think that would be possible?

      https://www.cjoint.com/c/MKgiRQvuCcH


      Best regards.

      0
  2. via55 Posted messages 14393 Registration date   Status Member Last intervention   2 759
     

    Hello

    See if it works

    https://www.cjoint.com/c/MKgqonORsnQ

    The 7 in the formula is because there are 7 columns (from A to G) before your date table that need to be added to the rank found in the date column to get its column number

    Best regards

    Via

    1
    1. nathan01983 Posted messages 30 Registration date   Status Member Last intervention   10
       

      You manage crazily THANK YOU SO MUCH !!!!!!!!!!!!!!!!!

      0
    2. nathan01983 Posted messages 30 Registration date   Status Member Last intervention   10
       

      Hello Via55


      I'm reaching out for your help again

      In my adapted file, I have a small issue when I set it to a frequency of every three weeks, for example, it doesn't work (the start date is shifted) this happens with three weeks, six weeks, quarterly and semi-annually... and I can't understand why.

      Could you please help me :)

      Thanks for your future help.

      https://www.cjoint.com/c/MKBlPFOwUJL

      0
  3. via55 Posted messages 14393 Registration date   Status Member Last intervention   2 759
     

    Good evening Nathan

    Indeed, given how your file is presented, it's complicated

    Moreover, it mixes week offsets and month offsets (you need to use EDATE to offset the date by a certain number of months because not all months have 4 weeks)

    So I've opted for another approach:

    week offset = numeric offset indicated in the Data Sheet table

    month offset = offset based on the number of non-numeric characters indicated in the same table

    Then in the table (hidden columns I to BJ of Planning) we calculate all the weeks of each year when interventions should take place according to the frequency

    The formulas below the week numbers then mark a P when the corresponding week exists in the range I:BJ

    https://www.cjoint.com/c/MKBtnHfTvzQ

    Best regards

    Via

    1
    1. nathan01983 Posted messages 30 Registration date   Status Member Last intervention   10
       

      I'll take a look at it during the day, but in any case, thank you very much for taking the time to help me :)

      THANK YOU :)

      0
    2. nathan01983 Posted messages 30 Registration date   Status Member Last intervention   10
       

      It works perfectly, but it gives me a super heavy file :/

      But thank you so much for your work :)

      Have a great day.

      0
    3. nathan01983 Posted messages 30 Registration date   Status Member Last intervention   10
       

      Hello @via55 StatusMember

      Thank you once again for your help ..

      I'm reaching out to you again :) the schedule works perfectly for the year 2023 and as soon as I switch to 2024 it no longer works :(

      Can you pleaaaase help me AGAIN?

      Thank you sooo much :)

      0
      1. nathan01983 Posted messages 30 Registration date   Status Member Last intervention   10 > nathan01983 Posted messages 30 Registration date   Status Member Last intervention  
         

        I tried to search a bit anyway and finally I found

        I replaced this formula in BK8 (and those that follow)

        =DATE(YEAR(TODAY()),1,3)-WEEKDAY(DATE(YEAR(TODAY()),1,3))-5+7*BK9

        With ("choice_year" being a name associated with a cell giving the year in which the schedule will evolve)

        =DATE(choice_year,1,3)-WEEKDAY(DATE(choice_year,1,3))-5+7*BK9

        0
      2. via55 Posted messages 14393 Registration date   Status Member Last intervention   2 759 > nathan01983 Posted messages 30 Registration date   Status Member Last intervention  
         

        Perfect

        Good continuation

        1