I have an Excel file in which I have multiple sheets with financial statements of different companies (called Databas.xlsx). The structures of these sheets are identical. Then I want to use another Excel file to analyze these financial statements with the help of graphics. So I have to integrate the data from the different sheets into my analysis file. Performing this operation from a sheet is no problem because I can simply create a chart and mark the data I need in this sheet, so that the chart's data range looks like close to this:
=[Databas.xlsx]Kopparbergs! $ 3 C: $ 3 K
where "Kopparbergs" is the name of the sheet in Databas.xlsx. The problem I am facing is that I want to be able to change the name of the sheet that is inserted in this formula by writing the name in a cell (because that would allow me to change several charts at once). So just to clarify, in the formula written above, I want to be able to change the word "Kopparbergs" by writing text in a cell. If this is not possible, how could I accomplish that? How to create a chart that can change content based on the text of a cell that corresponds to a sheet?