Auto-fill data based on two drop-downs

[paullew17]

Good morning everyone, I am looking for some help with quite a specific problem I have if possible please.

I have a workbook to track my teams productivity in 8 areas. I have created a tab for each individual within the workbook which I populate with their numbers each month.

What I would like to do, is create an additional tab in that workbook with two drop-downs. One for name and one for month, so that when I select their name from drop-down one, and the month from drop-down two, it will auto-fill their numbers from their individual sheet into the new tab so it can be printed.

Is there a way to do this?

Many thanks in advance

[Glenn Kennedy]

Welcome, but... it's as clear as mud!!



Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? However, please give us an indication of the approximate number of rows of data you want the solution to work with (100, 1000, 100,000 or whatever). Please don't attach a picture of an Excel sheet (no-one will want to re-type all your stuff before starting).

1. It does NOT have to be your real sheet - mock up a SAMPLE if you need to. But not 1000's of rows!!! It makes manual checking so tedious. Whatever you do... make sure that all confidential information is removed first!!

2. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

3. Make sure that your desired solution is also shown (mock up the results manually).

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[paullew17]

Many thanks for your reply Glenn, attached as requested

[paullew17]

attachment

[Glenn Kennedy]

Nope. Please re-read Point 3. I have no idea what you expect to see in any of the merged cells marked "Autofill here".

Why are you using merged cells?

[paullew17]

re upload as requested with example page

I dont have to use them, its more aesthetics really

The areas that where marked autofill here need to be the relevant data from the agent sheet, based upon the name and month. For example. if changed to agent 2 and February, it should autofill from the agent 2 sheet and febs data

[Glenn Kennedy]

1. Kill ALL of the merged cells. Every one. No exceptions. They are the work of Satan and will drag you down in no time.

2. Make sure the months in the column headers and the DD match. Jan is not the same as January.

3. Use this formula:

=INDEX(INDIRECT("'"&$B$1&"'!C5:N12"),MATCH(B7,INDIRECT("'"&$B$1&"'!B5:B12"),0),MATCH($H$4,INDIRECT("'"&$B$1&"'!C4:N4"),0))

I have only done this for the first one. Try to do the rest yourself.

If you learn only one thing here today - let it be that merged cells are horrible and should be uninvented.

[paullew17]

wow! Thank you so much, it works perfectly!