I have explained my problem in workbook attached. Please suggest any
formula to solve that problem.
please let me know if I could not explain to make you understand.


+-------------------------------------------------------------------+
|Filename: Sample.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4886 |
+-------------------------------------------------------------------+

--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=551813

I'm getting error trying to go to your posted link - invalid file type
attached.

"starguy" wrote:


I have explained my problem in workbook attached. Please suggest any
formula to solve that problem.
please let me know if I could not explain to make you understand.


+-------------------------------------------------------------------+
|Filename: Sample.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4886 |
+-------------------------------------------------------------------+

--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=551813

I have saved it to my system, there is no such error. It has been saved
and opened withour any error.


--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=551813

One way ..

Try something along these lines

In 1.1,

Placed in C6, array-entered with CSE*:
=INDEX(Main!$F$6:$F$15,MATCH(1,(Main!$B$6:$B$15=D6 )*(Main!$E$6:$E$15=$E$3),0))
C6 is then copied down

*press CTRL+SHIFT+ENTER to confirm the formula
(instead of just pressing ENTER)

Similarly, in 1.2,

Placed in C6, array-entered wih CSE:
=INDEX(Main!$H$6:$H$15,MATCH(1,(Main!$B$6:$B$15=D6 )*(Main!$G$6:$G$15=$E$3),0))
C6 then copied down

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

These formulas only work when we assume that 1.1 will always be in col E
and 1.2 in col G of Main sheet.
Infact I enter subject codes of ACCA (which are from 1.1 to 1.3, 2.1 to
2.6 and 3.1 to 3.7) and there is no such way that I could enter them in
a sequence.
e.g if a student choose subjects 2.3, 2.4 and 2.5, I enter them as
follows.
Reg # Name Father (subjects/Sections)
A1106 GGG FAGGG 2.3 2 2.4 1 2.5 1
so in sheet 2.3 at the left of this Reg # (i-e A1106) I want 3 as
section and in sheet 2.4 at the left of Reg # I want 1 and in sheet 2.5
at the left of same Reg # I want 1 as section number of that particular
subject.
I elaborate it more, suppose in sheet 1.2 all students which are pasted
in 1.2 sheet do not necessarity have this subject in Main sheet in 2nd
blue column this might be in 1st blue column for some students. Same is
the case for all subjects that in subject sheets some of students may
have that particular subject in first blue column (col E), some may
have it in second blue column and some may have it in third and some
may have that subject code in fourth blue column.

I think its more elaborative now and will help to determine the exact
formula.

thanks for working Max.

Max Wrote:
One way ..

Try something along these lines

In 1.1,

Placed in C6, array-entered with CSE*:
=INDEX(Main!$F$6:$F$15,MATCH(1,(Main!$B$6:$B$15=D6 )*(Main!$E$6:$E$15=$E$3),0))
C6 is then copied down

*press CTRL+SHIFT+ENTER to confirm the formula
(instead of just pressing ENTER)

Similarly, in 1.2,

Placed in C6, array-entered wih CSE:
=INDEX(Main!$H$6:$H$15,MATCH(1,(Main!$B$6:$B$15=D6 )*(Main!$G$6:$G$15=$E$3),0))
C6 then copied down

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---


--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=551813

These formulas only work when we assume that 1.1 will always be in col E
and 1.2 in col G of Main sheet.

One way to extend it to cover cols E, G, I, K
[& to extract from the corresponding cols F, H, J, L] ..

Select cell C6 in sheet: 1.1, then group all sheets: 1.1 to 3.7 (hold down
SHIFT & select the rightmost sheet: 3.7), then array-enter in C6, and copy
down as far as required:

=IF(ISNA(MATCH(1,(Main!$B$6:$B$15=D6)*(Main!$E$6:$ E$15=$E$3),0)),
IF(ISNA(MATCH(1,(Main!$B$6:$B$15=D6)*(Main!$G$6:$G $15=$E$3),0)),
IF(ISNA(MATCH(1,(Main!$B$6:$B$15=D6)*(Main!$I$6:$I $15=$E$3),0)),
IF(ISNA(MATCH(1,(Main!$B$6:$B$15=D6)*(Main!$K$6:$K $15=$E$3),0)),"",
INDEX(Main!$L$6:$L$15,MATCH(1,(Main!$B$6:$B$15=D6) *(Main!$K$6:$K$15=$E$3),0))),
INDEX(Main!$J$6:$J$15,MATCH(1,(Main!$B$6:$B$15=D6) *(Main!$I$6:$I$15=$E$3),0))),
INDEX(Main!$H$6:$H$15,MATCH(1,(Main!$B$6:$B$15=D6) *(Main!$G$6:$G$15=$E$3),0))),
INDEX(Main!$F$6:$F$15,MATCH(1,(Main!$B$6:$B$15=D6) *(Main!$E$6:$E$15=$E$3),0)))

