View original (French)

Formula Offset

Solved
nathan01983 Posted messages 30 Registration date   Status Membre Last intervention   -  
via55 Posted messages 14730 Registration date   Status Membre 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 réponses

Réponse 1 / 3
via55 Posted messages 14730 Registration date   Status Membre Last intervention   2 755
 

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
nathan01983 Posted messages 30 Registration date   Status Membre Last intervention   10
 

Hello

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

0
nathan01983 Posted messages 30 Registration date   Status Membre 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
Réponse 2 / 3
via55 Posted messages 14730 Registration date   Status Membre Last intervention   2 755
 

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
nathan01983 Posted messages 30 Registration date   Status Membre Last intervention   10
 

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

0
nathan01983 Posted messages 30 Registration date   Status Membre 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
Réponse 3 / 3
via55 Posted messages 14730 Registration date   Status Membre Last intervention   2 755
 

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
nathan01983 Posted messages 30 Registration date   Status Membre 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
nathan01983 Posted messages 30 Registration date   Status Membre 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
nathan01983 Posted messages 30 Registration date   Status Membre Last intervention   10
 

Hello @via55 StatutMembre

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
nathan01983 Posted messages 30 Registration date   Status Membre Last intervention   10 > nathan01983 Posted messages 30 Registration date   Status Membre 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
via55 Posted messages 14730 Registration date   Status Membre Last intervention   2 755 > nathan01983 Posted messages 30 Registration date   Status Membre Last intervention  
 

Perfect

Good continuation

1