Pulling information from another workbook

Hello,

I am here looking for advice as to what the best thing to do with my problem.
The problem itself is that I have a drop down box with a large list of companies.
The requirement is that when one of these companies is selected the company information will appear in a couple of other cells below that drop down box. The data is in another workbook. I looked at an IF function that said if this company is in the box then display its information in this cell, but I can't put more than seven nested if statements in one cell.
So my question is this a VBA solvable problem or is there another function I can use?

Thanks you

I must apologise I meant a different sheet

but here is an example of my problem as the data is private

I can't find go advanced

I found it but doesn't seem to be working

Basically in one sheet (sheet1) I have a drop down list with 6 lines of data in:

Gigantic Company
Huge Company
Big Company
Medium Company
Small Company
Tiny Company

Below that I have a cell which will have the data in.

Then in another sheet (sheet2) I have the data for each company

So how do I pull that data in to that single cell in sheet 1?

I tried doing what you said and it still doesn't allow me to attach a file.

I go through the 'Go Advanced' link, then to the 'Manage Attachments' link and there are no instructions, just the standard manage attachments page, which doesn't work

To attach an Excel file to your post,

• desensitize data
• remeber that your example should reflect structure and type of data
• click Go Advanced,
• scroll down until you see Manage Attachments,
• click that and select Browse,
• select your file and click Open,
• click Upload and you will see your attachment below Upload Files from a website
• click Close this window,
• click Submit reply

After that you should see attachment in your post

I choose a file to upload, I then click upload and I get an error message saying 'Please select a file to upload'
I will try from a personal device

My personal device seems to have worked

Is that what you want?
but with this example and to see how it works you'll need PowerQuery and macro enabled workbook

Sandy powerquery will work great if eventually you still want to pull out even more information.

If it is just pulling out Sheet2 column C data, maybe you can look at vlookup or index match to retrieve the data.

Under Sheet1 D5, try insert this =IFERROR(INDEX(Sheet2!C:C,MATCH(Sheet1!D3,Sheet2!B:B,0)),"")

The actual data set is around 60 entries, so what would be the best approach?

60 columns or 60 rows?
doesn't matter - you can use PowerQuery as well

So say what I had in sheet2 Gigantic Company - 10000
Huge Company - 5000
etc
I have 60 rows of data like that

and where is the problem?

attach representative example excel file

So here is another example of my data. In all on sheet two i have around 60 entries.

On sheet one i have a dropdown box with those companies on sheet 2 listed.

When a user selects a company from that list, the the cell highlighted on sheet one, the data for that company would appear from sheet two

And is there a way of doing it without powerquery?
Thank you

ok, here is an example with 100 companies and values
with duplicates or not
selected company in PivotTable will be highlighted on the list - sheet1

Just wanted to say I solved the issue with an index match function, I suppose VLookup would have worked too.

With theses functions I was able to keep my design.

Much appreciated for all your help!

I'm not sure if you have found your answer or not but I am currently doing something very similar to this. I used the vlookup function to have it pull the name of the company, the contact, address and phone number to certain cells when I selected a certain city. My equation looked like this:
=VLOOKUP(H7,LOCATIONS2!A1:F7,1,False)
Basically H7 had the city I wanted to equation to reference, LOCATIONS2! is the worksheet & A1:F7 is the table array, 1 is the column it will find the answer in, False prevents anything that doesn't match 100% from being pulled over.
Like I said, this may not work for you but it works for me when I'm needing to pull their mailing info over for each city I select.