Returning cell value in sheet referenced in another cell.

[Marcus_Grant]

Hi,

I'm very much a rookie with excel and was hoping someone can help with the following.  I'm trying to return the value of a cell from a sheet.  As in the image below, I've simply put "=" then clicked on the cell in another sheet I want the value from.  However, I want the file it takes the value from to be based on the cell on the left e.g

=[B3]Sheet1!$C$5

Without going into detail, the value is in the same cell on each file as they are filed separately for each day.  The aim would be to simply pull the formula down and it takes each file name on the left and returns the cell from each.

Any help would be greatly appreciated!

[#2]

@Marcus_Grant 

You can use the INDIRECT function for this, BUT (and this is a big BUT!) it will only work if all the workbooks referred to (1.xlsx etc.) is also open in Excel. If they are closed, the formula will return #REF!

In C3:

=INDIRECT("'["&B3&"]Sheet1'!C5")

Fill down.

[#3]

@Marcus_Grant 

Slightly modified formula suggested by @Hans Vogelaar  if get value from the same cell in another workbook

=INDIRECT("'[" & $B3 & "]Sheet1'!" & CELL("address"))

If to keep all files opened is not the case perhaps it could be workaround with VBA or Power Query.

[#4]

Hi Hans,
The but is big unfortunately but I really appreciate the help! I may be able to open each as they're saved and essentially manually update it.

[#5]

Much appreciated, I've only just heard of power query but seems like there will be lots of uses so will definitely look into that!