Return source/delivery sites from different files

[u1905068]

Hi all,

I hope you are well! This is my first message in this forum, I hope I can learn a lot from you guys!!

I am seeking for some help for a project I would like to do.
I work in a Logistics company and at the moment we have different excel files where we keep some rates (from point X to point Y, price is Z).
The thing is that we have like 10/12 different Excel files (each one for different countries, for example UK to Germany, UK to France, which we keep updating as we get new ones), and inside of these files we multitude of rates

I would like to create an excel where by typing: Location site, and delivery site, it would pull out the price inmediately, rather going inside each file and look for it.

I've tried with the forumla "search" and "pull data", but I can't seem to make it work.
I am not sure if all this makes sense.

I would appreciate if someone could give me a hand! Many thanks guys!
Have a lovely evening!

Moderator note: Please review forum rules regarding thread titles. As a courtesy I have taken the liberty of changing your thread title to something more descriptive this time.

[Special-K]

Not saying I'll answer this but...

Probably some form of VLOOKUP or INDEX( MATCH(...)) is required.

Do all the files conform to the same format?

Post a small sample spreadsheet with expected results, remove any sensitive data, create a mockup example if necessary.
Use the Go Advanced option at the bottom of the page then scroll down to Manage Attachments as the "paperclip" method does not work on this forum.

[u1905068]

Hi Special-k!

Many thanks for your prompt response - It actually helps me to start to know where to look at!
I did try to attach some files before but unfortuntealy I couldn't!

The files they have all the same format, which I believe this helps to achieve my point..

I have attached x3 files which can explain a bit what I am looking to get..

By having x2 different Excel files with some information of the price for going from point A to point B (see files UK - Germany and UK - Spain), I would like to create a 3rd file (Master sheet) where by typing the supplier town (ex. Swansea), and the buyer town (ex. Barcelona), would give me automatically which is the cheapest Transport and it's rate, which can vary from Euros / Pounds / Dollars

Many thanks guys for your help, once again!

Have a lovely evening!

[FlameRetired]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

I have changed it for you this time.

[u1905068]

HI Flame,

Many thanks for the above message, I will be aware for next time!

Dave!

[Special-K]

Ok this works (see attached)
Formula in C5

You'll have to have the external workbook open for this to work (I think).

[u1905068]

Ok this works (see attached)
Formula in C5

You'll have to have the external workbook open for this to work (I think).

Hi Special-K, I will have a look later this evening / tomorrow morning. Much appreciated your help here mate!

I did try this morning with the formula "Lookup & Index", but it wasn't working since I couldn't make it work by using an external workbook.

[popipipo]

10/12 different Excel files

I should use 1 file with 10/12 worksheets

[Special-K]

My solution was for your example. I can only solve problems that you define clearly and provide examples for. I can't predict what yor end result looks like.

I was gonna test manchester to Munich but its not in the dropdown list

I'm not sure how to make dropdowns limit lthe contents based on another cell.
e.g.

Spain - Dropdown shows only Spanish cities
Germany - Dropdown shows only German cities

I dont know how to do that.

[u1905068]

Many thanks popipipo & Special-K for your help and knowledge, much appreciated!
I will work from here, and if I need any other advise I will let you know!

[u1905068]

Good afternoon guys!

Many thanks for your advises guys!
I've have been working with the Excel over the weekend and I've managed to create some extra worksheets, for different countries and it's working perfectly (I belive!)
However, I would like to ask you a couple of questions, which I haven't been able to sort on my own!

• I have tried to use the Developer in order to be able to autocomplete the Dropdown when I am typing a town (for example, I was trying to get the word Swansea by typing only "Swa"). I've tried with the help of Google and Youtube but my skills are not good enough unfortunately

• I would like to get the cheapest rate for each freight. For example, for the route Swansea - Amsterdam I get a result of £725.00 when the cheapest is £600.00 with Transport 5. It gives me the first option because it's in the Row2 (higher than the other). Is there any way I can get the cheapest freight?

Many thanks once again all!

[popipipo]

Second question:
Sort the tables on lowest price first.