• 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.

Formula help to show random result in one place

W

witsonjoyet

New Member
Good Day,
I have an excel sheet having IF formulae (=if(TEXT(D1,"DD-MM")=TEXT(TODAY(),"DD-MM"),"BIRTHDAY","") in D1:D50 and result will be displayed in E1:E50. here the problem is when ever the if formula is trun true it will show the result randomly.(i.e. where ever its turn true it will show birthday in that cell). so i have to use scroll bar to see the results.

Now i want to show all these results in one place only. Is there any formula or any macro to do like this ?

for Ex. E1 , E7 , E15 , E25 ,E50 Cells having the birthday results then i want to show all these 5 results in one place like F1:F5.

Regards

Witson Joyet
 
Thanks a lot .. Really for past few months i have been facing problem with this query.. Thanks once again

Regards

Witson
 
Hai,
I have gone through the link which you have provided me and do as same as that link. but i got error answer that is the first name only repeated in the array. I am new to arrays . Please guide me . I have attached my file for your referance

Regards
 

Attachments

  • EMP DETAILS.xlsm
    25.1 KB · Views: 6
Dear Witson

The problem is that you copied the formula down before entering it as an array formula. You need to array enter the formula (using ctrl+shift+enter) and then copy it down.
 
Hi Witsonjoyet,

Can you upload the file with the results that you want to see, so that I know what you are after.

cheers

kanti
 
Hi,

Please refer uploaded file, I have made a correction to your formula
 

Attachments

  • EMP DETAILS_2.xlsm
    24.3 KB · Views: 11
Thank you for your valuable suggestions , quick reply and support .

kindly explain me why it was not worked previously. i have observed that the cell Row(i1) is continuously changing in your rectified file. but when i tried to do the same its not been changed . What exactly should i do to write the array formulas
 
Hi, witsonjoyet!
Do you have Automatic Calculation enabled? Press F9 to test it, and if it then changes, go to Formula tab, Calculation group, Calculation Option icon, and check Automatic option.
Regards!
 
SirJB7,
The problem is how you enter and copy the Array formula, which in some instances does not change the relative reference in the formula, I fixed the formula, but could not explain the reason why it happened. You will note in the formula where it refers to I1, this reference did not change in the original file, I subsequently changed the formula.

It would be a good idea for someone to do a write-up on editing array formulas, to get past the "cannot change part...."

Thanks

Kanti
 
@kchiba
Hi!

I actually answered to OP without having compared previous and latest version of your file, I opened this last one, didn't see anything strange, and regarding the text of the question I tried go assure that it wasn't a calculation mode issue.

About your idea, it's a good one, we usually write a bit whenever an issue is raised and the receiver wants to understand what received. Today I've wrote this, it's about array formulas but not on how to copy/move:
http://chandoo.org/forum/threads/shortest-route.13469/#post-79509

My workaround when having to edit an array formula that spreads thru a vector (1 dimension) or a matrix (2 dimension) range is this:
a) Edit the top left cell, or any intermediate but significant cell regarding the edition involved
b) When it works, if not the top left cell, delete top left and copy edited to top left
c) Delete all other formulas in the range (i.e., all range but top left cell)
d) Copy top left across the range
e) Copy 1st range row down the range

Quick & dirty, but effective.

Your idea it's quite good, surely someone in a fit of inspiration will put it into words. BTW, why not you? :) I'm being tempted to do the job, but my always winner laziness keeps on with its habit. :p

Regards!
 
kchiba said:
SirJB7,
The problem is how you enter and copy the Array formula, which in some instances does not change the relative reference in the formula, I fixed the formula, but could not explain the reason why it happened. You will note in the formula where it refers to I1, this reference did not change in the original file, I subsequently changed the formula.

It would be a good idea for someone to do a write-up on editing array formulas, to get past the "cannot change part...."

Thanks

Kanti
Click to expand...
Hi Kanti ,

The reason the array formula does not change the relative reference is because of the way it has been entered.

Array formulae are of two types :

1. Where the array formula is entered in a single cell , using CTRL SHIFT ENTER.

