Using cell value in formula

[jb]

Hi Experts,

I have an excel file consisting of many subject-wise sheets having marks of students of different class.

Each sheet is given name based on subject. Each sheet may have different number of students but format of each sheet is same.

First column is roll number ,second column is name and third column is marks.

With the help of this website, a dynamic list of students is prepared on a separate excel sheet where only those students should appear who were Absent or Cancelled in particular subject.

Now I am trying to make this sheet dynamic.

1.

Name of worksheet used in formula is fixed and when subject changed, editing is to be done in formula. e.g. MARK-RDBMSI, MARK-ADFS, MARK-SPM, MARK-IT etc. In non-submitted worksheet, title of subject is written. e.g. RDBMSI, ADFS, SPM, IT etc. so is it possible the concatenation of fixed part "MARK-" and subject title to be used in formula? so that we need not require to edit formula?

2.

Number of students are also different for different subjects. So if I type number of students alongwith subject title e.g. 49 and data is available on different sheets from row 5. so if number of candidates are 49 then range will be row 5 to row 53 i.e 49+4. f number of candidates are 8 then range will be row 5 to row 12 i.e 8+4.

I want to use this value in formula wherever fixed 53 value is referred which is required to be edited everytime if number of candidated changed. Is it possible?

Note: I do not want my end user to type 53 or 12 as number of candidates. They will type actual number of candidates.

 

[NARAYANK991]

Hi ,

See if this is OK.

Narayan

 

[jb]

Hi ,

See if this is OK.

Narayan

Thank you NARAYANK991 sir. My 1st problem is solved. Now I don't have to edit formula for subject title.

But sir, my second problem is still unsolved. i.e. formula contains range from a5 to a53 or c5 to c53. My data will always start from from row 5. But the total number of candidates will decide the last row. for e.g. if number of students are 8 then last row will be 12. if number of students are 49 then last row will be 53. i.e. 8+4=12, 49+4=53. so I have entered number of students in bold red color. According to that number, formula sould take range. Where lower limit 5 is fixed but upper limit should be total number of students+4.

Thanks in advance sir.

 

[bosco_yip]

Thank you NARAYANK991 sir. My 1st problem is solved. Now I don't have to edit formula for subject title.

But sir, my second problem is still unsolved. i.e. formula contains range from a5 to a53 or c5 to c53. My data will always start from from row 5. But the total number of candidates will decide the last row. for e.g. if number of students are 8 then last row will be 12. if number of students are 49 then last row will be 53. i.e. 8+4=12, 49+4=53. so I have entered number of students in bold red color. According to that number, formula sould take range. Where lower limit 5 is fixed but upper limit should be total number of students+4.

Thanks in advance sir.

Try……….

In A1, enter formula and copy to D1,G1 & J1

=INDEX(INDIRECT("'"&"Mark-"&B$1&"'" &"!"&"$A:$A"),MATCH(1E+307,INDIRECT("'"&"Mark-"&B$1&"'" &"!"&"$A:$A")))

Regards
Bosco

 

[narunfca]

Hi,

Slightly modified the solution of NARAYANK991. Please check if it is ok.

Arun N

 

[jb]

thanks a lot. working perfectly. thanks again.