Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two different criterias in one formula?
A time-table consists of 6 different groups and 5 different subjects. To
verify the correct subjects for each group, I must use a formula for the following: See if group AB in the range C11:J42 has Engineering Science. See if group DC in the same range has Mathematics. See if group EF in the same range has Industrial Electronics N1. I tried the IF function but no go. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two different criterias in one formula?
Just to clear up some uncertainty - below is an abbreviated range of the
actual worksheet (Sheet 1). On a seperate worksheet (Sheet 2) with all subjects and groups for cross reference, I tried formulas (IF,AND,SUMIF,AND,OR, etc.) to mark with an "x" which subjects had been assigned to which group. For example: =IF(AND(B20:E24="L2DM",B20:E24="Eng. Drawing"),"x","")) This is a HUGE worksheet (1000+ students) and drawing up this time-table to mark on a seperate worksheet (Sheet 2) if Mathematics has been assigned to group L2DM, got me completely baffled. Some cells are merged as well (in case where one subject has to be assigned to two groups). A B C D E 19 Time Group Subject Group Subject 20 07:45 L2BM Eng. Science L2FT Mathematics 21 L2DM Motor Theory L2EL Ind. Electronics 22 23 08:45 L2BM Platers' Theory L2FT F & M Theory 24 L2DM Eng. Drawing L1EL Mathematics Somehow I don't see the light at the end of the curved tunnel! Thanx |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two different criterias in one formula?
If cells have been merged, the recommendation would be to throw the problem
back at whichever bright spark did such a stupid thing to the worksheet. Merging is very rarely the sensible solution. -- David Biddulph "Chris m=ec" wrote in message ... Just to clear up some uncertainty - below is an abbreviated range of the actual worksheet (Sheet 1). On a seperate worksheet (Sheet 2) with all subjects and groups for cross reference, I tried formulas (IF,AND,SUMIF,AND,OR, etc.) to mark with an "x" which subjects had been assigned to which group. For example: =IF(AND(B20:E24="L2DM",B20:E24="Eng. Drawing"),"x","")) This is a HUGE worksheet (1000+ students) and drawing up this time-table to mark on a seperate worksheet (Sheet 2) if Mathematics has been assigned to group L2DM, got me completely baffled. Some cells are merged as well (in case where one subject has to be assigned to two groups). A B C D E 19 Time Group Subject Group Subject 20 07:45 L2BM Eng. Science L2FT Mathematics 21 L2DM Motor Theory L2EL Ind. Electronics 22 23 08:45 L2BM Platers' Theory L2FT F & M Theory 24 L2DM Eng. Drawing L1EL Mathematics Somehow I don't see the light at the end of the curved tunnel! Thanx |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two different criterias in one formula?
Dear Sir,
Thank you kindly for replying, I am the bright spark that did "such a stupid a thing to the worksheet" because the function is there to be used if needed. This is a discussion group to help each person in need. Rather evaluate the problem instead of the person! Don't let merging blinds you. The real problem is, what formula or combination of formula's can be used to show to me if a subject has been already assigned to a specific classgroup in a range as per example. Thanx again "David Biddulph" wrote: If cells have been merged, the recommendation would be to throw the problem back at whichever bright spark did such a stupid thing to the worksheet. Merging is very rarely the sensible solution. -- David Biddulph "Chris m=ec" wrote in message ... Just to clear up some uncertainty - below is an abbreviated range of the actual worksheet (Sheet 1). On a seperate worksheet (Sheet 2) with all subjects and groups for cross reference, I tried formulas (IF,AND,SUMIF,AND,OR, etc.) to mark with an "x" which subjects had been assigned to which group. For example: =IF(AND(B20:E24="L2DM",B20:E24="Eng. Drawing"),"x","")) This is a HUGE worksheet (1000+ students) and drawing up this time-table to mark on a seperate worksheet (Sheet 2) if Mathematics has been assigned to group L2DM, got me completely baffled. Some cells are merged as well (in case where one subject has to be assigned to two groups). A B C D E 19 Time Group Subject Group Subject 20 07:45 L2BM Eng. Science L2FT Mathematics 21 L2DM Motor Theory L2EL Ind. Electronics 22 23 08:45 L2BM Platers' Theory L2FT F & M Theory 24 L2DM Eng. Drawing L1EL Mathematics Somehow I don't see the light at the end of the curved tunnel! Thanx |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two different criterias in one formula?
You are welcome to use merging if it suits you. The advice from many
experts in this newsgroup (and elsewhere) is to avoid it if at all possible. Good luck with your problem. -- David Biddulph "Chris m=ec" wrote in message ... Dear Sir, Thank you kindly for replying, I am the bright spark that did "such a stupid a thing to the worksheet" because the function is there to be used if needed. This is a discussion group to help each person in need. Rather evaluate the problem instead of the person! Don't let merging blinds you. The real problem is, what formula or combination of formula's can be used to show to me if a subject has been already assigned to a specific classgroup in a range as per example. Thanx again "David Biddulph" wrote: If cells have been merged, the recommendation would be to throw the problem back at whichever bright spark did such a stupid thing to the worksheet. Merging is very rarely the sensible solution. -- David Biddulph "Chris m=ec" wrote in message ... Just to clear up some uncertainty - below is an abbreviated range of the actual worksheet (Sheet 1). On a seperate worksheet (Sheet 2) with all subjects and groups for cross reference, I tried formulas (IF,AND,SUMIF,AND,OR, etc.) to mark with an "x" which subjects had been assigned to which group. For example: =IF(AND(B20:E24="L2DM",B20:E24="Eng. Drawing"),"x","")) This is a HUGE worksheet (1000+ students) and drawing up this time-table to mark on a seperate worksheet (Sheet 2) if Mathematics has been assigned to group L2DM, got me completely baffled. Some cells are merged as well (in case where one subject has to be assigned to two groups). A B C D E 19 Time Group Subject Group Subject 20 07:45 L2BM Eng. Science L2FT Mathematics 21 L2DM Motor Theory L2EL Ind. Electronics 22 23 08:45 L2BM Platers' Theory L2FT F & M Theory 24 L2DM Eng. Drawing L1EL Mathematics Somehow I don't see the light at the end of the curved tunnel! Thanx |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two different criterias in one formula?
Thanx again for replying.
I used merging a lot of times the past 12 years with Excel and didn't run into unsolvable problems. I took your advise and unmerge but still it doesn't solve my nightmare, so I'm going to post this problem again. "David Biddulph" wrote: You are welcome to use merging if it suits you. The advice from many experts in this newsgroup (and elsewhere) is to avoid it if at all possible. Good luck with your problem. -- David Biddulph "Chris m=ec" wrote in message ... Dear Sir, Thank you kindly for replying, I am the bright spark that did "such a stupid a thing to the worksheet" because the function is there to be used if needed. This is a discussion group to help each person in need. Rather evaluate the problem instead of the person! Don't let merging blinds you. The real problem is, what formula or combination of formula's can be used to show to me if a subject has been already assigned to a specific classgroup in a range as per example. Thanx again "David Biddulph" wrote: If cells have been merged, the recommendation would be to throw the problem back at whichever bright spark did such a stupid thing to the worksheet. Merging is very rarely the sensible solution. -- David Biddulph "Chris m=ec" wrote in message ... Just to clear up some uncertainty - below is an abbreviated range of the actual worksheet (Sheet 1). On a seperate worksheet (Sheet 2) with all subjects and groups for cross reference, I tried formulas (IF,AND,SUMIF,AND,OR, etc.) to mark with an "x" which subjects had been assigned to which group. For example: =IF(AND(B20:E24="L2DM",B20:E24="Eng. Drawing"),"x","")) This is a HUGE worksheet (1000+ students) and drawing up this time-table to mark on a seperate worksheet (Sheet 2) if Mathematics has been assigned to group L2DM, got me completely baffled. Some cells are merged as well (in case where one subject has to be assigned to two groups). A B C D E 19 Time Group Subject Group Subject 20 07:45 L2BM Eng. Science L2FT Mathematics 21 L2DM Motor Theory L2EL Ind. Electronics 22 23 08:45 L2BM Platers' Theory L2FT F & M Theory 24 L2DM Eng. Drawing L1EL Mathematics Somehow I don't see the light at the end of the curved tunnel! Thanx |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
mutiple criterias formula | Excel Worksheet Functions | |||
Too many criterias... | Excel Worksheet Functions | |||
If Criterias | Excel Discussion (Misc queries) | |||
Formula format for Count or Countif funtion with two criterias | Excel Worksheet Functions | |||
Formula requiring two different criterias | Excel Worksheet Functions |