Hi Everyone,
I'm struggling to put this formula together, but essentially I have my data set up by week numbers (WWYY) Row 1 and I want the subsquent week to be listed below it. I cannot do +1 since there are only 52 weeks. I almost got it to work in column A. I hardcoded the values I'm expecting in columns B and C.
Really appreciate any set of eyes on this.
Thanks,
Steven
What's wrong with column A???
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
In A2
=$A$1+MOD(ROWS($A$1:A1),52)+INT((ROW()-1)/52)*100
Copy down
(Like Glenn: your original looks OK)
in B2
=OFFSET($A$1,COLUMNS($A:A)+ROWS($1:1),0)
or better
=INDEX($A$2:$A$1000,ROWS($1:1)+COLUMNS($A:A))
Copy across and down
Last edited by JohnTopley; 10-11-2016 at 11:48 AM.
Nice one J. I had been getting hopelessly tangled up in INTs and MODs to make a one-formula-fits-all solution, but gave up when I saw your post. I'd just suggest one further enhancement:
in A1, copied across:
=--(15+INT((COLUMNS($A:A)-1)/52)&TEXT(MOD(COLUMNS($A:A)-1,52)+1,"00"))
That, together with your two formulae, should make the whole thing run smoothly.
@Glenn,
Thanks for the feedback. I too was going down the MOD/INT when the proverbial light bulb switched on!
I hadn't looked at ROW 1 so thank you for the update.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks