vlookup, drop down, indirect function

[shingudaze]

Hello, thanks to member assistance I have almost completed my task. I appreciate your time and guidance.

I am at the last piece of the puzzle but am stuck.

I have a workbook with 2 worksheets, Main and Data.
On the main sheet I would like to enter my customer's account number in column A. From that, I would like vlookup to populate the customer's company name in column B, have Data Validation return a drop down box with contact names related only to that account number which I can pick from in column C, and then have vlookup populate column D and E with the email and phone number of the contact I selected from the drop down.

The vlookup parts I can do, but I can't get the drop down box to populate. I would like to keep the "Data" worksheet information organized the way it currently is.

I have attached a sample sheet with the problem. Can someone please help to fix the error of the drop down box, and get it to populate with the correct contact names associated to the company/acct#?

ContactSamp.xlsx

Thank you!

[hemesh]

copy below then go to formulas-> Name Manager---> select New --> give it a name as DROPDOWN in source box paste
=INDEX(Data!$C$2:$C$13,MATCH(Main!$B2,Data!$B$2:$B$13,0)):INDEX(Data!$C$2:$C$13,MATCH(Main!$B2,Data!$B$2:$B$13))

then select the range for data validation --> go to data--> data validation--> List in Source box typr =DROPDOWN

[shingudaze]

Thank you, almost working. I'm not sure why, but the drop down is only allowing me to select from the first list of contacts in the data sheet.

My formula in the name manager is: =INDEX(Data!$D$2:$D$91,MATCH(Main!$A4,Data!$A$2:$A$91,0)):INDEX(Data!$D$2:$D$91,MATCH(Main!$A4,Data!$A$2:$A$91))

No matter what acct# i enter in columns A4 to A10 of the main sheet, I am only able to select the contacts from whatever is entered into A4. In other words, the first row is working perfectly, but the others are not...

Also, if I copy the data validation dropdown using a ctrl-c/ctrl-v, it displays all contacts for all accounts no matter what I enter into A5 to A10

Any idea?

Thank you.

[hemesh]

Are you sure formula is same in name manager.
delete data validation then
First Select all the cells where you want to apply data validation. Then go to --> data validation and then enter the formula Or else

[shingudaze]

Yes, I'm not sure.. I selected all the cells, applied data validation using =DROPDOWN and the formula above =INDEX(Data!$D$2:$D$91,MATCH(Main!$A4,Data!$A$2:$A$91,0)):INDEX(Data!$D$2:$D$91,MATCH(Main!$A4,Data!$A$2:$A$91))

Again, it works for the very first cell (in my case N4), looking up from A4 and returning the three contacts from that account, but all the other cells N5 to N150 show all contacts. I confirmed that if I choose cell N6 for example, then check name manager, the formula references $A6 correctly as well. I don't understand...

[shingudaze]

Here is some more information... I tried to re-enter the formula from the beginning and received an error: "The Source currently evaluates to an error. Do you want to continue?"

Also, the Excel changed the name manager formula to: =INDEX(Data!$D$2:$D$91,MATCH(Main!$A1048486,Data!$A$2:$A$91,0)):INDEX(Data!$D$2:$D$91,MATCH(Main!$A1048486,Data!$A$2:$A$91))

Thank you.

[hemesh]

Ohk, May be you are getting wrong results because of the same. correct the formula and try again or upload a sample book where it is actually happening (remove sensitive information)

You will find a icon(at the end) in the source tab, where you put the formula, in the name manager . Once you enter the formula click the icon and it will take you to the results if it shows correct results then your formula is correct.

[shingudaze]

It looks like there was an issue with me trying to get the information from the acct# instead of the company name. So I did things a different way, I recreated my entire pipeline sheet around your original example spreadsheet.

The reason I wanted to go by acct# instead of company name is that from internal system to internal system around here, the spelling of some of the accounts may change. For example, in one system it a company may be referred to as ABC, INC. but in another it may show as just Advanced BC, Inc. It's a pain, but as I only really need to pull from one report, I have renamed all my accounts to match what the reporting system I use shows, and now it works.

Thanks again for your help Hemash. I think the takeaway here is for me to get used to the name manager, and the index/match features!

Have a great weekend.

[hemesh]

You are welcome and Thanks for the feedback !