May 11 2022 10:27 AM
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!
May 11 2022 12:20 PM
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.
May 11 2022 01:30 PM
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.
May 11 2022 10:03 PM
May 11 2022 10:11 PM