# HELP! Formulas assistance

#### Roo007

##### New Member
i have an excel formula below that i need to add a column to after each day of the week,. How do i write/adjust the formula to give me what i need?
=IF(MONTH(\$K\$6)<>MONTH(\$K\$6-(WEEKDAY(\$K\$6,1)-(\$AU\$3-1))-IF((WEEKDAY(\$K\$6,1)-(\$AU\$3-1))<=0,7,0)+(ROW(K8)-ROW(\$K\$8))*7+(COLUMN(K8)-COLUMN(\$K\$8)+1)),"",\$K\$6-(WEEKDAY(\$K\$6,1)-(\$AU\$3-1))-IF((WEEKDAY(\$K\$6,1)-(\$AU\$3-1))<=0,7,0)+(ROW(K8)-ROW(\$K\$8))*7+(COLUMN(K8)-COLUMN(\$K\$8)+1))
You help is greatly appreciated!!!!

### Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

#### ukmikeb

##### Well-known Member
Hi

Welcome to the MrExcel Forum.

What is in cell AU3?

What cell is the formula in?

What is the purpose of the formula?

#### ukmikeb

##### Well-known Member
If K6 is 01/11/2015 and AU3 is 1 then the result is 01/11/2015.

What cell is the formula in?

What is the purpose of the formula?

What are you trying to do with the formula?

Post a screen shot using one of the Add-Ons in my signature.

#### ukmikeb

##### Well-known Member
If K6 is 01/11/2015 and AU3 is 1 then the result is 01/11/2015.

What cell is the formula in?

What are you trying to do with the formula?

1, K8
2, I am trying to mirror the months in a vertical format rather than 3 months horizontally with the exception that there is a blank cell between each day.

I have created a formula which replicates January in the new format In K8 :-
Code:
``=IF(N(MOD(COLUMNS(\$K8:K8),2)),INDEX('2016'!\$A\$6:\$W\$40,MATCH(\$K\$6,'2016'!\$A\$6:\$W\$6,0)+2+ROWS(\$K9:\$K\$9)-1,INT(COLUMNS(\$K8:K8)/2)+1),"")``
dragged across and down.

I will get back to the rest when I have completed some necessary tasks.

#### Roo007

##### New Member
Thank you! FYI columns A-J will not have formulas in the cell, only text.

#### ukmikeb

##### Well-known Member
Rather than make specific refernce to cell addresses each of the following formulae will be placed in the leftmost cell under "Domingo" for the month in question, dragged across to the column headed "Sabado" and yhen dragged down four or five rows in order to complete the days for that month.

