Excel indirect - combine file with cell reference-CodePudding

My reference without indirect looks like this (german version)

='[Spektren A Auswertung.xlsx]Power Integral'!$M6

I want to have the file in cell B2 and C2, ... So I tried to combine it with indirect

=INDIREKT("'[" & B2 & "]Power Integral'!")& $M6

But that seems to be wrong. How can I realise this?

CodePudding user response：

The function parameters should be in this format:

=INDIRECT("'[sample data.xlsx]Sheet1'!B2")

For your example:

=INDIREKT("'[" & B2 & "]Power Integral'!M6")

To make it more dynamic

If you need to reference many values, e.g. M5, M6, M7, ... then it's probably worth noting that the address of the cell itself can be constructed using a function, making it more dynamic.

e.g. ADDRESS(6;13;4) results in M6, (i.e. the value 4 makes sure there are no absolute references ($ signs) in the address.

Putting all that together, you could

=INDIREKT("'[" & B2 & "]Power Integral'!" & ADRESSE(6;13;4))

Note: I'm not sure if ADRESSE is correct for your language of excel. It's a calculated guess.

Using this format you could

=INDIREKT("'[" & B2 & "]Power Integral'!" & ADRESSE(A1;13;4))
=INDIREKT("'[" & B2 & "]Power Integral'!" & ADRESSE(A2;13;4))
=INDIREKT("'[" & B2 & "]Power Integral'!" & ADRESSE(A3;13;4))

with values 6,7,8 in the cells A1,A2,A3.