+ 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
    13

    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
    MS-Off Ver
    MS Office 2019
    Posts
    17,931

    Re: Date Table Help

    Please read the yellow banner at the top of this page on how to attach a file.

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

    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
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,644

    Re: Help Shrinking Formulas

    Nope - nothing attached (yet).
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

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

    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
    MS-Off Ver
    365
    Posts
    7,812

    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
    13

    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
    5,738

    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

  9. #9
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    365
    Posts
    7,812

    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
    13

    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
    5,738

    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
    13

    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
    5,738

    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