add name sheet in formula

Hello everyone,

I've been looking in the forum to find an answer to the following, but can't seem to get it to work. Could you help me with this one?

I would like to create an excel file with multiple sheets. The first sheet will be an overview sheet which shows information from the other sheets. The information on the other sheets is always the same for every sheet, so I would like to make a formula which stays the same, but uses another sheet name in it.

For example:

on the overview sheet I have a cell which contains the "formula" (more a reference)

=SheetnameOne!B1

. On the cell below this I would like the same information from sheet 2, so

=SheetnameTwo!B1

. Of course this is just one of the cells I would like referenced. I thought I could type the sheetname once on the same line as where the references go and "add" this into a formula. I've started with Concatenate, but this doesn't result in a valid formula. I then tried Indirect, but can only get REF or VALUE errors. I don't seem to be able to wrap my head around this..

I think this is a very easy issue for you guys (pls don't laugh at me ), so I hope someone can help me. In short, I need a way to add the contents of a cell (i.e. SheetnameOne which I would manually enter into a cell) into a formula so that I only have to type the name of the sheet once rather than do this for all formulas.

Thank you so much in advance if you could help me with this one.

Kind regards,
DMR