2. Where the array formula is entered over multiple cells , first selecting the range of multiple cells , and then entering the array formula using CTRL SHIFT ENTER. In such a case , the formula will be identical in all the cells of the range.

Neither of these formula types will work if entered the other way i.e. a formula which needs to be entered in a single cell and copied down , will not work if entered the second way ; the second type of formula will not work if entered the first way and copied down.

To illustrate the second , consider a formula such as :

=FREQUENCY(ROW($1:$5),ROW($1:$5))

=FREQUENCY(ROW(1:5),ROW(1:5))

=FREQUENCY({1;2;3;4;5},{1;2;3;4;5})

If any of the above formulae is entered in a single cell , even using CTRL SHIFT ENTER , and copied down to multiple rows , it will not give the correct result ; the only way it will give the correct result is if the appropriate range of cells ( in this case 6 ) is selected first , and then the formula is array entered using CTRL SHIFT ENTER.
Here the formula in all the 6 cells will be identical , but the outputs in each cell will be calculated correctly , and can be different depending on the input data.

The difference between the first formula , which uses absolute references , and the second which uses relative references , does not matter when the formula is entered correctly.

An example of the first formula type being entered the second way is any formula which uses the INDEX / SMALL combination ; in this , the ROW(A1) is being used , and it is expected that when the formula is array entered in one cell , and then copied down , the A1 increments to A2 , A3 ,...

However , if this same formula is array entered by first selecting a multiple-row range , and then array entering the formula using CTRL SHIFT ENTER , then all the cells in the range will have an identical formula , and the output will also be identical.

Narayan
 
Last edited:
Hi Narayan,

If you follow from the start of the post you will note that I did fix the formula for the OP.

Thanks for the explanation, I am sure that this will be very useful for many people, and thus I think we should put it somewhere where it can be used as a reference.

In fact a full write-up on Array formulas will be useful, I am aware that there a a number of great articles on in other places in the web, but I notice that more and more people do not search elsewhere and hope to find the replies here.

Cheers
 
Hai friends,

Thanks for your suggestions.Now the formula works perfectly. For your reference i have attached my file . This file is for Birthdays reminder for that day and for coming 30 days birthdays.

1)In this file for Today's Birthday array formula in reminder sheet , i notice that #NUM error is coming. How to correct this ?

2)How can i change the formula to show the date and month too for those having the birthday. Suppose in my file three members having birthdays in December month. then is it possible to show like Priyanka - Dec 1st ; Ashish - Dec 18th like this ?

Regards
Witson Joyet
 

Attachments

  • Birthday Reminders for upload.xlsm
    27.7 KB · Views: 5
Hi Witson ,

Regarding your first problem , just change the < sign in the check to <= , as follows :

Code: 
=+IF(COUNTIF(Sheet1!I:I,Sheet1!$I$1)<=ROWS($D$5:D5),"",INDEX(Sheet1!C:C,SMALL(IF(Sheet1!$I$2:$I$50=Sheet1!$I$1,ROW(Sheet1!$I$2:$I$50)),ROW(Sheet1!I1))))
Narayan
 
Hai all,

Again i came with some VBA doubt for my previous question. at last i have prepared nice excel birthday reminder which shows today's birthdays and coming 30 days birthdays. and i have put one button which sends mail to that concern person.

1)Now coming to the point in my VBA code allows me to send one mail to the one person. but now i want to change it as to send the mail to the first person (who has birthday on that day) and cc to all my colleagues.

2) The file don't allow me to send the wishes mail more than one person ,i.e. if today three people has birthdays then i should send mails to three of them. but in this macro it sends to one person only. How can i change the code as it allows to send mail to everyone.

3) If i want to send two people only is it possible to send as per my wish ?

And i wish to use this same file for give mails to festival wishes too.For festivals i have to send mail to every one right? so in another module (Mail to Everyone) which code should i write?

Kindly find the attached file for your reference.

Regards

Witson Joyet
 

Attachments

  • Birthday Reminders.xlsm
    137.9 KB · Views: 4
You must log in or register to reply here.
Back
Top