# Help Shrinking Formulas

1. ## 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?

Anthony  Register To Reply

2. ## Re: Date Table Help  Register To Reply

3. ## Re: Date Table Help

Hi alansidman; apologies I thought I had attached the example, have uploaded a copy now.  Register To Reply

4. ## Re: Help Shrinking Formulas

Nope - nothing attached (yet).  Register To Reply

5. ## Re: Help Shrinking Formulas Originally Posted by AliGW Nope - nothing attached (yet).
Round 3!   Register To Reply

6. ## Re: Help Shrinking Formulas

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

7. ## Re: Help Shrinking Formulas Originally Posted by Fluff13 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  Register To Reply

8. ## 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  Register To Reply

9. ## 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))  Register To Reply

10. ## 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!  Register To Reply

11. ## 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.

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.  Register To Reply

12. ## 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!  Register To Reply

13. ## 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})  Register To Reply