Link Names and Add Column

Hi, i am trying to build a report that will by a users entry in a userform, add the correct layers (Senior Manager to Agent Level) to all the required sheets.

I have attached a sample workbook, that might help to understand what i am trying to do a bit better.

I have a worksheet "StaffList" that basically gives you a breakdown of the structure.

What i would like is for example if u enter "L4 B" in the userform, then the L4 Report sheet will add the name in cell B2 and then from columns H onwards add the "L5" that belong to L4.

For example L4 B would show L5 BA in cell H3 and in cell I3 it would show L5 BB.

If somebody could please help me to automate this, would really appreciate it. I thought I'd start off with the L4 Sheet and then should be able to apply same method to the other sheets.

Thanks in advance.

Hi,

Why sheet "L4 Report" is equal to "L5 Report"?
Can you show us the result in a sheet when you enter "L4 B" or "L5 AA" in the form?

Elio

efernandes67, thanks for your response. Apologies at present the L5 needs to be ignored. I have attached a results sheet that shows what the L4 Report should look like if "L4 B" is inputted into the user form.

In regards to the L5, L6 and L7 Sheets I think I may need to change the userform and add a different input for each one which would then take you to the specific sheet, but that is something that i will have to work on after i work this bit out.

thanks once again.

Tell me what is the result if you insert "L4 A" and "L4 C".

efernandes67, i have attached an example of what the sheets should look like if L4 A or L4 C is entered. The results should show up within the L4 Report Sheet but for illustration i have added 2 extra sheets with how the results should look.

thanks once again

I believe that I found a soltion for your problem.

I set a table called "tbStaff" to your Staff list (A1:D19)
I created a pivot table called "ptStaffList" just for "L4" users.

I created a name range "LstStaffRecord" for the results of the pivot table.

Please Login or Register  to view this content.

I add some code to your form "cmdID"

Please Login or Register  to view this content.

put this code in a module

Please Login or Register  to view this content.

The formatting is up to you.

Elio

Thanks for that, will try that and see how I get on. Just out of curiosity is this something that can be done without a pivot table? Or is that the only way?

Thanks once again...

If you give me your e-mail I can send you the workbook. I know another way, but I think this way is easy.

Elio

elio, sent u a private message yday... thanks for all ur help.

elio, apologies... just wondering if u had a chance of sending the file? thanks once again

I have done what efernandes posted, but when trying the following:

"I created a pivot table called "ptStaffList" just for "L4" users." i cant create this just for L4 users?

any reasons why? any help would be appreciated.. thanks.