+ Reply to Thread
Results 1 to 13 of 13

Help Shrinking Formulas

  1. #1
    Registered User
    Join Date
    07-07-2020
    Location
    Australia
    MS-Off Ver
    2013
    Posts
    19

    Help Shrinking Formulas

    Hi All,

    I am currently trying to design/design a worksheet for the following and am about to rip out my hair! I hope someone can shed some light on where I am going wrong or what would be the best way of doing this.

    Main Layout
    Cell A1 = Month Drop Down List
    Cell A3 = Week Number (1,2,3,4)
    Cell B3 = Month 1 Chosen Date (Monday or Friday)
    Cell B4 = Month 2 Chosen Date (Opposite day from same week e.g. if B3 is Monday then B4 would be Friday)

    Needing to have any changes to the Week Column needs to update to that week's Monday and Friday.

    Monday Code (C1)
    =$C$1+7-WEEKDAY($C$1+5)
    Subsequent Mondays are entered as C1+7, C1+14, etc.

    Friday Code (C2)
    =$C$1+7-WEEKDAY($C$1+5
    subsequent Fridays are entered as C2+7,C2+14, etc.


    I hope the above information is clear and understandable, happy to supply any further information needed or a copy of the worksheet so far.

    Anthony
    Above is what I had originally asked for help with but now as I believe I have solved (damn quotation marks!) what I need instead of creating a new thread I have updated this one keeping above for others who may one day need this sort of table.

    My knowledge of Excel is quite limited so I am hoping someone might be able to look at the formulas included within my spreadsheet.

    I am curious:
    1) Can any be changed but give the same result, e,g.:
    My main formula is =IF(AND($E3="M",$F3=1),$B$3,IF(AND($E3="F",$F3=1),$C$3,IF(AND($E3="M",F3=2),$B$4,IF(AND($E3="F",$F3=2),$C$4,IF(AND($E3="M",$F3=3),$B$5,IF(AND($E3="F",$F3=3),$C$5,IF(AND($E3="M",$F3=4),$B$6,IF(AND($E3="F",$F3=4),$C$6))))))))

    2) Will shrinking \ changing the formulas make any difference to the spreadsheet?

    Thanks so much in advance.
    Anthony
    Last edited by t0ny84; 07-08-2020 at 06:57 AM.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Date Table Help

    Please read the yellow banner at the top of this page on how to attach a file.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    07-07-2020
    Location
    Australia
    MS-Off Ver
    2013
    Posts
    19

    Re: Date Table Help

    Hi alansidman; apologies I thought I had attached the example, have uploaded a copy now.
    Last edited by AliGW; 07-08-2020 at 07:01 AM. Reason: Please don't quote unnecessarily!

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Help Shrinking Formulas

    Nope - nothing attached (yet).
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  5. #5
    Registered User
    Join Date
    07-07-2020
    Location
    Australia
    MS-Off Ver
    2013
    Posts
    19

    Re: Help Shrinking Formulas

    Quote Originally Posted by AliGW View Post
    Nope - nothing attached (yet).
    Round 3!
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Help Shrinking Formulas

    How about
    In E5 copied down
    =INDEX(Data!$C$3:$D$6,B5,SWITCH(D5,"M",1,"F",2))

  7. #7
    Registered User
    Join Date
    07-07-2020
    Location
    Australia
    MS-Off Ver
    2013
    Posts
    19

    Re: Help Shrinking Formulas

    Quote Originally Posted by Fluff13 View Post
    How about
    In E5 copied down
    =INDEX(Data!$C$3:$D$6,B5,SWITCH(D5,"M",1,"F",2))
    Hi Fluff13,
    Thank you for the suggestion but when I try this formula it is giving me a #Name? only. When you used this did you change any other data?
    Thanks
    Anthony

  8. #8
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Help Shrinking Formulas

    Sheet Data, cell C3:

    Please Login or Register  to view this content.
    D3:

    =C3+4

    Drag C3:D3 into C6:D6

    Copy C3:D6 into C10:D13

    C10 to adjust the range into D8

    E5 sheet2:

    Please Login or Register  to view this content.
    Drag down

    Copy E5 to G5 change C2 to C9 then drag down
    Attached Files Attached Files
    Quang PT

  9. #9
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Help Shrinking Formulas

    when I try this formula it is giving me a #Name? only
    Apologies, forgot that Switch doesn't exist in 2013.
    Try
    =INDEX(Data!$C$3:$D$6,B5,IF(D5="M",1,2))

  10. #10
    Registered User
    Join Date
    07-07-2020
    Location
    Australia
    MS-Off Ver
    2013
    Posts
    19

    Re: Help Shrinking Formulas

    Hey Fluff13 and bebo021999 both of these worked perfectly thank you so so much!

    If I might also ask bebo021999 with the two formulas you have used:

    =$D$1+CHOOSE(WEEKDAY($D$1),1,0,6,5,4,3,2)
    1) With the day formula can you please advise how the numbering (above bolded) works with the formula?
    2) How could I incorporate this to also be for Tuesday, Wednesday, Thursday?

    =OFFSET(Data!$C$2,$B5,IF(D5="M",0,1),)
    Is my understanding correct with this formula that if D5 = M supply formula from C column but if not then do from D column using the 1,2,3,4 as the index?
    Also can you help me use this formula to also include Tuesday, Wednesday, Thursday? Just trying to future proof this at the beginning rather than at the end

    Thanks again both of you SO SO SO MUCH!

  11. #11
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Help Shrinking Formulas

    =$D$1+CHOOSE(WEEKDAY($D$1),1,0,6,5,4,3,2)

    May I remind that CHOOSE(n,A,B,C) would give "A" if n=1, "B" if n=2,...

    Scenarios:
    1) if D1=Sunday, WEEKDAY(D1)=1
    CHOOSE(WEEKDAY(...) returns 1
    D1+CHOOSE = sunday + 1 = next monday
    2) if D1=Monday, WEEKDAY=2
    CHOOSE(WEEKDAY(...) returns 0
    D1+CHOOSE = monday + 0 = next monday
    ...
    if D1=Saturday, WEEKDAY=7
    CHOOSE(WEEKDAY(...) returns 2
    D1+CHOOSE = sartuday + 2 = next monday

    =>D1+CHOOSE always return nearest next Monday

    Follow this logic, if you need next Tue, try CHOOSE(WEEKDAY($D$1),2,1,0,6,5,4,3); next Wed: CHOOSE(WEEKDAY($D$1),3,2,1,0,6,5,4) and so on.

    About the OFFSET:
    OFFSET(cell,r,c,): move the cell down r rows, to the right c columns
    i,e OFFSET(C2,1,1) refer to cell D3

    with B5=1, D5="M"
    =OFFSET(Data!$C$2,$B5,IF(D5="M",0,1),)

    cell C2 will move down 1 row, stay in same column (c=0) to refer to cell C3

    Hope it is clear now for you.

  12. #12
    Registered User
    Join Date
    07-07-2020
    Location
    Australia
    MS-Off Ver
    2013
    Posts
    19

    Re: Help Shrinking Formulas

    Hey bebo021999 thanks so much, it is starting to sink in and make sense.
    Just to clarify with OFFSET

    =OFFSET(Data!$D$10,$C4,IF($G4="Mon",0,IF($G4="Tues",1,IF($G4="Fri",3,3))))

    The above would be used to go to the table of dates in D10 then if MON in G4 it would take the data from the same column, if TUES then it would move across 1 column and if it was FRI it would move across 3 columns?

    Could this be shortened any further?

    Thanks again!
    Last edited by AliGW; 07-13-2020 at 12:30 AM. Reason: Please don't quote unnecessarily!

  13. #13
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Help Shrinking Formulas

    Try to lookup G4 in a sort-weekday-list like this and pick the relevant number:

    =LOOKUP(G4,{"Fri";"Mon";"Sat";"Sun";"Thu";"Tue";"Wed"},{3,0,4,5,2,1,6})

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VBA Code Button that runs through Date list Table and Copies onto another table
    By Bisky in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-07-2018, 10:26 AM
  2. Replies: 1
    Last Post: 05-31-2017, 04:20 PM
  3. Replies: 1
    Last Post: 12-19-2014, 02:08 PM
  4. [SOLVED] Code to move data from an entry table to a historical table by date.
    By rlh in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-17-2014, 11:04 AM
  5. [SOLVED] Index Match Based on One Date Criteria, Table Contains Beginning Date and End Date
    By jcox1953 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-09-2014, 02:41 PM
  6. Replies: 4
    Last Post: 10-05-2012, 03:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1