January
Code:
``=IF(N(MOD(COLUMNS(\$K\$8:K\$8),2)),INDEX('2016'!\$A\$6:\$W\$40,MATCH(\$K\$6,'2016'!\$A\$6:\$W\$6,0)+2+ROWS(\$K\$8:\$K8)-1,INT(COLUMNS(\$K\$8:K\$8)/2)+MATCH(\$K\$6,'2016'!\$A\$6:\$W\$6,0)),"")``
February
Code:
``=IF(N(MOD(COLUMNS(\$K\$8:K\$8),2)),INDEX('2016'!\$A\$6:\$W\$40,MATCH(\$K\$6,'2016'!\$A\$6:\$W\$6,0)+2+ROWS(\$K\$17:\$K17)-1,INT(COLUMNS(\$K\$8:K\$8)/2)+MATCH(\$K\$15,'2016'!\$A\$6:\$W\$6,0)),"")``
March
Code:
``=IF(N(MOD(COLUMNS(\$K\$8:K\$8),2)),INDEX('2016'!\$A\$6:\$W\$40,MATCH(\$K\$6,'2016'!\$A\$6:\$W\$6,0)+2+ROWS(\$K\$26:\$K26)-1,INT(COLUMNS(\$K\$8:K\$8)/2)+MATCH(\$K\$24,'2016'!\$A\$6:\$W\$6,0)),"")``
April
Code:
``=IF(N(MOD(COLUMNS(\$K\$8:K\$8),2)),INDEX('2016'!\$A\$15:\$W\$40,MATCH(\$K\$33,'2016'!\$A\$15:\$W\$15,0)+2+ROWS(\$K\$35:\$K35)-1,INT(COLUMNS(\$K\$8:K\$8)/2)+MATCH(\$K\$33,'2016'!\$A\$15:\$W\$15,0)),"")``
May
Code:
``=IF(N(MOD(COLUMNS(\$K\$8:K\$8),2)),INDEX('2016'!\$A\$15:\$W\$40,MATCH(\$K\$33,'2016'!\$A\$15:\$W\$15,0)+2+ROWS(\$K\$44:\$K44)-1,INT(COLUMNS(\$K\$8:K\$8)/2)+MATCH(\$K\$42,'2016'!\$A\$15:\$W\$15,0)),"")``
June
Code:
``=IF(N(MOD(COLUMNS(\$K\$8:K\$8),2)),INDEX('2016'!\$A\$15:\$W\$40,MATCH(\$K\$33,'2016'!\$A\$15:\$W\$15,0)+2+ROWS(\$K\$53:\$K53)-1,INT(COLUMNS(\$K\$8:K\$8)/2)+MATCH(\$K\$51,'2016'!\$A\$15:\$W\$15,0)),"")``
July
Code:
``=IF(N(MOD(COLUMNS(\$K\$8:K\$8),2)),INDEX('2016'!\$A\$24:\$W\$40,MATCH(\$K\$60,'2016'!\$A\$24:\$W\$24,0)+2+ROWS(\$K\$62:\$K62)-1,INT(COLUMNS(\$K\$8:K\$8)/2)+MATCH(\$K\$60,'2016'!\$A\$24:\$W\$24,0)),"")``
August
Code:
``=IF(N(MOD(COLUMNS(\$K\$8:K\$8),2)),INDEX('2016'!\$A\$24:\$W\$40,MATCH(\$K\$60,'2016'!\$A\$24:\$W\$24,0)+2+ROWS(\$K\$71:\$K71)-1,INT(COLUMNS(\$K\$8:K\$8)/2)+MATCH(\$K\$69,'2016'!\$A\$24:\$W\$24,0)),"")``
September
Code:
``=IF(N(MOD(COLUMNS(\$K\$8:K\$8),2)),INDEX('2016'!\$A\$24:\$W\$40,MATCH(\$K\$60,'2016'!\$A\$24:\$W\$24,0)+2+ROWS(\$K\$80:\$K80)-1,INT(COLUMNS(\$K\$8:K\$8)/2)+MATCH(\$K\$78,'2016'!\$A\$24:\$W\$24,0)),"")``
October
Code:
``=IF(N(MOD(COLUMNS(\$K\$8:K\$8),2)),INDEX('2016'!\$A\$33:\$W\$40,MATCH(\$K\$87,'2016'!\$A\$33:\$W\$33,0)+2+ROWS(\$K\$89:\$K89)-1,INT(COLUMNS(\$K\$8:K\$8)/2)+MATCH(\$K\$87,'2016'!\$A\$33:\$W\$33,0)),"")``
November
Code:
``=IF(N(MOD(COLUMNS(\$K\$8:K\$8),2)),INDEX('2016'!\$A\$33:\$W\$40,MATCH(\$K\$87,'2016'!\$A\$33:\$W\$33,0)+2+ROWS(\$K\$98:\$K98)-1,INT(COLUMNS(\$K\$8:K\$8)/2)+MATCH(\$K\$96,'2016'!\$A\$33:\$W\$33,0)),"")``
December
Code:
``=IF(N(MOD(COLUMNS(\$K\$8:K\$8),2)),INDEX('2016'!\$A\$33:\$W\$40,MATCH(\$K\$87,'2016'!\$A\$33:\$W\$33,0)+2+ROWS(\$K\$107:\$K107)-1,INT(COLUMNS(\$K\$8:K\$8)/2)+MATCH(\$K\$105,'2016'!\$A\$33:\$W\$33,0)),"")``
hth

#### ukmikeb

##### Well-known Member
I was also able to modify the original formula to cope with the column spacing.

For January :-
Code:
``=IF(MOD(COLUMNS(\$K\$6:K\$6),2),IF(MONTH(\$K\$6)<>MONTH(\$K\$6-(WEEKDAY(\$K\$6,1)-(\$AU\$3-1))-IF((WEEKDAY(\$K\$6,1)-(\$AU\$3-1))<=0,7,0)+(ROWS(\$K\$8:\$K8)-1)*7+(INT(COLUMNS(\$K\$6:K\$6)/2)+1)),"",\$K\$6-(WEEKDAY(\$K\$6,1)-(\$AU\$3-1))-IF((WEEKDAY(\$K\$6,1)-(\$AU\$3-1))<=0,7,0)+(ROWS(\$K\$6:\$K6)-1)*7+(INT(COLUMNS(\$K\$6:K\$6)/2)+1)),"")``
hth

#### Roo007

##### New Member
Can you send me the modified file? Thank you!!!! ???