In doing some data analysis, I'm using a codebook key and/or frequently extracting data cells from databases in other excel workbooks using connections.
I've come up with an issue connecting to a location using a string of text created using a reference cell within the sheet I am doing the calculations. I have databases for different countries all saved to my hard drive. Based on the text string with the country name in a reference cell, (B2), I would the connection pathway in cell B4 to change and link to the information specific to the reference cell country. If I put "US" in cell B2 I want the connection name to change to "'c:\users\documents\project\database\consumption\[data US.xlsx]sheet1'!N2" or if b2 was China I would want the connection string to read "'c:\users\documents\project\database\consumption\[data China.xlsx]sheet1'!N2"
I have tried using the concatenate function to join the reference cell text with other parts of the pathways name, but I don't know what kind of function or method of using connections to call up that text string as the pathway.
Any help would be fantastic!
Bookmarks