1

I have one excel file in which I have multiple sheets with financial statements from different companies (called Databas.xlsx). The structures of these sheets are identical. Then I have another excel file that I wish to use to analyse these financial statements using charts. Thus, I must get data from the different sheets into my analysis file. Doing this from one sheet is no problem, as I can simply create a chart and mark the data I need from this sheet, so that the chart data range would be something like this:

=[Databas.xlsx]Kopparbergs!$C$3:$K$3

where "Kopparbergs" is the sheet name in Databas.xlsx. The problem I am facing is that I want to be able to change the sheet name that is put into this formula by writing the name in a cell (because that would enable me to change multiple 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 that is not possible, how would I accomplish this? That is, how do you create a chart that can change its content depending on a text in a cell that corresponds to a sheet?

MarkusVtJohanssson
  • 11
  • 1

1 Answers1

0

So rather than using Indirect I think you need to use two named ranges for referencing when using a Chart.

This previous answer looks like a good guide to implement (not sure about etiquette of just copy & pasting previous answers so I'll just provide the link):

Dynamic chart range using INDIRECT: That function is not valid (despite range highlighted)

Earl_of_Duke
  • 358
  • 1
  • 6
  • Thanks a lot for the answer! I have tried using the indirect function, but I can't get it to work together with charts. It always tells me: "That function isn't valid" when I put it into the chart data range. How can I create a chart using the indirect function? – MarkusVtJohanssson Jun 22 '19 at 20:30