Formula pulling from a table but the table names changes

[Warren Keogh]

Hi

I am pulling tables of data from an external source into excel. Lets say the first source is company A. I have vlookups etc pulling data from this table.

If I pull the same table from Company B it breaks my formulas because the table name changes.

I can't fix or change the table names as I will not be able to refresh from the data source.

See sample attached. At present I just a simple formula to pull the data from the table for (example "=B5" )to pull the data in to a cell so I can link it to a formula in another sheet. I just run a very basic macro to recreate these formulas when I change the data source from Company A to B, otherwise I get '#REF error.

Is there a more elegant solution?

WK

 

[David Evans]

Is there a table naming convention in place that company A and B use?
Do these files have a standard structure?
Are these files always in the same location?
Is the external table in Excel or some other format?
Even if you can't write the name of a file, you might be able to utilize a point and select from a directory to identify the file you wish to import. In my experience these processes are made more robust and easier to program with a standardized approach, to file naming, range naming, table naming etc.

Sorry to ask so many questions but you're going to need those answers to solve your puzzle.

 

[Deepak]

Hi,

You haven't shared the code!!

Check this...

 

[NARAYANK991]

Hi ,

Can you not try something like this ?

Narayan