Hello
I have a report that indicates the lectures that the student "A" had.
I want to create a vba or formula that can fetch the data on criteria "Student Name"
The data sheet may extend to 1000 column and more than 100 students
Hello
I have a report that indicates the lectures that the student "A" had.
I want to create a vba or formula that can fetch the data on criteria "Student Name"
The data sheet may extend to 1000 column and more than 100 students
In C5, enter this array formula with Ctrl + Shift + Enter keys together and drag + fill the formula from C5 to G5.
=INDEX(Data!$C$3:$M$28,MATCH(Report!$E$1,Students,0),MATCH(C3&C4,Data!$C$1:$M$1&Data!$C$2:$M$2,0))
Last edited by leprince2007; 12-02-2016 at 04:19 PM.
Keep the array formula just extend the range. Anyway, give this code a try.
Please Login or Register to view this content.
First,your code isn`t complete because it doesn`t fetch "date field"
Secondly: I want to do the job using formulas.
My knowledge in Excel is insufficient, hopefully someone else will be able to assist you.
Can you complete your code to fetch date field also??
Hi leprince, try maybe an array formula (to C3):
One Formula is it enough... I think... Best regards...Please Login or Register to view this content.
One Formula is it enough... drag and fill this formula (from C3 to G5)... You have Three Formulas in One...
When I change student name to"C" or any other name,I found the formula didn`t fetch date field.
Please note that my data sheet may expand to 1000 column and more than 100 students.
Last edited by leprince2007; 12-02-2016 at 06:29 PM.
While I suppose one could argue that the below formulas for Lecturer and Lectures are the same formula with different INDEX 1st arguments it works because the layout of the data permits it.1- formula to fetch the lecturer
2-formula to fetch the lectures
3-formula to fetch dates
Try array entering this in C3. Then fill down and across to G4. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.Then array enter this in C5 and fill across.Formula:Please Login or Register to view this content.Formula:Please Login or Register to view this content.
Will this work?
Dave
Thanks I will try it and let you know
You have to change my Solution or the Solution of FR, if You have more Columns as in the above Example...
Don't forget to enter this array formula with Ctrl+Shift+Enter...
PS. I like Hurgada...
Last edited by hurgadion; 12-02-2016 at 07:04 PM.
Your solution is great,but I replaced:
with thisPlease Login or Register to view this content.
and I replaced also :Please Login or Register to view this content.
with this:Please Login or Register to view this content.
and I did that in order to simplify the formulas and their results are correct.Is it ok???Please Login or Register to view this content.
Waiting your feedback
Can anyone create a vba code because I tried formulas but they slow down my pc?????
leprince2007,
I did this part
COLUMN($C:$M)-MIN(COLUMN($C:$M))+1 to allow flexibility. Should you insert / deleted columns to the left of the formula this "resets" the first column number to 1 and does it without need for editing. That shouldn't slow the workbook. The same is true of COLUMNS($C:C).
The likely culprit in the slow down are the whole column references such as in MATCH(Report!$E$1,Data!$B:$B,0).
Try replacing the formulas in post #12 with these. They are still array entered. In C3 then filled down and across to G4Then array enter this in C5 and fill across.Formula:Please Login or Register to view this content.The reason for selecting row 1000 is to make adequate room for additional rows.Formula:Please Login or Register to view this content.
If 1000 is not enough edit the formulas to include a little more than what you would anticipate. ie if anticipated need is 500 make it 750 or more. The main thing to avoid with exact matches is referencing whole columns.
My apologies for the oversight.
Thank you sir well done
You are welcome. Thank you for the feedback and the rep.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks