PDA

View Full Version : Vlookup in macros



rajagopal
11-13-2008, 01:05 AM
I've an excel sheet which has two sheets - Form & Consolidated data.

In the sheet 'Form', when i enter the Student number in cell B6, the details in the range B8:B33 to be automatically populated using the data available in the consolidated sheet.
Student number is a unique data.

If the same student number repeats 'n' times, i want 'n' column headings in the sheet form.

If Student no. 1001 appears 10 times, I want all the 10 details under separate column headings like Data1, Data2..Data10.

I attached my working file for your ref.
Please help...

rajagopal
11-13-2008, 02:06 AM
I the below KB entry will help you to count the number of repeated sequences (Student no)
http://vbaexpress.com/kb/getarticle.php?kb_id=708

Bob Phillips
11-13-2008, 02:35 AM
Try this

rajagopal
11-13-2008, 03:22 AM
The data has to be picked up in the Form sheet based on Student no. and not the school name.
The form sheet template will have only one column heading (data1). But the column heading has to increase (data1, data2,...) based on how many times the same Student no. repeated.

For ex. Student no. 1001 repeated 3 times in the consolidated data and hence additional two headings (Data2, Data3) has to be provided in the form sheet and the corresponding details has to be populated in Data1, Data2, Data3 section.
Once the detail populated, user has to get a alert to add new sheet automatically.

In the new sheet, user will again key in the next student no. 1002 -
it is present in the consoldiated data 2 times and hence additional one column heading Data2 to be provided in addition to Data1.

Refer the attached..

MaximS
11-13-2008, 04:30 AM
you can also try version without macro involved. see attachment for details.

all have been used is formulas and conditional formating.

Enjoy.

MaximS
11-13-2008, 04:30 AM
please remove that post - that's browser error.

MaximS
11-13-2008, 04:30 AM
please remove that post - that's browser error.

rajagopal
11-13-2008, 05:58 AM
I am not understanding the sentence "remove browser error"..