[ Above formula is all within a single cell, C6 ]

Then right-click Ungroup sheets
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"starguy" wrote:
These formulas only work when we assume that 1.1 will always be in col E
and 1.2 in col G of Main sheet.
In fact I enter subject codes of ACCA (which are from 1.1 to 1.3, 2.1 to
2.6 and 3.1 to 3.7) and there is no such way that I could enter them in
a sequence.
e.g if a student choose subjects 2.3, 2.4 and 2.5, I enter them as
follows.
Reg # Name Father (subjects/Sections)
A1106 GGG FAGGG 2.3 2 2.4 1 2.5 1
so in sheet 2.3 at the left of this Reg # (i-e A1106) I want 3 as
section and in sheet 2.4 at the left of Reg # I want 1 and in sheet 2.5
at the left of same Reg # I want 1 as section number of that particular
subject.
I elaborate it more, suppose in sheet 1.2 all students which are pasted
in 1.2 sheet do not necessarity have this subject in Main sheet in 2nd
blue column this might be in 1st blue column for some students. Same is
the case for all subjects that in subject sheets some of students may
have that particular subject in first blue column (col E), some may
have it in second blue column and some may have it in third and some
may have that subject code in fourth blue column.

I think its more elaborative now and will help to determine the exact
formula.

thanks for working Max.

Click "View this thread" URL

"JLatham" wrote:

I'm getting error trying to go to your posted link - invalid file type
attached.

"starguy" wrote:


I have explained my problem in workbook attached. Please suggest any
formula to solve that problem.
please let me know if I could not explain to make you understand.


+-------------------------------------------------------------------+
|Filename: Sample.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4886 |
+-------------------------------------------------------------------+

--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=551813

Thanks, to you and Paul Lauterman below for that info. I had clicked on the
first link next to Download: and hit the error. Since the game is over now,
I'll go back to sleep. But I'll remember the problem at excelforum.com in
the future.

"Toppers" wrote:

Click "View this thread" URL

"JLatham" wrote:

I'm getting error trying to go to your posted link - invalid file type
attached.

"starguy" wrote:


I have explained my problem in workbook attached. Please suggest any
formula to solve that problem.
please let me know if I could not explain to make you understand.


+-------------------------------------------------------------------+
|Filename: Sample.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4886 |
+-------------------------------------------------------------------+

--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=551813

thank you Max I will try to understand it because its a lengthy
formula.

however thanks for working.


--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=551813

"starguy" wrote:
thank you Max I will try to understand it because its a lengthy
formula. however thanks for working.

You're welcome. Pl test it out at your end.

The "lengthy" formula is essentially an extension of the earlier,
with nested IFs used to iterate the checks/returns through
all 4 columns that inputs are likely (in cols E,G,I,K),
with corresponding returns (from cols F,J,H,L)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

JLatham wrote:
I'm getting error trying to go to your posted link - invalid file type
attached.

"starguy" wrote:
This is due to a bug in excelforum. All attempts to get whoever it is that
supposedly looks after excelforum to fix it have fallen on deaf ears.

Change ?postid=4886 to ?attachmentid=4886 and it'll work



I have explained my problem in workbook attached. Please suggest any
formula to solve that problem.
please let me know if I could not explain to make you understand.


+-------------------------------------------------------------------+
Filename: Sample.zip |
Download: http://www.excelforum.com/attachment.php?postid=4886 |
+-------------------------------------------------------------------+

--
starguy
------------------------------------------------------------------------
starguy's Profile:
http://www.excelforum.com/member.php...o&userid=32434
View this thread:
http://www.excelforum.com/showthread...hreadid=551813

Thanks for that info. I finally figured it out, someone else told me to
click the 'View this thread' link and that worked for me.

At least they permit/enable file uploading, which certainly facilitates
giving assistance in 9 out of 10 cases around here.

"Paul Lautman" wrote:

JLatham wrote:
I'm getting error trying to go to your posted link - invalid file type
attached.

"starguy" wrote:
This is due to a bug in excelforum. All attempts to get whoever it is that
supposedly looks after excelforum to fix it have fallen on deaf ears.

Change ?postid=4886 to ?attachmentid=4886 and it'll work



I have explained my problem in workbook attached. Please suggest any
formula to solve that problem.
please let me know if I could not explain to make you understand.


+-------------------------------------------------------------------+
Filename: Sample.zip |
Download: http://www.excelforum.com/attachment.php?postid=4886 |
+-------------------------------------------------------------------+

--
starguy
------------------------------------------------------------------------
starguy's Profile:
http://www.excelforum.com/member.php...o&userid=32434
View this thread:
http://www.excelforum.com/showthread...hreadid=551813