dynamic formula needed

S

starguy

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 |
+-------------------------------------------------------------------+
 
S

starguy

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

Paul Lautman

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

"starguy" wrote:
Click to expand...
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
 
G

Guest

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


---
 
S

starguy

These formulas only work when we assume that 1.1 will always be in col
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 t
2.6 and 3.1 to 3.7) and there is no such way that I could enter them i
a sequence.
e.g if a student choose subjects 2.3, 2.4 and 2.5, I enter them a
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 a
section and in sheet 2.4 at the left of Reg # I want 1 and in sheet 2.
at the left of same Reg # I want 1 as section number of that particula
subject.
I elaborate it more, suppose in sheet 1.2 all students which are paste
in 1.2 sheet do not necessarity have this subject in Main sheet in 2n
blue column this might be in 1st blue column for some students. Same i
the case for all subjects that in subject sheets some of students ma
have that particular subject in first blue column (col E), some ma
have it in second blue column and some may have it in third and som
may have that subject code in fourth blue column.

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

thanks for working Max.
 
G

Guest

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.
Click to expand...

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

---
 
G

Guest

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

starguy

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

however thanks for working.
 
G

Guest

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

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)

---
 
S

starguy

would you please explain it for me that how each part of formula works
and return what? I'll be grateful to you.
 
G

Guest

starguy said:
would you please explain it for me that how each part of formula works
and return what? I'll be grateful to you.
Click to expand...

Did it work well for you ?
Pl confirm.

---
 
S

starguy

Infact it will take some time to implement this formula in my original
workbook that contains large data and references are also not the same
as were in the sample file.
I am also working on another formula for the same purpose and that is
not an array. I will share it with you after I get success to complete
that formula.
 
G

Guest

Here's some key explanations ..

Typical core array expression:
MATCH(1,(Main!$B$6:$B$15=D6)*(Main!$E$6:$E$15=$E$3),0))

The lookup_array part: (Main!$B$6:$B$15=D6)*(Main!$E$6:$E$15=$E$3)
is essentially a product of 2 conditions: = Cond1 x Cond2
which will produce a resultant array of zeros with a single 1,
eg:{1;0;0;0;0;0;0;0;0;0}
where the "1" indicates the row position satifying both conditions (Cond1 &
Cond2)

[Cond1: where Reg# criteria satisfies, Cond2: where subject code criteria
satisfies,
Cond1 x Cond2: where both criteria satisfy]

MATCH(1,{1;0;0;0;0;0;0;0;0;0},0))
then matches the "1" to the array above to return the row position within
the array

INDEX(Main!$F$6:$F$15,MATCH(...))
then returns the contents of the cell within F6:F15 corresponding to the row
position returned by MATCH

The nested:
=IF(ISNA(MATCH(1),IF(ISNA(MATCH(2),...
INDEX(...,MATCH(2))),INDEX(...,MATCH(1)))
will check through in sequence, the possibility of any inputs for the
subject codes: 1.1, 1.2, etc being made within either of cols E,G,I,K
and if so, the corresp INDEX(...,MATCH(..)) will then return the required
result from either of cols F,H,J,L

The implicit assumption of course, is that there will not be any duplicate
subject code inputs.
 
S

starguy

thank you very much Max for your time and effort. I will try it now on
my orginal workbook but it will take some time as I am also working on
another formula which also seems to be a long formula but that is not
an array.
 
G

Guest

starguy said:
thank you very much Max for your time and effort. I will try it now on
my original workbook but it will take some time as I am also working on
another formula which also seems to be a long formula but that is not
an array.
Click to expand...

Would suggest you try implementing the earlier array formula <g>, which
should work fine and is, I believe the simpler/shorter route in this
instance. cheers.

---
 
S

starguy

I will try to implement both formulas but then I check for excel
calculation speed. I have many other formulas in that workbook as well,
some of which are arrays. Infact arrays slow down speed that's why I
avoid to use them. I have to implement this formula in most of my
sheets and in many rows and the reference data is also large. Array
will definitely slow down speed of calculation therefore I have to have
a substitution of this.
I will appreciate if you help me to complete that formula.

Thank you in anticipation

Regards
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Why doe this match nor work? 3
Bar Code Scanning 1
Merging Cells in Excel 1
Lookup Formula 7
Is there an easier way? 5
How to convert text to numbers 5
Matching 15
Percentage Formulas 2

Top