• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

COLUMN counting for Merge Cells

crenson313

crenson313

New Member
Good Day!

Chandoo's & Ninja's

Please help me in my formula

Is it possible to count the merge cells when I used the COLUMN formula?
make it count like 4 cells merge into 1, so it will be like

4 Cells merge = count as 1

If I drag again the formula it counts as 2, 3, 4, 5,

Attached below is my workbook.

Hoping for someone to help me in my problem thank you.

Thank you

Chando
 
Good Day!

Sir Hui,

First I would like to say " Thank you for this fast reply"
I appreciate it thou I don't quite understand How should I be able to add that formula into this formula?

=IF(MONTH($C$1)<>MONTH($C$1-WEEKDAY($C$1,start_day)+(COLUMN(C3)-COLUMN($C$3)+1)),"",$C$1-WEEKDAY($C$1,start_day)+(COLUMN(C3)-COLUMN($C$3)+1))


Sir, If it's not too much,Can you please re-arranged the formula for me.

Thank you :)
 
Good Day!

Sir @Hui

It doesn't work

=IF(MONTH($C$1)<>MONTH($C$1-WEEKDAY($C$1,start_day)+(COLUMN(C3)-2/4+1)),"",$C$1-WEEKDAY($C$1,start_day)+(COLUMN(C3)-2/4+1))
 
If I copy paste the above formula into C3:
Then copy across
I get:

upload_2016-9-6_12-52-59.png

Which is what you asked for ?
 
Hi Sir @Hui

Sorry for inconvenience

I just want to apply the formula that you gave into my existing formula in my IF statement.

Like this one but ( This one is actually working but it counts "4" columns when i drag the formula to the right)

=IF(MONTH($C$1)<>MONTH($C$1-WEEKDAY($C$1,start_day)+(COLUMN(C3)-COLUMN($C$3)+1)),"",$C$1-WEEKDAY($C$1,start_day)+(COLUMN(C3)-COLUMN($C$3)+1))

but when i try to re apply the formula with the one you gave, I can't figure out where should I add the formula in my existing formula like this one below,

We know its wrong but where is the proper place to input your formula into my formula ( IF statement)

=IF(MONTH($C$1)<>MONTH($C$1-WEEKDAY($C$1,start_day)+(COLUMN(C3)-2/4+1)),"",$C$1-WEEKDAY($C$1,start_day)+(COLUMN(C3)-2/4+1))

Your's Trully

Crenson
 
I think it is
C3: =IF(MONTH($C$1)<>MONTH($C$1-WEEKDAY($C$1,start_day) + (INT((COLUMN()-2)/4)+1)), "", $C$1 - WEEKDAY($C$1,start_day) + INT((COLUMN()-2)/4)+1)

Copy across
 
Or......

Change your formula

From this :

=IF(MONTH($C$1)<>MONTH($C$1-WEEKDAY($C$1,start_day)+(COLUMN(C3)-COLUMN($C$3)+1)),"",$C$1-WEEKDAY($C$1,start_day)+(COLUMN(C3)-COLUMN($C$3)+1))

Into this :

=IF(MONTH($C$1)<>MONTH($C$1-WEEKDAY($C$1,start_day)+(INT((COLUMNS($C1:C1)-1)/4)+1)),"",$C$1-WEEKDAY($C$1,start_day)+(INT((COLUMNS($C1:C1)-1)/4)+1))

Regards
Bosco
 

Attachments

:eek:Thank you so much for this master @Hui ,
my problem now is the data inside the box on each date , if changing the date the data inside the box should be also change :):oops:
 
crenson313 said:
Thank you so much @bosco_yip

now the problem is the data inside :confused:
Click to expand...

Difference between :

Formula [1] =IF(MONTH($C$1)<>MONTH($C$1-WEEKDAY($C$1,start_day) + (INT((COLUMN()-2)/4)+1)), "", $C$1 - WEEKDAY($C$1,start_day) + INT((COLUMN()-2)/4)+1)

Formula [2] =IF(MONTH($C$1)<>MONTH($C$1-WEEKDAY($C$1,start_day)+(INT((COLUMNS($C1:C1)-1)/4)+1)),"",$C$1-WEEKDAY($C$1,start_day)+(INT((COLUMNS($C1:C1)-1)/4)+1))

Testing :

Before testing, both formulas give : 1, 2, 3, 4, 5, 6, 7, 8

If you insert 3 columns in Column A,

Formula [1] : Result become : 2, 3, 4, 5, 6, 7, 8, 9

Formula [2] : Result become : 1, 2, 3, 4, 5, 6, 7, 8

Reason :

Causing by COLUMN() of which result will be changed while the data shifting right

And,

COLUMNS($C1:C1) always give 1 disregarding the data shifting right

Regards
Bosco
 
Thank you for info Sir@bosco_yip

but what if I have some data in the squre boxes?? is there anyway to follow the date with the data given inside the boxes? like this one for example?

Respectfully yours,

-crenson
 

Attachments

crenson313 said:
Thank you for info Sir@bosco_yip
but what if I have some data in the squre boxes?? is there anyway to follow the date with the data given inside the boxes? like this one for example?
Respectfully yours,
-crenson
Click to expand...
This is a calendar planner, the "boxes" Data Output as example in the following details:

1] Try to set up a Data Input Table, in your example I put them in Column AK:AO

2] Calendar box range C5:AH9 is your Data Output Table

3] By using a multi-criteria Lookup formula in C5, copy across and down to return the data.

4] Please check as per attached file.

Regards
Bosco
 

Attachments

Last edited:
You must log in or register to reply here.
Back
Top