Take values from another spreadsheet, based on a value on the original workbook

Question

I'm looking automatically to calculate the average of 40 values that come from a .csv file, which I have managed to do.

My problem is I would like it to take the values from any separate workbook based on the cell value from the original workbook, so this can automatically calculate the average from any one of a number of available spreadsheets.

This is the formula I'm using currently:

=Sample1.csv!C1

And this is the what I tried, which obviously doesn't work:

=B4.csv!C1

With B4 containing the word Sample1 or any other Sample Number.

Answer 1

The formula you can use to refer to a cell on another sheet

=INDIRECT(B1 & "!C1")

Where B1 has the sheet name and you get the value of cell C1 from it.

It can be used to get data from another workbook , so long as the other workbook is open.

eg =INDIRECT("[Book1.xlsx]a.csv!$A$1")

=INDIRECT("[" & B1 & "]!$A$1")

Where B1 has the name of the workbook

In practise you might use:

=INDIRECT(B1 & "!" & C1)

Where C1 contains (ie stores as a value!) the address of the cell you want to reference. Thsi is useful as it allows you to copy the formulas such that the addresses change.

It is better to use the CELL function as follows which uses CELL to get the Address of A1 as text eg "$A$1". This means the formula can easily be copied and pasted into the cells you want it to be in.

=INDIRECT(B1 & "!" & CELL("address",A1)

Enjoy.

Answer 2

You can use the Indirect() function to have Excel interpret a text string as a cell reference, and this string can be constructed with the text values from one (or more) cells. So in your example use: =INDIRECT(B4 & "!C1") which retrieves the value "Sample1" from B4 and concatenates this with the second part of the string to give "Sample1!C1" that Indirect() then uses as an address & retrieves the value from there.